Monday, June 8, 2015

SQL - Common Table Expression

Normally, Common Table Expression is known as CTE in SQL Server which was introduced in SQL Server 2005 released. CTE's are a great feature of SQL which can be used anywhere to make the query easier to read. For the better understanding, you can reference them as a temporary result set which is defined within the execution scope of a single SQL statement such as SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW.

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. It can be self-referencing or can be referenced multiple times in the same query. CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.  You can be used CTE for the following purposes: 
  • It can be used in recursive query.
  • It is the best substitute for a view when the general use of a view is not required.
  • It enables grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access. 
  • Reference the resulting table multiple times in the same statement.
  • CTE is the best option to avoid the temporary tables in the views because we can not use temp tables in the views.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Simply, CTEs are based on the Duration of the Query which means we cannot use the same CTE in two different SQL statements as everyone is a separate query. A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

If you need a CTE and want to reuse its results then you could always insert the results into a temp table or table variable. So it is easy to use one or the other or both.

CTE -Multiple times used example-
A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy. A CTE creates the table being used in memory, but is only valid for the specific query following it. When using recursion, this can be an effective structure, but bear in mind that it will need to be recreated every time it's needed.

CTE's are a great way of declaring a set! Not being able to index a CTE is actually a good thing because you don't need to! It's really a kind of syntactic sugar to make your query easier to read/write.

----- declare variable to store values
declare @StartDate datetime,
 @EndDate datetime

----- Set values in the defined local variable here
select @StartDate=getdate(),  @EndDate = getdate()+10

------ using CTE to show multiple use itself
;WITH CTE AS (
               SELECT @StartDate AS myDate
               UNION ALL

               ------ calling multiple time here
               SELECT DATEADD(Day,1,myDate)
               FROM cte
               WHERE DATEADD(Day,1,myDate) <=  @EndDate
               )
----- View the result here
Select * from CTE;

Work with CTE:
To understand the functionality of CTE, we are taking the two tables as Employee Master and Department Master and both tables are linked with Dept Id key.



Employee Master
Emp Id
Employee Name
Dept Id
EmployeeRatePerDay
E0001
Ryan Arjun
D001
240
E0002
Tony Towery
D001
230
E0003
Lucy Gray
D001
250
E0004
Will Smith
D002
245
E0005
Chao Milk
D002
225
E0006
Chris Gyal
D002
210
E0007
Bill Gray
D003
190
E0008
Red Bill
D003
210
E0009
Tom Ramsay
D003
200

Department Master
Dept Id
Department Name
D001
Database
D002
Testing
D003
IT

----- Create CTE table to store values from employee master
;With CTE_Employee as
(
SELECT [Emp Id]
      ,[Employee Name]
      ,[Dept Id]
      ,[EmployeeRatePerDay]
  FROM [dbo].[EmployeeMaster]
  ),

  ----- Create another CTE table to store values from department master
  CTE_Department AS
  (
  SELECT [Dept Id]
      ,[Department Name]
  FROM [dbo].[DepartmentMaster]
  )

  ----- join both CTE table to get the output
  SELECT
                ecte.[Emp Id],
                ecte.[Employee Name],
                ecte.[Dept Id],
                dcte.[Department Name],
                ecte.EmployeeRatePerDay
 FROM       
  ----- Employee CTE table  
                CTE_Employee ecte
                INNER JOIN

  ----- Department CTE table
                CTE_Department dcte
                ON ecte.[Dept Id] = dcte.[Dept Id];


Output of the CTE query:
Emp Id
Employee Name
Dept Id
Department Name
EmployeeRatePerDay
E0001
Ryan Arjun
D001
Database
240
E0002
Tony Towery
D001
Database
230
E0003
Lucy Gray
D001
Database
250
E0004
Will Smith
D002
Testing
245
E0005
Chao Milk
D002
Testing
225
E0006
Chris Gyal
D002
Testing
210
E0007
Bill Gray
D003
IT
190
E0008
Red Bill
D003
IT
210
E0009
Tom Ramsay
D003
IT
200

Conclusion-
CTEs are unindexable (but can use existing indexes on referenced objects) and they cannot have constraints and are essentially disposable VIEWs. The best part of the CTEs that they persist only until the next query is run and you can used them as recursive or non-recursive also. A CTE should never be used for performance. You will almost never speed things up by using a CTE, due to just having disposable view behavior. They are best for doing some neat things and they do not have dedicated stats (rely on stats on the underlying objects).

Finally, CTEs don't use tempdb by default so you reduce contention on that bottleneck through their use.
To know more on the different kinds of the tables in SQL Servers at
  1. SQL – Wide Tables
  2. SQL - Table Variables
  3. SQL - Temp Table (Local & Global Temp Tables)
  4. SQL - Common Table Expression
  5. SQL - Difference between Table Variable and Common Type Expression
  6. SQL - Difference between Temp Table and CTE
  7. SQL - Difference between Temp Table and Table Variable

No comments:

Post a Comment

Popular Posts