Total Page Preview:   000000018953

What is Common Table Expression (CTE) in SQL SERVER.

CTE stands for Common Table expressions. It was introduced with SQL Server 2005. It is a temporary result set and temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.. Unlike temporary table its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with semicolon. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.


  • CTE are not replacement of the Temp Table or Temp Variable Table
  • Always begin CTE with semi-comma
  • The scope of the CTE is limited to very first SELECT statement
  • Usually the execution plan of the CTE like sub-query but there are cases when it can be different as well
  • Use MAXRECURSION to prevent infinite loop in recursive CTE

Sub Query without CTE given bellow:


--This query write using sub query:

SELECT TOP 1 [Eid] ,[Salary] FROM
(SELECT TOP 3 [Eid] ,[Salary] FROM [TestDB].[dbo].[Employee] ORDER BY Salary DESC) AS Emp


Using CTE above query can be re-written as follows:


  ;WITH Emp([Eid], [Salary]) --Column names for CTE, which are optional
    SELECT TOP 3 [Eid], [Salary] FROM [TestDB].[dbo].[Employee] ORDER BY Salary DESC

 --User CTE
  SELECT TOP 1 [Eid], [Salary] FROM emp ORDER BY Salary ASC





For Example :

CREATE TABLE [dbo].[Employee]
    [Eid] [float] NULL,
    [Ename] [nvarchar](255) NULL,
    [Salary] [float] NULL

INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (1, N'Neeraj', 45000)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (2, N'Ankit', 5000)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (3, N'Akshay', 6000)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (4, N'Ramesh', 7600)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (5, N'Vikas', 4000)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (7, N'Neha', 8500)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (8, N'Shivika', 4500)
INSERT [dbo].[Employee] ([Eid], [Ename], [Salary]) VALUES (9, N'Tarun', 9500)






Thank You

About Author

Brijesh Kumar

Database Developer

I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall and Database Migration On-Premise to Azure Cloud.
LinkedIn :


Good post however I was wondering if you could write a litte more on this subject? I'd be very thankful if you could elaborate a little bit further. Many thanks!
Asking questions are genuinely fastidious thing if you are not understanding something fully, but this article presents good understanding yet.
This is a topic that is near to my heart... Many thanks! Where are your contact details though?
Great blog here! Additionally your site loads up very fast! What web host are you using? Can I am getting your affiliate link to your host? I desire my website loaded up as fast as yours lol
Greetings! Very useful advice in this particular article! It is the little changes that make the biggest changes. Many thanks for sharing!
I don't even know how I stopped up here, but I assumed this put up was once great. I don't know who you're but definitely you're going to a famous blogger for those who aren't already. Cheers!
Thankfulness to my father who told me regarding this website, this web site is actually awesome.
I have been surfing online more than three hours today, yet I never found any interesting article like yours. It's pretty worth enough for me. In my view, if all site owners and bloggers made good content as you did, the internet will be a lot more useful than ever before.
My family members all the time say that I am killing my time here at web, but I know I am getting knowledge everyday by reading such good articles or reviews.
Yes! Finally something about plenty of fish dating site.
I've been exploring for a little bit for any high-quality articles or blog posts in this kind of space . Exploring in Yahoo I finally stumbled upon this site. Reading this info So i am glad to show that I've a very good uncanny feeling I discovered exactly what I needed. I most no doubt will make certain to don?t forget this website and give it a look on a relentless basis.
Hello! Someone in my Facebook group shared this site around so I arrived at take a peek. I'm definitely enjoying the info. I'm book-marking and you will be tweeting this to my followers! Superb blog and superb style and design.
It's actually a great and useful part of info. I am satisfied that you just shared this helpful info around. Please keep us up-to-date this way. Thanks for sharing.
I'm amazed, I must say. Seldom do you run into a blog that's equally educative and engaging, and without a doubt, you've hit the nail in the head. The problem is something which not enough people are speaking intelligently about. I am delighted i found this during my seek out something concerning this.
We're a small grouping of volunteers and opening a brand new scheme in our community. Your website offered us with valuable info to work on. You have done an impressive job and our entire community will likely be grateful to you personally.




Facebook User: