Total Page Preview:   000000019312

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

Schrantz
28-Jul-2020
What's up mates, pleasant paragraph and fastidious urging commented at this place, I am truly enjoying by these.
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!
Glade
21-Nov-2019
Asking questions are genuinely fastidious thing if you are not understanding something fully, but this article presents good understanding yet.
Paris
02-Dec-2019
This is a topic that is near to my heart... Many thanks! Where are your contact details though?
Conlon
31-Aug-2020
Hi there to all, how is all, I think every one is getting more from this website, and your views are good in support of new visitors.
McPeak
30-Jul-2020
If some one desires to be updated with most up-to-date technologies therefore he needs to be visit this website and be current everyday.
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
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!
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!
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.
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.
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.
Braun
16-Aug-2020
Hi, Neat post. There may be a problem together with your site in internet explorer, would check this? IE nonetheless is definitely the marketplace chief and a huge component to folks will omit your excellent writing because of this problem.
Brumby
26-Aug-2020
Hi, all is going sound here and ofcourse every one is sharing information, that's actually excellent, keep up writing.
Couture
29-Oct-2020
Helpful information. Fortunate me I discovered your web site accidentally, and I'm shocked why this coincidence didn't came about in advance! I bookmarked it.
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.
Durgin
23-Dec-2020
Awesome site you have here but I was wanting to know if you knew of any user discussion forums that cover the same topics talked about in this article? I'd really love to be a part of online community where I can get responses from other experienced individuals that share the same interest. If you have any suggestions, please let me know. Appreciate it!
Armytage
05-Jan-2021
Good information. Lucky me I came across your site by mistake (stumbleupon). I've saved being a favorite for later!
Wolff
25-Jan-2021
Wow! Ultimately I purchased a webpage from where I be capable of genuinely take useful facts regarding my study and knowledge.
McAlister
31-Jan-2021
I am just unsure where you're having your information, but great topic. I must take some time learning much more or understanding more. Many thanks for fantastic information I was looking for this information for my mission.
Holliday
10-Feb-2021
I've been exploring for a bit for any high-quality articles or blog posts on this kind of space . Exploring in Yahoo I eventually found this website. Studying this information So i'm satisfied to express that I have got a very just right uncanny feeling I came upon just what I needed. I most unquestionably can make sure to will not overlook this web site and give it a look regularly.
Weeks
19-Feb-2021
These are generally really great ideas in regarding blogging. You may have touched some pleasant points here. Any way keep up to date wrinting.
Harries
22-Feb-2021
great submit, very informative. I wonder why the exact opposite specialists on this sector do not understand this. You should proceed your writing. I am just confident, you've an incredible readers' base already!
Byatt
30-Jul-2020
When someone writes an paragraph he/she retains the thought of any user in the/her brain that how a user can know about it. Therefore that's why this post is amazing. Thanks!
Whiting
11-Jan-2021
Nice respond in return on this matter with solid arguments and describing all regarding that.
Monckton
31-Jul-2020
Excellent website. Lots of useful info here. I'm sending it to several buddies ans additionally sharing in delicious. And of course, thank you to your sweat!
McCollister
26-Jul-2020
This website was... how do I say it? Relevant!! Finally I've found something that helped me. Thanks a lot!
Marcotte
01-Aug-2020
I understand this web page offers quality based articles or reviews and additional information, is there any other web site which offers most of these information in quality?
Pring
08-Aug-2020
Nice blog here! Also your site loads up very fast! What host are you presently using? Can One get your affiliate hyperlink to your host? If only my site loaded as quickly as yours lol
Haffner
12-Aug-2020
You really help it become seem so simple with the presentation however i find this topic to get really something which I think I would never understand. It seems like too complicated and very broad for me. I'm anticipating for your forthcoming post, I am going to try to obtain the hang of this!
Parson
13-Aug-2020
Today, I went to the beach with my kids. I found a sea shell and gave it to my 4 year old daughter and said "You can hear the ocean if you put this to your ear." She placed the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is entirely off topic but I had to tell someone!
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.
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.
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.
Pickel
16-Nov-2020
I've learn a number of perfect stuff here. Certainly value bookmarking for revisiting. I wonder how a lot attempt you set to create this sort of wonderful informative site.
Calloway
03-Dec-2020
Very descriptive article, I enjoyed that a whole lot. Will there become a part 2?
Chestnut
27-Nov-2020
It's really a great and helpful part of info. I'm glad that you just shared this helpful information around. Please keep us up to date this way. Thanks for sharing.
Marlowe
22-Dec-2020
It's truly very complicated in this particular active life to listen news on TV, therefore I simply use web for your purpose, and get the newest news.
Uther
23-Jan-2021
Please let me know if you're looking for a writer for your site. You have some really good posts and I believe I would be a good asset. If you ever want to take some of the load off, I'd absolutely love to write some content for your blog in exchange for a link back to mine. Please send me an email if interested. Thanks!
Lillico
18-Mar-2021
Howdy! I know this is sort of off-topic but I needed to ask. Does operating a well-established website like yours take a massive amount work? I am brand new to blogging however I do write in my diary everyday. I'd like to start a blog so I can easily share my experience and thoughts online. Please let me know if you have any kind of suggestions or tips for new aspiring blog owners. Appreciate it!
Dearing
05-Mar-2021
I am certain this article has touched all of the internet viewers, its really really nice bit of writing on accumulating new weblog.
Ono
08-Aug-2021
I know this web page offers quality dependent articles or reviews and extra material, is there any other site which offers these data in quality?

                           
                           

                           

                           

Recent Posts

How to get the alternate rows or records from table in sql server
07-Jun-2017
Data Warehouse Three Tier Architecture
20-Aug-2017
SQL Server - Fragmentation on azure sql database
07-Oct-2018
Limitations of Azure SQL Database
19-Sep-2019
PIVOT and UNPIVOT in SQL Server with Example
29-Jun-2017
SQL Server - Find all index with key columns on Azure SQL Database
02-Oct-2018
SQL Server - Find unused indexes on azure SQL database scripts
02-Oct-2018
Find Resource usage in Azure SQL Database using script or T-SQL
07-Oct-2018
How to Set client IP Address using the firewall security in Azure SQL Using Portal
29-Jun-2017
What is a Trigger and type of trigger in SQL server?
10-Sep-2021
What is Table Variable? Explain its advantages and disadvantages?
10-Sep-2021
Top 60 SQL Server Interview Questions Answers for freshers and experienced
17-Sep-2021
Cloud Data Engineer SQL Server Interview Questions Answers for freshers and experienced
31-Aug-2021
Top 16 performance query optimization techniques in SQL Database?
16-Sep-2021
Data warehouse Interview Questions Answers for freshers and experienced
16-Sep-2021
What are the types of User Define Function and difference between a nested UDF and recursive UDF in SQL server?
10-Sep-2021
What are the different types of Error Handling in SQL Server?
11-Sep-2021
What are the differences between OLTP and OLAP?
07-Sep-2021
How many types of dimension tables available in data warehouse?
16-Sep-2021
What are characteristics and advantages of stored procedure?
10-Sep-2021

Facebook User: