Total Page Preview: 000000023518
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