In this article, I am going to explain you about CTE (Commann table Expression).CTE is used to store temparary result. Using CTE it improved readability and ease in maintenance of complex queries.There are two forms of CTP.In Temparary table you have to create first and you can call again and again, In CTP you have to call immediately.

1. Recursive
2. NonRecursive

In this article, I will consider that you know how to create database.

Now Create tables as give below

CREATE TABLE STUDENT
(
ID BIGINT IDENTITY(1,1),
FULLNAME NVARCHAR(MAX),
BIRTHDAY DATE,
REGISTRATION_DATE DATETIME,
FATHERNAME NVARCHAR(MAX),
DELETED BIT DEFAULT(0)

)
CREATE TABLE STUDENT_RESULT
(
ID BIGINT IDENTITY(1,1),
STUDENT_ID BIGINT,
COURSE_ID BIGINT,
SCORE BIGINT,
DELETED BIT DEFAULT(0),
)
CREATE TABLE COURSES(
ID BIGINT IDENTITY(1,1),
COURSENAME NVARCHAR(MAX),
DELETED BIT DEFAULT(0)
)

Insert some records into tables.

INSERT INTO STUDENT VALUES('AAMIR HASAN','01/01/1985','01/01/2010','ZAHOOR AHMED',0)
INSERT INTO STUDENT VALUES('NADIA KHAN','01/01/1985','01/01/2001','GUL KHAN',0)
INSERT INTO STUDENT VALUES('JOHN GILL','01/01/1988','01/01/2010','AHMED',1)
INSERT INTO STUDENT VALUES('SADIA','01/01/1989','01/01/2010','AAMIR KHAN',0)
INSERT INTO STUDENT VALUES('SOBIA HINA','01/01/1985','01/01/2009','TAJMAL',0)

INSERT INTO COURSES VALUES('MATH',0)
INSERT INTO COURSES VALUES('ENGLISH',0)
INSERT INTO COURSES VALUES('COMPUTER',0)

INSERT INTO STUDENT_RESULT VALUES(1,1,99,0)
INSERT INTO STUDENT_RESULT VALUES(1,2,97,0)
INSERT INTO STUDENT_RESULT VALUES(1,3,95,0)

INSERT INTO STUDENT_RESULT VALUES(2,1,99,0)
INSERT INTO STUDENT_RESULT VALUES(2,2,91,0)
INSERT INTO STUDENT_RESULT VALUES(2,3,92,0)

INSERT INTO STUDENT_RESULT VALUES(3,1,66,0)
INSERT INTO STUDENT_RESULT VALUES(3,2,77,0)
INSERT INTO STUDENT_RESULT VALUES(3,3,94,0)

Recursive

Recursive CTE are temparary view.using CTP simplicity of syntax in your queries.

Let start with simple example Which will return All Student Total marks, name in Asceding order, who have score above 100.

WITH  REC_CTE AS
(
SELECT SUM(SCORE) AS TOTAL_MARK,S.FULLNAME FROM STUDENT S
INNER JOIN STUDENT_RESULT SR ON S.ID=SR.STUDENT_ID
GROUP BY S.FULLNAME
)
SELECT * FROM REC_CTE WHERE TOTAL_MARK >=100 ORDER BY FULLNAME ASC

This will return the result as shown in daigram below.

Excution Plan as show in Daigram below.

You can not write follow syntax into CTE.

1. ORDER BY (not in  when a TOP clause is specified)
2. COMPUTE or COMPUTE BY
3. for xml
4. into
   
5. for browse

Failure of CTE

Below is the daigram, Daigram is showing failur occurrence of CTE.

NonRecursive

Non recursive CTE are those which can not refer inside the CTE.

WITH CTP_nonRes  as
(
SELECT SUM(SCORE) AS marks,student_id,COURSE_ID FROM dbo.STUDENT_RESULT
GROUP BY course_id,STUDENT_ID 
)
--
SELECT * FROM CTP_nonRes cte

For more information Click here  microsoft msdn