T-SQL VIEW - CTE + UNPIVOT versus UNIONs versus other tehnique

Question

I wonder which solution is better. I have to declare some variables in view, which are calculated using T-SQL date functions (DATEADD,DATEPART,GETDATE(),etc).

After some research I've write this:

WITH DatePeriods(ThisWeek,LastWeek,MonthToDate,QuarterToDate,YearToDate) AS
(
    SELECT  "...date functions..." AS ThisWeek
            "...date functions..." AS LastWeek
            "...date functions..." AS MonthToDate
            "...date functions..." AS QuarterToDate
            "...date functions..." AS YearToDate
)
SELECT Desciption,Value
FROM DatePeriods
UNPIVOT
(
    Value FOR Desciption IN (ThisWeek,LastWeek,MonthToDate,QuarterToDate,YearToDate)
) AS Source

And it looks cool because I have "cte" and "unpivot". If I want to add other date variable I only should insert in the CTE's select.

And the other solution is using ordinary "union":

SELECT  'ThisWeek',"...date functions..." AS ThisWeek
UNION
SELECT  'LastWeek',"...date functions..." AS LastWeek
UNION
SELECT  'MonthToDate',"...date functions..." AS MonthToDate
UNION
SELECT  'QuarterToDate',"...date functions..." AS QuarterToDate
UNION
SELECT  'YearToDate',"...date functions..." AS YearToDate

I think it is not so good because a new date variable means new union, but after all union between few variables only.

Can anyone tell me which technique is the good practice in this case or even offers other solution?

Thanks in advance.

EDIT:

This is the output that I want:

Desciption      Value
ThisWeek        2012-08-05 08:55:23.013
LastWeek        2012-07-29 08:55:23.013
MonthToDate     2012-07-08 08:55:23.013
QuarterToDate   2012-05-08 08:55:23.013
YearToDate      2011-08-08 08:55:23.013

@bluefeet Yes, I have but I am not really a pro when it comes to compare execution plans. Something more, there are a lot of things happening during execution of each query, but they both are executed for 0 seconds. And almost each item in the plan has cost 0%. — gotqn, Aug 08 '12 at 14:22

score 5 · Accepted Answer · answered Aug 08 '12 at 14:23

If you have a look at the query plans you will see that your union version has much higher cost than your unpivot version. But if you change to union all instead it will be better than unpivot.

If you are on SQL Server 2008 or later you can use values instead and according to the execution plan the cost is the same as union all.

Values version:

select Description, Value
from (values ('ThisWeek',      getdate()+1),
             ('LastWeek',      getdate()+2),
             ('MonthToDate',   getdate()+3),
             ('QuarterToDate', getdate()+4),
             ('YearToDate',    getdate()+5)
     ) as T(Description, Value)

Union all version:

SELECT  'ThisWeek' AS Description, getdate()+1 AS Value
UNION ALL
SELECT  'LastWeek', getdate()+2
UNION ALL
SELECT  'MonthToDate', getdate()+3 
UNION ALL
SELECT  'QuarterToDate', getdate()+4 
UNION ALL
SELECT  'YearToDate', getdate()+5

The reason for union is slower than union all is because it tries to remove duplicates from the result set where union all incorporates all rows regardless of value.

T-SQL VIEW - CTE + UNPIVOT versus UNIONs versus other tehnique

1 Answers1