CTE in SQL Server 2008

CTE stands for common table expressions.

common table expressions are very useful for getting results using T-SQL.
It provides an efficient way to execute the results and also provide complex queries into simple queries.

Example1:

WITH CTE
AS (
	SELECT ASCII(1) AS i
		,CHAR(ASCII(1)) AS Value
	
	UNION ALL
	
	SELECT i + 1
		,CHAR(i)
	FROM CTE
	WHERE VAlue IS NOT NULL
	)
SELECT i AS AscIIValue
	,Value AS CHARVALUE
FROM CTE
OPTION (MAXRECURSION 32767)

Example 2:

WITH CTE
AS (
	SELECT 1 AS I
	
	UNION ALL
	
	SELECT I + 1
	FROM CTE
	WHERE I
	)
SELECT POWER(I, 2) AS SQUARES
FROM CTE

<10 p="p">
For mathematical expressions and tally tables CTE is useful. The above query will generate squares of the numbers from 1 to 10. 

-------------------------------------------
--GETTING HIERARCHY
-------------------------------------------

WITH Relationcte
AS (
	SELECT category_name
		,LINK_ID
		,PARENT_LINK_ID
		,RELATIONSHIP_TYPE
		,RELATIONSHIP_SUBTYPE
		,0 AS lvl
	FROM RELATIONSHIP
	WHERE link_id = 116
	
	UNION ALL
	
	SELECT R.category_name
		,R.LINK_ID
		,R.PARENT_LINK_ID
		,R.RELATIONSHIP_TYPE
		,R.RELATIONSHIP_SUBTYPE
		,lvl + 1
	FROM RELATIONSHIP R
	JOIN Relationcte R1 ON r.PARENT_LINK_ID = r1.LINK_ID
	)
SELECT REPLICATE('+', lvl) + right('>', lvl) + CATEGORY_NAME AS partname
FROM Relationcte
WHERE CATEGORY_NAME IS NOT NULL
GO

;WITH Relationcte
AS (
	SELECT category_name
		,LINK_ID
		,PARENT_LINK_ID
		,RELATIONSHIP_TYPE
		,RELATIONSHIP_SUBTYPE
		,0 AS lvl
	FROM RELATIONSHIP
	WHERE link_id = 38225
	
	UNION ALL
	
	SELECT R.category_name
		,R.LINK_ID
		,R.PARENT_LINK_ID
		,R.RELATIONSHIP_TYPE
		,R.RELATIONSHIP_SUBTYPE
		,lvl + 1
	FROM RELATIONSHIP R
	JOIN Relationcte R1 ON r.PARENT_LINK_ID = r1.LINK_ID
	)
SELECT REPLICATE('+', lvl) + right('>', lvl) + CAST(LINK_ID AS VARCHAR) AS partname
	,RELATIONSHIP_TYPE
FROM Relationcte

DECLARE @test1 TABLE (value VARCHAR(50))
DECLARE @test VARCHAR(4000)
	,@var INT
	,@name VARCHAR(50);

SET @test = 'malli,test,hi,red,mmm,ttt,hjgh.hgh,dddff,lkldfsggd';

DECLARE @Date1 DATETIME2
	,@date2 DATETIME2

SET @Date1 = GETDATE();

WITH CTE
AS (
	SELECT LTXT = LEFT(@test, CHARINDEX(',', @test))
		,RTXT = RIGHT(@test, LEN(@test) - CHARINDEX(',', @test))
	
	UNION ALL
	
	SELECT LTXT = LEFT(ct.RTXT, CHARINDEX(',', ct.RTXT))
		,RTXT = RIGHT(@test, LEN(ct.RTXT) - CHARINDEX(',', ct.RTXT))
	FROM CTE ct
	WHERE LEN(LTXT) <> 0
	)
SELECT replace((
			CASE 
				WHEN charindex(',', LTXT) > 0
					THEN LTXT
				ELSE RTXT
				END
			), ',', '')
FROM CTE

SET @date2 = GETDATE()

SELECT DATEDIFF(ms, @date1, @date2)

--------------
--SECOND WAY
--------------
SET @Date1 = GETDATE()

WHILE CHARINDEX(',', @test, 1) <> 0
BEGIN
	SET @var = CHARINDEX(',', @test, 1)
	SET @name = SUBSTRING(@test, 1, @var - 1)
	SET @test = SUBSTRING(@test, @var + 1, len(@test))

	INSERT @test1
	SELECT @name
END

INSERT @test1
SELECT @test

SELECT *
FROM @test1

SET @date2 = GETDATE()

SELECT DATEDIFF(ms, @date1, @date2)