8/29/17

By request

DECLARE @start datetime, @numberofyears int
SET @start = '1900-01-01'
SET @numberofyears = 180
;WITH a1(col)
AS (
SELECT NULL
UNION ALL
SELECT NULL
), a2(col)
AS (
SELECT NULL FROM a1 x1 CROSS JOIN a1 x2
), a3(col)
AS (
SELECT NULL FROM a2 x1 CROSS JOIN a2 x2
), a4(col)
AS (
SELECT NULL FROM a3 x1 CROSS JOIN a3 x2
), a5(col)
AS (
SELECT NULL FROM a4 x1 CROSS JOIN a4 x2
), nums(num)
AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 FROM a5
), dates(datetime)
AS (
SELECT DATEADD(day, num, @start) FROM nums WHERE DATEADD(day, num, @start) < DATEADD(year, @numberofyears, @start)
)
SELECT YEAR(datetime) * 10000 + MONTH(datetime) * 100 + DAY(datetime) AS DATEKEY, datetime FROM dates

8/18/17

If you don't like your query plan!

In one of the cool channels us SQLMVPs communicate through the question was raised about why do the execution plan do what they do, and we started to elaborate about tweaks and twerks, and apply hinting in one way or the other. Sometimes you need to do these ninja moves, but usually it's because your statistics are skewed and/or your cached query plan is to old and the data has changed to much. This is usually fixed by updating the statistics or rebuild/reorganize your indexes and updating the statistics. It might be that you need to revamp your indexes too. Maybe wrong cluster key och you need a non-clustered index and include some columns. This if the query you're trying to run is a common query or not. Sometimes even a filtered index might be needed, or sometimes partitioning can be of help. Major changes should only be done if this is a common and re-occuring query.

I'd like to end this post with a lovely quote:

I encourage you to consider updating statistics rather than trying syntactic rewrites. Such things worked on rule-based optimizers but it is a goal of the SQL Server QP to avoid these kinds of gymnastics to the extent possible. Focus on the plans that come out and whether the optimizer has the right info to get you the plan shape you think is best more than the syntax. -Conor Cunningham