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<10 p="p">)
SELECT POWER(I,2) AS SQUARES FROM CTE


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)