Real World Common Table Expressions
Maurice writes our newsletter today with some gold nuggets wrapped around different uses for Common Table Expresssions
CTE are a great way to express business logic into SQL, by breaking querying elements into more meaningful part.
I usually give them long names that express the meaning of each step into SQL.
CTE makes rewriting of table function into inline function, which help the optimizer rewrites into a more suited access plan. Such inline functions are more used as a macro for which the optimizer expand into the access only the used code. The optimizer is able to strip out unused expressions. Inline functions could then be turned into handy toolbox, and such inline function could not be written as inline without CTE. CROSS APPLY + Inline function are an elegant, set-based way to pack business logic into SQL. They allows rewriting of a lot of procedural logic into more readable and shorter set-based logic, which is almost all the time a lot faster that procedural logic.
Sometimes I also use them also to generate data, as for example a truth table, or hide expressions into simple and more meaningful column names, which make the query code more meaningful, readable and less bloated.
A lot a procedural code is easily rewritable into set based expression using CTE, virtual truth table created by a CTE are an example of this.
-- By cross applying (CROSS APPLY in from clause) this fonction to date data, it makes
-- available useful derived columns from a given date. The optimizer however only expand into access plan expressions
-- related to columns used. Optimization leaves no trace of the function call, just expressions.
-- These properties are only possible because this is an inline table, and without CTE this function
-- would be barely readable.
create function dbo.DateTimeToolBox (@d datetime)
returns table
as
return
(
With DayNumExpr (dayNum) as (select (DATEPART(dw, @d) + @@DATEFIRST) % 7)
, InfoJr as
(
Select
dayNum
, rtrim(SUBSTRING('Saturday Sunday Monday Tuesday WednesdayThursday Friday ', DayNum*9+1, 9)) as DayNameEng
, rtrim(SUBSTRING('Samedi Dimanche Lundi Mardi Mercredi Jeudi Vendredi ', DayNum*9+1, 9)) as DayNameFr
, dateadd(yy, datediff(yy, '19000101', @d), '19000101') as StartOfYear
, dateadd(yy, 1+datediff(yy, '19000101', @d), '19000101') as StartOfNextYear
, dateadd(mm, datediff(mm, '19000101', @d), '19000101') as StartOfMonth
, dateadd(mm, 1+datediff(mm, '19000101', @d), '19000101') as StartOfNextMonth
, dateadd(dd, datediff(dd, '19000101', @d), '19000101') as StartOfDay
, dateadd(hh, datediff(hh, '19000101', @d), '19000101') as StartOfHour
From DayNumExpr
)
, DateTimeToolBoxInfoAndJulianStuff as
(
Select
*
, Datediff(dd, StartOfYear, @d) as JulianDate
, Datediff (dd, StartOfMonth, StartOfNextMonth) AS NbOfDayInMonth
, Case when Datediff (dd, StartOfYear, StartOfNextYear) = 366 Then 1 Else 0 End as LeapYear
From InfoJr
)
Select *
From DateTimeToolBoxInfoAndJulianStuff
)
go
-- this function uppercase characters and for those with accents
-- remove accents
create function dbo.UpperWithNoAccent(@chn NVarchar(max))
returns NVarchar(max)
as
Begin
-- CTE that avoid the mess of nesting multiple replaces, that
-- replaces accented characters by a non-accented version
with
c1 as (select (UPPER(@chn) COLLATE FRENCH_CI_AI) as C)
, c2 as (select REPLACE(c, 'a', 'A') as c from c1)
, c3 as (select REPLACE(c, 'e', 'E') as c from c2)
, c4 as (select REPLACE(c, 'i', 'I') as c from c3)
, c5 as (select REPLACE(c, 'o', 'O') as c from c4)
, c6 as (select REPLACE(c, 'u', 'U') as c from c5)
, c7 as (select REPLACE(c, 'y', 'Y') as c from c6)
, c8 as (select REPLACE(c, 'n', 'N') as c from c7)
, c9 as (select REPLACE(c, 'c', 'C') as c from c8)
, c10 as (select REPLACE(c, 'd', 'd') as c from c9)
select @chn = c from c9 -- oublie pas de prendre le dernier de la liste
Return (@chn collate database_default)
End
go
select dbo.UpperWithNoAccent(N'áàâãäåæçéêëìíîïñòóôõöùúûüýÿ????ñåãbcdefghijhklmnopqrstuvwxyz')
go
-- function that use CTE to create multi-rows truth table from its parameters
Create FUNCTION dbo.FE_Freq
(
@SelTyp NCHAR(1)
, @RefDate SMALLDATETIME
, @FreqTypFINCOP NCHAR(1)
, @FreqTypFRE NCHAR(1)
, @FreqTypSAN NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
--Truth table the convert parameters into selectable data (one or more rows)
WITH FreqTypFilters (FreqTypFilter) AS
(
SELECT 'FIN' WHERE @FreqTypFINCOP > '0'
UNION all SELECT 'COP' WHERE @FreqTypFINCOP = '1'
UNION all SELECT 'FRE' WHERE @FreqTypFRE = '1'
UNION all SELECT 'SAN' WHERE @FreqTypSAN = '1'
)
--Control info
, IdClient1_Or_SchoolYrStartDate
as
(
Select F.Id, F.Yr, F.freq
From
Client1ou2_AvecAucunTypeCOP_Par_IdYrSchoolStartDate X
Join
E_Freq F
ON F.Id = X.Id
AND F.Yr = X.Yr
AND F.Client = X.Client
AND F.School = X.School
AND F.StartDate = X.StartDate
Where
F.FreqTyp in (Select FreqTypFilter From FreqTypFilters)
)
....
go
Thanks Maurice, for the detailed response.
Feel free to send any ideas on this or any other topic you think we should discuss to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Troubleshooting problems with SQL Server 2008 R2 cluster
In this article, Alexander Chigrik explains some problems that you can have when you work with SQL Server 2008 R2 cluster.
Featured White Paper(s)
Encryption & Key Management for Microsoft SQL Server 2008
Written by Townsend Security
Simplify encryption and key management on … (read more)