{"id":1234,"date":"2011-11-17T16:09:42","date_gmt":"2011-11-17T16:09:42","guid":{"rendered":"http:\/\/craftydba.com\/?p=1234"},"modified":"2011-11-17T16:21:08","modified_gmt":"2011-11-17T16:21:08","slug":"temporary-tables-part-5","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=1234","title":{"rendered":"Temporary Tables – Part 5"},"content":{"rendered":"

Today, I am going to talking about common table expressions<\/a>. A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single DML statement. A CTE is simular to a derived table in which the results are not stored as an object; However, CTE’s can be recursive or built upon other CTE’s. <\/p>\n

The WITH <\/a>key word is used to indicate the start of a CTE and needs to be preceeded with a semicolon. The rest of the syntax is like a stored procedure — name of object, fields of object, AS keyword, and body of object (query). The only difference is the body is between two brackets () instead of a BEGIN and END. A CTE must be followed by a single DML statement such as SELECT, DELETE, INSERT, UPDATE, or MERGE.<\/p>\n

The first example of a common table expression creates the car ownership table for person A. Please see the AUTOS database I created in a earlier article<\/a>. The cool thing about this example is that the data is in-line VALUES and does not come from a table.<\/p>\n

-- Database selection does not matter\r\nUSE model;\r\nGO\r\n\r\n-- Create a list & show contents\r\nWITH CTE_LIST(MyId, MyValue)\r\nAS\r\n(  \r\nSELECT * FROM \r\n  (\r\n    VALUES \r\n    (1, 'Continental'),\r\n    (2, 'Edsel'),\r\n    (3, 'Lincoln'),\r\n    (4, 'Mercury'),\r\n    (5, 'Ram')\r\n  ) AS A (MyId, MyValue)\r\n)\r\nSELECT * FROM CTE_LIST;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
I will be using the Adventure Works<\/a> Sample database supplied by Microsoft during the rest of this talk.<\/p>\n
A common query pattern is to get the most recent id by date.  Joining these results to the original table will bring back the most recent record by date.<\/p>\n
The SELECT <\/a>query below uses the last order by customer id as common table expression named MC.  The main query uses the CTE to return customer name, sales id, sub-total and order date.  This might be a query for the SALES team to cold call customers that have not ordered in the past x months.<\/p>\n
-- Use the correct databaseg\r\nUSE AdventureWorks2008R2\r\nGO\r\n\r\n\r\n-- Get customer name, sales id, sub-total and order date\r\nWITH CTE_MC (MyCustomer, MyDate)\r\nAS\r\n(\r\n    SELECT \r\n      CustomerID AS MyCustomer,\r\n      MAX(OrderDate) AS MyDate\r\n    FROM \r\n      Sales.SalesOrderHeader\r\n    WHERE\r\n      YEAR(OrderDate) = 2008 \r\n    GROUP BY \r\n  \t  CustomerID\r\n)\r\n\r\nSELECT \r\n  P.FirstName, \r\n  P.LastName,\r\n  SH.SalesOrderID, \r\n  SH.SubTotal,\r\n  SH.OrderDate\r\nFROM\r\n  Sales.SalesOrderHeader AS SH \r\n  INNER JOIN CTE_MC AS MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate\r\n  INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID\r\n  INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID\r\nORDER BY \r\n  SH.OrderDate ASC;\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
I am going to create a duplicate of the department table from the Adventure Works database.
\nThis table will be used in the examples below.<\/p>\n
-- Remove department staging table\r\nIF  EXISTS (\r\n    SELECT * \r\n    FROM sys.objects \r\n    WHERE object_id = OBJECT_ID(N'[STAGE].[Department]') AND type in (N'U')\r\n    )\r\n    DROP TABLE [STAGE].[Department]\r\nGO\r\n\r\n-- Recreate table from AW entries\r\nSELECT * INTO [STAGE].[Department] \r\n  FROM [HumanResources].[Department]\r\nGO\r\n\r\n-- Add a default date\r\nALTER TABLE [STAGE].[Department] ADD CONSTRAINT df_Modified_Date\r\n  DEFAULT GETDATE() FOR [ModifiedDate];\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Common table expressions can be used as the target of an UPDATE <\/a>statement.  The query below updates the two Quality Assurance deparment names with a location ‘US’ as a prefix.  We are doing this to get ready for making a ASIA department with the same job functions.<\/p>\n
-- Update group to US\r\nWITH CTE_DEPT (GroupName)\r\nAS\r\n(\r\n    SELECT GroupName FROM [STAGE].[Department]\r\n    WHERE GroupName = 'Quality Assurance'\r\n)\r\nUPDATE CTE_DEPT SET GroupName = 'US Quality Assurance';\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Common table expressions can be inserted into directly if they are based upon a simple table.  Just like insertion rules for views, aggregation and joins can make a CTE non-deterministic.  The query below INSERT<\/a>s two new ASIA departments like their US counterparts.<\/p>\n
-- Insert group for ASIA\r\nWITH CTE_DEPT(Name, GroupName)\r\nAS\r\n(\r\n      SELECT Name, GroupName\r\n      FROM [STAGE].[Department]\r\n      WHERE GroupName = 'US Quality Assurance'\r\n)\r\nINSERT INTO CTE_DEPT\r\nSELECT D.Name, 'ASIA ' + SUBSTRING(D.GroupName, 4, LEN(D.GroupName) - 3) \r\nFROM CTE_DEPT AS D\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
Last but not least, derived tables can be used as the target of a DELETE <\/a>statement.  <\/p>\n
-- Delete group for US\r\nWITH CTE_DEPT(Name, GroupName)\r\nAS\r\n(\r\n      SELECT Name, GroupName\r\n      FROM [STAGE].[Department]\r\n      WHERE GroupName = 'US Quality Assurance'\r\n)\r\nDELETE FROM CTE_DEPT;\r\nGO\r\n\r\n-- Show the data\r\nSELECT * FROM [STAGE].[Department];\r\nGO\r\n<\/span><\/pre>\n<\/p>\n
In short, common table expressions (CTE’s) are useful when you want to combine the results of several CTE’s into one large result set.  The real power of CTE’s comes with recursion<\/a>.  Traversing a hierarchial table structure can be accomplished with self referencing CTE’s.  I did not talk about this topic since it is not related to storing results into a temporary table (object).  <\/p>\n
Next time, I want to analyze performance, disk space, and query plans of all five ways to create temporary objects to solve a business problem.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today, I am going to talking about common table expressions. A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single DML statement. A CTE is simular to a derived table in which the results are not stored as an object; However, CTE’s can be recursive or built upon other CTE’s. The WITH key word is used to indicate the start of a CTE and needs to be preceeded with a semicolon. The rest of the syntax is like a stored…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[247,33,12,30,15,37,28,29,32],"class_list":["post-1234","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-common-table-expressions","tag-delete","tag-free-code","tag-insert","tag-john-f-miner-iii","tag-select","tag-sql-server","tag-tsql","tag-update"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1234","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1234"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/1234\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1234"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}