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

Navneet Mishra 25-Nov-2016 Nice Articles. Move forward for gr8 future. Relpy Palmos 14-Jul-2021 I have read so many articles regarding the blogger lovers except this piece of writing is in fact a pleasant article, keep it up. https://gcialisk.com Relpy

Notify me of new comments via email. Notify me of new posts via email.