CTE (Common Table Expression) syntax can be a little tricky at first, but once you have a little primer, you will love using them as much as I do. Examine the following code below that uses a derived table from which to select. (Please realize that this is a contrived example and you could of course just join the tables in the query directly.)<\/p>\n
\r\nUSE tempdb\r\nGO\r\n\r\nSELECT t.TableName, c.ColumnName\r\nFROM (SELECT OBJECT_ID AS Id, [Name] AS TableName FROM sys.tables) t\r\nINNER JOIN (SELECT OBJECT_ID AS Id, [Name] AS ColumnName FROM sys.columns) c ON t.Id = c.Id\r\n<\/pre>\nThe query between the parentheses creates a table structure (that I aliased as “c” and “t” for my two) that only lasts for the life of the query. I can then reference those tables in the query in any way that I would a regular table. However, that syntax can make it very difficult to read, especially in long queries with several complicated derived tables.<\/p>\n
Sql Server 2005 introduced a new syntax to be used instead called Common Table Expressions (CTEs). Using a CTE, the above query would be written like this<\/p>\n
\r\nUSE tempdb\r\nGO\r\n\r\nWITH Tbls AS\r\n(\r\n\tSELECT OBJECT_ID AS Id, [Name] AS TableName\r\n\tFROM sys.tables\r\n),\r\nClmns AS\r\n(\r\n\tSELECT OBJECT_ID AS Id, [Name] AS ColumnName\r\n\tFROM sys.columns\r\n)\r\n\r\nSELECT t.TableName, c.ColumnName\r\nFROM Tbls t\r\nINNER JOIN Clmns c ON t.Id = c.Id\r\n<\/pre>\nAlternatively, you can also explictly declare you column names in the CTE definition and not have to alias the columns in the query itself.<\/p>\n
\r\nUSE tempdb\r\nGO\r\n\r\nWITH Tbls (Id, TableName) AS\r\n(\r\n\tSELECT OBJECT_ID, [Name]\r\n\tFROM sys.tables\r\n),\r\nClmns (Id, ColumnName) AS\r\n(\r\n\tSELECT OBJECT_ID, [Name]\r\n\tFROM sys.columns\r\n)\r\n\r\nSELECT t.TableName, c.ColumnName\r\nFROM Tbls t\r\nINNER JOIN Clmns c ON t.Id = c.Id\r\n<\/pre>\nOne “gotcha” is that the CTE – beginning with the WITH keyword – must be the first statement that is encountered. All previous statements above it must be terminated with a GO or a semicolon. If you ran the following<\/p>\n
\r\nUSE tempdb\r\nGO\r\n\r\nSELECT 'Here Comes the Error'\r\n\r\nWITH Tbls (Id, TableName) AS\r\n(\r\n\tSELECT OBJECT_ID, [Name]\r\n\tFROM sys.tables\r\n),\r\nClmns (Id, ColumnName) AS\r\n(\r\n\tSELECT OBJECT_ID, [Name]\r\n\tFROM sys.columns\r\n)\r\n\r\nSELECT t.TableName, c.ColumnName\r\nFROM Tbls t\r\nINNER JOIN Clmns c ON t.Id = c.Id\r\n<\/pre>\nYou’d get the errors shown below (or something very close)<\/p>\n
\r\nMsg 102, Level 15, State 1, Line 4\r\nIncorrect syntax near 'Tbls'.\r\nMsg 102, Level 15, State 1, Line 8\r\nIncorrect syntax near ','.\r\n<\/pre>\nSome people even just make a habit of actually prefixing the WITH with a semicolon every time to make sure that everything goes off without a hitch. If I do run this script instead there are no issues.<\/p>\n
\r\nUSE tempdb\r\nGO\r\n\r\nSELECT 'No Problems Here'\r\n\r\n;WITH Tbls (Id, TableName) AS\r\n(\r\n\tSELECT OBJECT_ID, [Name]\r\n\tFROM sys.tables\r\n),\r\nClmns (Id, ColumnName) AS\r\n(\r\n\tSELECT OBJECT_ID, [Name]\r\n\tFROM sys.columns\r\n)\r\n\r\nSELECT t.TableName, c.ColumnName\r\nFROM Tbls t\r\nINNER JOIN Clmns c ON t.Id = c.Id\r\n<\/pre>\nThis is just a quick primer on CTEs, but I hope that you can find it useful. I really find that it cleans my code up quite a bit.<\/p>\n","protected":false},"excerpt":{"rendered":"
CTE (Common Table Expression) syntax can be a little tricky at first, but once you have a little primer, you will love using them as much as I do. Examine the following code below that …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,87],"tags":[89,4],"class_list":["post-128","post","type-post","status-publish","format-standard","hentry","category-code-tips","category-sql","tag-code-tips","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts\/128","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/comments?post=128"}],"version-history":[{"count":0,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/posts\/128\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/media?parent=128"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/categories?post=128"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.peteonsoftware.com\/index.php\/wp-json\/wp\/v2\/tags?post=128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}