Arthur Wang's Blog
Follow me on
  • My General Blog
  • Software Development
    • Latest Articles on Software Development
    • Complete Resources for Developers >
      • Tutorial Information for Developers
      • .NET Developer Blogs
      • Developer Journals and Magazines
      • Developer's Tools
      • Database Development
      • ​Developer Conference and Events
  • Tech
    • Latest Articles on Technology Development
  • Health
  • Money
  • Services
    • Modern Website Design
    • Web Maintenance of Existing Websites Service
    • Corporate Business Consulting Service
  • About
  • Contact
  • Art
  • 中文部落格

Using CTE Common table Expression in MS SQL Server

4/18/2014

0 Comments

 
If you have not used CTE in any of your SQL server projects, you've been missing a good tool.  CTE, also known as Common Table Expression, is a new construct introduced early in Microsoft SQL Server 2005, and it becomes popular when SQL server 2008 came out since the performance had been improved in this version.  

Basically, we can use it to return a particular subset of data or aggregate data across many tables, and do more manipulations on the subset.  However, one might ask, it sounds like doing a subquery(See Fig 1).  Well, it is true, but CTE can be encapsulated and have multiple references to it, and unlike subquery, you have choices of using CTE as non-recursive just like subquery or recursive.  Best of all, it is more maintainable than subquery.


Fig 1: An example of subquery

SELECT Ord.SalesOrderID, Ord.OrderDate, 
 (SELECT MAX(OrdDet.UnitPrice) FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM AdventureWorks2008R2.Sales.SalesOrderHeader AS Ord


When using CTE, complex SQL statement can be made to easier to understand format that you or other developer can still follow the logic of the what it is intended to do.  From years of experience, you know that your requirement is gradually more complex and in this way, the query would be better maintainable.

Temporary Table vs CTE

In a way, CTE is similar to temporary table that it can be logical separated.  However, CTE is a temporary resultset that does not physically created in the Tempdb database like the temporary table does.  So if exists in memory and cannot be indexed.  In addition, table variable is also like the temporary table which is created in the Tempdb database.

Here is a simple example of CTE, where CTEReports is the name of your temporary resultset

;With CTEReports(EmpID, MgrID) AS
(
    Select EmployeeID, ManagerID
    From Employees
    Where ManagerID is NULL
)
Select * from CTEReports


Please note that you should always place a ";" semicolon in front of "With" statement as a good habit.

Simple Example of Recursive CTE
There are two blocks of code within the With statement called the Anchor block and Recursive block.

;WITH Numbers AS
(

    SELECT n = 1        <------- first block
    UNION ALL
    SELECT n + 1          <------- begins of second block
    FROM Numbers
    WHERE n+1 <= 10

)
select * from Numbers


The anchor block is what defined the temporary resultset, and the recursive block is the one using the resultset.
So first block has 1, and in the second block it increases by +1, and loop until it is less than or equals to 10.
Picture
Using MAXRECURSION value to prevent CTE going into infinite loop since you can set your recursion limit.

So in the last select statement, you can write:
Select * From Numbers OPTION (MAXRECURSION 8) when you want to throw an error when recursion goes beyond 8th recursion.

There are many examples on the web about this topic but I hope I have used the simplest examples to explain this useful tool.
0 Comments

    Arthur Wang

    @ArthurWangLA
    MCSD App Builder
    MCSD Web Applications
    ​Member of Windows Insider Program & HoloLens Developer Community & Dev Center Insider Program

    Over 17+ years of  experience in web-based software development & management.  Specialized in Microsoft technology with c# language and its architecture design.  MCSD, MCSE, Microsoft Specialist, MCP + Internet, and B.S. from UCLA

    Archives

    August 2018
    March 2018
    January 2017
    December 2016
    May 2016
    April 2016
    March 2016
    February 2016
    April 2014

    Categories

    All
    API
    Arduino
    ASP.NET
    Cognitive
    CSS
    Database
    Deep Learning
    DevOps
    Electronics
    Flexbox
    HTML5
    IoT
    Katana
    Machine Learning
    Management
    .NET
    .NET Core
    Neural Network
    OWIN
    Programming
    Programming Tools
    Recognition
    Security
    SQL Server
    UWP
    Visual Studio
    Web API
    Web Developer

    RSS Feed

    Latest Articles

© 2014-2020 ArthurWiz.com All Rights reserved. | Home | About |
Protected by Copyscape