Total Page Preview:   000000018683

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
ORDER BY Salary ASC

 

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

 

  ;WITH Emp([Eid], [Salary]) --Column names for CTE, which are optional
  AS
  (
    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
) ON [PRIMARY]

GO
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)

 

 

Thanks!

 

 

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 : https://www.linkedin.com



Comments

Lindt
21-Jun-2017
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!
Wessel
12-Nov-2019
Yes! Finally something about plenty of fish dating site.
McGarry
12-Nov-2019
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.
Spivey
16-Nov-2019
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!
Kuykendall
16-Nov-2019
Greetings! Very useful advice in this particular article! It is the little changes that make the biggest changes. Many thanks for sharing!
Glade
21-Nov-2019
Asking questions are genuinely fastidious thing if you are not understanding something fully, but this article presents good understanding yet.
Poe
21-Nov-2019
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.
Haag
21-Nov-2019
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
Thurlow
22-Nov-2019
Thankfulness to my father who told me regarding this website, this web site is actually awesome.
Langdon
30-Nov-2019
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.
Paris
02-Dec-2019
This is a topic that is near to my heart... Many thanks! Where are your contact details though?
Macandie
07-Jul-2020
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.
Seymour
11-Sep-2020
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.
Timmer
16-Sep-2020
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.
Cribb
16-Sep-2020
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: