I’m giving a free webinar for MSSQLTips.com at 11th of August 2022. The abstract:

When building a data warehouse (DWH) in the Microsoft Data Platform stack, you typically have to write a fair share of T-SQL. Loading data into the data warehouse, transforming it into the desired format, troubleshooting and debugging, and writing source queries for reports; all of those tasks require some T-SQL and preferably it’s fast as well.

In this session, we’ll go over some T-SQL best practices from the viewpoint of the BI developer. We’ll also cover indexing for the data warehouse and how you can make that SSRS report a bit faster. In the end, you’ll walk out with solid knowledge of how to improve your T-SQL skills and deliver results faster. Most of this will also be directly transferable to other database vendors.

Note – basic knowledge of T-SQL and SQL Server is assumed.

Basically this presentation is about the idea “if I had to coach a junior starting on a business intelligence project, what would I tell that person regarding T-SQL performance tuning?”. You can register here.

It’s the second Tuesday of the month, so it’s time for the T-SQL Tuesday blog party! This month’s topic is chosen by Camila Henrique (blog): what T-SQL advice would you give to your younger self? Here’s a little list I’d tell anyone starting out with T-SQL:

• you can configure most tools that when you hit Tab, it enters 4 spaces instead
• get the difference between set-based processing and RBAR (aka cursors)
• learn the different types of JOINs
• don’t try to write one giant SQL statement that does it all. Either use common table expressions to clarify your code, or split up and use (temp) tables to store intermediate results.
• learn how indexes work. They’re one of the most fundamental things to learn when it comes to performance tuning.
• learn about SARGable queries, so your indexes are actually used
• learn about window functions, because they’re awesome. I can absolutely recommend the book by Itzik Ben-Gan.
• put everything in source control
• comment your code
• don’t spend too much time on hierarchyid, XML and other exotic stuff, nobody uses it (but you need to learn it for the certification exams)
• learn about dynamic SQL and how you can generate stuff based on metadata
• learn the logical query processing order. E.g. SELECT comes last, FROM comes first.
• get the subtle difference between DISTINCT and GROUP BY to remove duplicates. And don’t forget UNION and UNION ALL are not the same.

Linting is a process where a code analyzer is run to find programmatic errors, bugs, “stylistic constructs” or anti-patterns. For example, in Python there are specific modules that help you with linting when the code is build. If the code is not indented correctly (or whatever rule your code has to obey in Python), the line is flagged in the build. Depending on the implementation, the build gives a warning, or if they’re really strict, the build fails.

Wouldn’t it be nice if we could do the same for T-SQL? That when someone tries to push a SELECT * or a script with a NOLOCK hint to the server, the build fails and the T-SQL script never makes it to the server? Well, your dreams have come true! (well, if this is your dream at least)

ScriptDom is a free tool from the SQL Server DacFx that can make this happen. It’s a .NET library that can parse and analyze your T-SQL scripts and find potential anti-patterns like the ones mentioned above. At SQLBits 2022, Mala Mahadevan and I are giving a session on this very topic. Check it out if you want a deep dive into this technology. Mala has written a couple of PowerShell scripts for the demos, which can be found on GitHub. The focus of this blog post is not explaining how those PS scripts work, but rather how you can integrate those into an Azure Devops Build Pipeline (if the recording of the SQLBits session is live, I’ll link to it if you’re interested in more detail).

To make this work, you need the following:

• an Azure Devops git repo
• in that repo, which is cloned to a local folder on your machine, you have a bunch of .sql files. This can be a folder with some scripts, or the entire project structure of a SQL Server Data Tools (SSDT) database project from Visual Studio.
• also in the same repo, a folder with the PS scripts from Mala, and the ScriptDom .dll file (you can find it C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.TransactSql.ScriptDom.dll after you’ve installed either sqlpackage or SQL Server itself. Of course the version number can vary.) We include the .dll file because it will typically not be presented on the machine of the build agent.

This is how my repo looks like:

The .yml files are automatically added when a build pipeline is created (more on that later). The StackOverflow_UnsungHeroes.sql file is a SQL script where I’ve added a NOLOCK hint. Inside the UnitTests folder, I have a folder with the PS scripts and the .dll file:

All the scripts starting with “Find” are from Mala (again, check her GitHub). The CheckLintingErrors script is a wrapper I wrote. It will take the default working directory and search for all .sql files. It also includes references to the .dll file and the PS files.

$folder = $env:System_DefaultWorkingDirectory

Add-Type -Path "./UnitTests/PowerShell/Microsoft.SqlServer.TransactSql.ScriptDom.dll"

. ./UnitTests/PowerShell/Find-LintingErrorsForAzure.ps1
. ./UnitTests/PowerShell/Find-4PartNameWithPattern.ps1
. ./UnitTests/PowerShell/Find-NoLockHintWithPattern.ps1
. ./UnitTests/PowerShell/Find-SelectStarWithPattern.ps1

$files = Get-ChildItem $folder -Recurse -Include *.sql

foreach ($f in $files){

    Write-Host $f.FullName
    Find-LintingErrors $f
}

Once this is set-up, we can create our build pipeline. In Azure Devops, go to the Pipelines section.

In Pipelines, we can create a new build pipeline (you can create a release pipeline under the tab Releases. It’s a slightly different pipeline with a different purpose, but you should be able to integrate ScriptDom in a release pipeline as well). Click on the New Pipeline button and select Azure Repos Git as the source for your code.

Select the repository with your T-SQL scripts.

Azure Devops will analyze the code in your repo and suggest a build template. As usual, these things don’t care about SQL so you’ll be presented with the options to either use a default starter pipeline, or to use your own YAML file. The first option is what we need.

The build pipeline is now “ready”. It will run some standard Hello World PowerShell scripts.

You can get rid of the scripts, and when you click on a line below “steps:”, you can insert a new PS script from the Tasks menu:

In the settings, choose “File Path” and reference the CheckLintingErrors script.

The full path is “$(System.DefaultWorkingDirectory)/UnitTests/PowerShell/CheckLintingErrors.ps1”. The YAML script should be like this:

# Starter pipeline
# Start with a minimal pipeline that you can customize to build and deploy your code.
# Add steps that build, run tests, deploy, and more:
# https://aka.ms/yaml

trigger:
- dev

pool:
  vmImage: ubuntu-latest

steps:

- task: PowerShell@2
  inputs:
    filePath: '$(System.DefaultWorkingDirectory)/UnitTests/PowerShell/CheckLintingErrors.ps1'

And that’s it. When you run the pipeline, it will check the .sql scripts for a couple of common anti-patterns. However, to make it really useful, we need to make some minor adjustments. In the scripts from Mala, when something is found it’s just written to the screen with the Write-Host command. If we would do this in the build pipeline, you would have to dig through the log of the build to find these messages. It would be better if these would just pop-up in the automatic e-mail that Azure Devops sends. Without any adjustments, the build will succeed and you would be none the wiser.

With the following line, we can add a warning that will be picked up by the build pipeline (I’ve added this in the Find-NoLockHintWithPattern.ps1 script):

# Writes a warning to build summary and to log in yellow text
Write-Host  "##vso[task.LogIssue type=warning;]NOLOCK DETECTED!"

The result:

And with the following line, we can add an error:

# Writes an error to build summary and to log in red text
Write-Host  "##vso[task.LogIssue type=error;]I'm serious, NOLOCK! You want ghost data?"

This looks already a bit more alarming when you get it in your inbox:

However, the build still succeeds. If you really want to make sure not a single NOLOCK makes it into your database server, we got to nuke that pipeline. Simply add the line “exit 1” to your PS script to make the build crash.

This will lead to 2 errors, and a failed build.

Mission accomplished!

Recently I was working with a client who was still on SQL Server 2012. Support issues aside, there are some very useful T-SQL functions/clauses who didn’t exist in 2012, but were introduced in a later version. One of them is AT TIME ZONE, which you can use the convert dates between time zones, as demonstrated in the blog post Converting a Datetime to UTC. But alas, no such thing in SQL Server 2012. I searched the web a bit, and most suggestions took the route of CLR.

Yeah, I favor a pure T-SQL approach instead. On a StackOverflow thread, I found the inspiration for the following script:

CREATE FUNCTION dbo.ConvertLocalDateToUTC(@localDate DATETIME)
RETURNS TABLE
AS
RETURN
WITH cte_dstperiod AS
(
SELECT
     dststart   = DATEADD(HOUR, 2, --> starts at 2 o'clock
                        DATEADD(DAY
                                ,1 - DATEPART(WEEKDAY --> assumes datefirst is set to 7 (Sunday is first day of the week)
                                             ,DATEADD(DAY, -1, DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))) --> March 31
                                ,DATEADD(DAY, -1, DATEADD(MONTH, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))))
    ,dstend     = DATEADD(HOUR, 2, --> starts at 2 o'clock
                        DATEADD(DAY
                                ,1 - DATEPART(WEEKDAY --> substract the number of days to go back to the last Sunday of the month
                                             ,DATEADD(DAY, -1, DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))) --> October 31
                                ,DATEADD(DAY, -1, DATEADD(MONTH, 10, DATEADD(YEAR, DATEDIFF(YEAR, 0, @localDate), 0)))))
)
,   cte_dst AS
(
    SELECT
         dst    = IIF(@localDate >= dststart AND @localDate < dstend,1,0) --> is the supplied date in a DST period?
        ,offset = +60 --> CET = UTC + 1 (60 minutes)
    FROM cte_dstperiod
)
,   cte_offset AS
(
    SELECT
         dstFlag        = dst
        ,offset
        ,localdate      = @localDate
        ,localdateTZ    = IIF(dst = 1
                            ,TODATETIMEOFFSET(@localDate, offset + 60)
                            ,TODATETIMEOFFSET(@localDate, offset))
    FROM cte_dst
)
SELECT
     dstFlag    
    ,offset     
    ,localdate  
    ,localdateTZ
    ,UTCdate = CONVERT(DATETIME,localdateTZ,1)
FROM cte_offset;

It’s an inline table-valued function (yay performance) which takes the local datetime as an input parameter. The offset (configured in the CTE cte_dst) is set to 60 minutes, which is my time zone (CET which is GMT + 1). If you need something else, you can change it to whatever offset your time zone is in. There are two major assumptions:

• the datefirst system setting is set to 7 (aka Sunday). This is because it is the default in most systems. If you need something else, you might want to use SET DATEFIRST before you call the function, or you adapt the logic in the first CTE.
• daylight savings time starts on the last Sunday of March, and ends on the last Sunday of October. Again, adapt if necessary.

If those assumptions don’t work for you, you can just create a reference table with all the start and end dates of the DST for all the necessary years. I didn’t do that because I’m lazy and because I also want to leave the client with a script where they didn’t need to remember to update a table once in a while.

You can find the script on Github as well.

I’m delighted to announce I’ll be speaking at the SQLBits conference in March 2022! I’m not delivering one session, but two this time. Well, actually, for one session I’m co-presenting with Mala Mahadevan. I’m really looking forward to this session, as it’s a not-well known topic (I hadn’t heard about it till Mala told me everything about it): Finding and Fixing T-SQL Anti-Patterns with ScriptDOM. Yups, bet you haven’t heard of it either ScriptDOM is included with SQL Server and it’s a bunch of .NET classes (which you can also use in PowerShell of course) that allow you to parse T-SQL automatically and search for certain patterns in the T-SQL script. For example, you can use it to find out if those sneaky developers have been using SELECT * or NOLOCK. The abstract:

Quality code is free of things we call ‘anti-patterns’ – nolock hints, using SELECT *, queries without table aliases and so on.
We may also need to enforce certains standards: naming conventions, ending statements with semicolons, indenting code the right way etc. Furthermore, we may need to apply specific configurations on database objects, such as to create tables on certain filegroups or use specific settings for indexes.

All of this may be easy with a small database and a small volume of code to handle, but what happens when we need to deal with a large volume of code? What if we inherit something full of these anti-patterns, and we just don’t have time to go through all of it manually and fix it? But suppose we had an automated utility that could do this for us? Even better, if we could integrate it in our Azure Devops pipelines?

ScriptDOM is a lesser-known free tool from SQL Server DacFx which has the ability to help with finding programmatic and stylistic errors (a.k.a linting) in T-SQL code.It can even fix some of these errors!
In this session we will learn about what it is, how we can harness its power to read code and tell us what it finds, and actually fix some of those anti-patterns.
Join us for this highly interactive and demo-packed session for great insights on how to improve the quality of your code. Basic knowledge of T-SQL and Powershell is recommended to get the most out of this session.

The other session is my “Introduction to Snowflake on Azure”. Both sessions are 50 minutes long and will be presented virtually. SQLBits itself is hybrid, meaning there will be on-premises sessions, but also a number of virtual sessions. You can find more info on the SQLBits website. The theme at SQLBits this year is arcade gaming:

It’s this time of the month again! T-SQL Tuesday is a monthly blogging party on the second Tuesday. This month’s topic is “What are your go to handy short scripts?” brought to you by John McCormack.

What are those little short bits of code that you can’t live without? I’m talking about little snippets that are only a few lines, that you may even have memorised. It could be T-SQL, PowerShell, Python or anything else you use day to day.

One piece of T-SQL snippet I use a lot is one to generate a tally table (aka numbers table). It’s based upon the Itzik Ben-Gang style of generating a virtual table using cross joins:

WITH T0 AS (SELECT N	 FROM (VALUES (1),(1)) AS tmp(N))
	,T1 AS (SELECT N = 1 FROM T0 AS a CROSS JOIN T0 AS b)
	,T2 AS (SELECT N = 1 FROM T1 AS a CROSS JOIN T1 AS b)
	,T3 AS (SELECT N = 1 FROM T2 AS a CROSS JOIN T2 AS b)
	,T4 AS (SELECT N = 1 FROM T3 AS a CROSS JOIN T3 AS b)
SELECT RID = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM T4
ORDER BY RID;

For those of you who don’t know what a tally table is, it’s a construct that allows you to mimic looping behavior in SQL, instead of writing an explicit WHILE loop or a cursor. The advantage is that it is set-based which SQL Server loves and thus wicked fast in many situations. More info here and here. With some slight modifications, we can use it to generate dates as well:

WITH T0 AS (SELECT N	 FROM (VALUES (1),(1)) AS tmp(N))
	,T1 AS (SELECT N = 1 FROM T0 AS a CROSS JOIN T0 AS b)
	,T2 AS (SELECT N = 1 FROM T1 AS a CROSS JOIN T1 AS b)
	,T3 AS (SELECT N = 1 FROM T2 AS a CROSS JOIN T2 AS b)
	,T4 AS (SELECT N = 1 FROM T3 AS a CROSS JOIN T3 AS b)
SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1,'2000-01-01')
FROM T4;

Speaking of dates, another script I use from time to time is one that generates a date table. Pretty useful at the start of a BI project or for a proof of concept. And yes, it uses a tally table

DECLARE @datefrom DATE;
DECLARE @dateto DATE;
    
SET DATEFIRST 1;  -- Set monday as first day of week
    
-- Insert date Records
SELECT @datefrom = '2015-01-01',@dateto = '2035-12-31';
    
WITH cte_tally AS
(
    SELECT Num = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
    FROM		sys.columns c
    CROSS JOIN	sys.columns c1
)
,	cte_dates AS
(
    SELECT
    		[date] = DATEADD(day, Num, @datefrom)
    	,Num
    FROM cte_tally
    WHERE	Num >= 0
        AND Num <= DATEDIFF(day, @datefrom, @dateto)
)
SELECT
     SK_Date                = d.[date]
    ,[DateDesc]				= CONVERT(CHAR(11),d.[date],120)
    ,[WeekNbr]				= DATEPART(WEEK,d.[date])
    ,[MonthNbr]				= DATEPART(MONTH,d.[date])
    ,[QuarterNbr]			= DATEPART(QUARTER,d.[date])
    ,[SemesterNbr]			= CASE WHEN DATEPART(MONTH, d.[date]) <= 6 THEN 1 ELSE 2 END
    ,[Year]					= DATEPART(YEAR, d.[date])
    ,[DayName]				= DATENAME(WEEKDAY, d.[date])
    ,[WeekName]				= 'W' + CAST(DATEPART(WEEK,d.[date]) AS VARCHAR(2))
    ,[MonthName]			= DATENAME(MONTH,d.[date])
    ,[QuarterName]			= 'Q' + CAST(DATEPART(QUARTER,d.[date]) AS CHAR(1))
    ,[SemesterName]			= 'S' + CAST(CASE WHEN DATEPART(m, d.[date]) <= 6 THEN 1 ELSE 2 END AS CHAR(1))
    ,[YearWeek]				= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + RIGHT('0' + CAST(DATEPART(WEEK, d.[date]) AS VARCHAR(2)),2)
    ,[YearWeekDesc]			= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + ' W' + CAST(DATEPART(WEEK,d.[date]) AS VARCHAR(2))
    ,[YearMonth]			= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + SUBSTRING(CONVERT(VARCHAR(6),d.[date],112),5,2)
    ,[YearMonthDesc]        = CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + '-' + RIGHT('0' + CAST(DATEPART(MONTH, d.[date]) AS VARCHAR(2)),2)
    ,[YearMonthDescFull]	= DATENAME(MONTH,d.[date]) + ' ' + CAST(DATEPART(YEAR, d.[date]) AS CHAR(4))
    ,[YearQuarter]			= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + CAST(DATEPART(QUARTER, d.[date]) AS CHAR(1))
    ,[YearQuarterDesc]		= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + ' ' + 'Q' + CAST(DATEPART(QUARTER,d.[date]) AS CHAR(1))
    ,[YearSemester]			= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + CASE WHEN DATEPART(MONTH, d.[date]) <= 6 THEN '1' ELSE '2' END
    ,[YearSemesterDesc]		= CAST(DATEPART(YEAR, d.[date]) AS CHAR(4)) + ' ' + 'S' + CAST(CASE WHEN DATEPART(m, d.[date]) <= 6 THEN 1 ELSE 2 END AS CHAR(1))
    ,[IsWeekDay]			= CASE WHEN DATEPART(WEEKDAY, d.[date]) < 6 THEN 'yes' ELSE 'no' END
    ,[IsMonthFirstDay]		= CASE WHEN d.[date] = DATEADD(MONTH, DATEDIFF(MONTH, 0, d.[date]), 0) THEN 'yes' ELSE 'no' END
    ,[IsMonthLastDay]		= CASE WHEN d.[date] = EOMONTH(d.[date],0) THEN 'yes' ELSE 'no' END
    ,[MonthFirstDay]		= DATEADD(DAY,1,EOMONTH(d.[date],-1))
    ,[MonthLastDay]			= EOMONTH(d.[date],0)
    ,[DayOfWeek]			= DATEPART(WEEKDAY, d.[date])
    ,[DayOfMonth]			= DATEPART(DAY, d.[date])
    ,[DayOfQuarter]			= DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,d.[date]),0),d.[date]) + 1
    ,[DayOfSemester]		= CASE	WHEN DATEPART(QUARTER,d.[date]) IN (1,3) -- day of semester is equal to day of quarter in the quarters 1 and 3
    									THEN DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,d.[date]),0),d.[date]) + 1
    									ELSE CASE WHEN DATEPART(QUARTER,d.[date]) = 2 -- take the last day of quarter of quarter 1 + day of quarter of quarter 2
    												THEN DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,DATEFROMPARTS(YEAR(d.[date]),3,31)),0),DATEFROMPARTS(YEAR(d.[date]),3,31)) + 1 + DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,d.[date]),0),d.[date]) + 1
    												-- take the last day of quarter of quarter 3 + day of quarter of quarter 4
    												ELSE DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,DATEFROMPARTS(YEAR(d.[date]),9,30)),0),DATEFROMPARTS(YEAR(d.[date]),9,30)) + 1 + DATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,d.[date]),0),d.[date]) + 1
    											END
    							END
    ,[DayOfYear]			= DATEPART(DAYOFYEAR, d.[date])
    ,[DaysAgo]				= DATEDIFF(DAY,d.[date],CAST(GETDATE() AS DATE))
    ,[WeeksAgo]				= DATEDIFF(WEEK,d.[date],CAST(GETDATE() AS DATE))
    ,[MonthsAgo]			= DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE))
    ,[QuartersAgo]			= DATEDIFF(QUARTER,d.[date],CAST(GETDATE() AS DATE))
    ,[SemestersAgo]			= CASE WHEN DATEDIFF(QUARTER,d.[date],CAST(GETDATE() AS DATE)) % 2 = 0 -- even number of quarters ago
    									THEN DATEDIFF(QUARTER,d.[date],CAST(GETDATE() AS DATE)) / 2 -- there are 2 quarters in a semester
    									ELSE (DATEDIFF(QUARTER,d.[date],CAST(GETDATE() AS DATE)) + 1) / 2
    							END
    ,[YearsAgo]				= DATEDIFF(YEAR,d.[date],CAST(GETDATE() AS DATE))
    ,IsCurrentDate			= CASE WHEN d.[date] = CAST(GETDATE() AS DATE) THEN 'yes' ELSE 'no' END
    ,IsCurrentWeek			= CASE WHEN DATEPART(WEEK,d.[date])		= DATEPART(WEEK,CAST(GETDATE() AS DATE))	AND YEAR(d.[date]) = YEAR(GETDATE()) THEN 'yes' ELSE 'no' END
    ,IsCurrentMonth			= CASE WHEN DATEPART(MONTH,d.[date])		= DATEPART(MONTH,CAST(GETDATE() AS DATE))	AND YEAR(d.[date]) = YEAR(GETDATE()) THEN 'yes' ELSE 'no' END
    ,IsCurrentQuarter		= CASE WHEN DATEPART(QUARTER,d.[date])	= DATEPART(QUARTER,CAST(GETDATE() AS DATE)) AND YEAR(d.[date]) = YEAR(GETDATE()) THEN 'yes' ELSE 'no' END
    ,IsCurrentSemester		= CASE WHEN CAST(DATEPART(yyyy, d.[date]) AS CHAR(4)) + CASE WHEN DATEPART(m, d.[date]) <= 6 THEN '1' ELSE '2' END
    										= CAST(DATEPART(yyyy,GETDATE()) AS CHAR(4)) + CASE WHEN DATEPART(m, GETDATE()) <= 6 THEN '1' ELSE '2' END
    									THEN 'yes' ELSE 'no' END
    ,IsCurrentYear			= CASE WHEN YEAR(d.[date]) = YEAR(GETDATE()) THEN 'yes' ELSE 'no' END
    ,[Last12Months]			= CASE WHEN DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE)) < 13 AND DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE)) > 0
    									THEN 'yes'
    									ELSE 'no'
    								END
    ,[PreviousMonth]		= CASE WHEN DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE)) < 2 AND DATEDIFF(MONTH,d.[date],CAST(GETDATE() AS DATE)) > 0
    									THEN 'yes'
    									ELSE 'no'
    								END
    ,[PreviousWeek]			= CASE WHEN DATEDIFF(WEEK,DATEADD(dd,-1,d.[date]),DATEADD(dd,-1,CAST(GETDATE() AS DATE))) < 2 AND DATEDIFF(WEEK,DATEADD(dd,-1,d.[date]),DATEADD(dd,-1,CAST(GETDATE() AS DATE))) > 0
    									THEN 'yes'
    									ELSE 'no'
    								END   
FROM cte_dates d;

You can find the scripts (and a few others) on my GitHub repo. Using such a repo is a good way to ensure you access to your handy scripts wherever you are.

I was in a need of converting some datetime values (of which I know the actual timezone) to UTC dates. A quick Google search showed me that most results on the first page were simply wrong. Most of them used this trick:

DECLARE @dt DATETIME = '2020-09-22 22:23:13.920';

SELECT  DATEADD(MI, (DATEDIFF(MI, SYSDATETIME(), SYSUTCDATETIME())), @dt);

Seems nifty, but they forgot about daylight savings time. If I run the same query somewhere in December, I get a different result. I like my functions deterministic please.

Luckily, SQL Server 2016 introduced us to the DATETIMEOFFSET data type and the AT TIME ZONE clause. This means you can convert the input datetime to your local timezone, convert this to UTC and finally convert that result to datetime again. In code:

DECLARE @dt DATETIME = '2020-09-22 22:23:13.920';

SELECT CONVERT(DATETIME,
			CONVERT(
				DATETIMEOFFSET,
					CONVERT(DATETIMEOFFSET
					-- assuming all servers are on CEST time
							,@dt AT TIME ZONE 'Central European Standard Time'
					)
				AT TIME ZONE 'UTC')
		  );

If you’re stuck on a lower version of SQL Server, I’d suggest you upgrade Or maybe create a table with all the start and end dates of daylight savings time for each year, so you can easily look up the offset.

UPDATE:

Turns out the conversion to DATETIMEOFFSET isn’t even necessary, which makes the code even shorter:

DECLARE @dt DATETIME = '2020-02-22 22:23:13.920';

SELECT CONVERT(DATETIME,
			    @dt AT TIME ZONE 'Central European Standard Time'
				    AT TIME ZONE 'UTC');

Thanks to Adam for pointing it out!

I had a curious performance issue today. An inline table-valued function (iTVF) was performing poorly for some parameter sets, and quite fast for other parameter values. In short, this one is fast:

SELECT * FROM dbo.myFunction(a);

This one is slow:

SELECT * FROM dbo.myFunction(b);

Turns out SQL Server used a plan with a hash join in the fast query, and a nested loop in the slow query. Due to SQL Server also wildly using incorrect estimates, the nested loops performs really poorly. Quite similar to parameter sniffing with stored procedures. Erik Darling has written a great article about it: Inline Table Valued Functions: Parameter Snorting.

The thing is, in contrast to scalar functions or multi-statement table-valued functions, the iTVF should have better performance because it will be expanded into the calling query. This way, SQL Server can use “more correct” estimates and create a plan for each different parameter. Well, today was not that day.

During testing, I took the actual query the iTVF was running and supplied the slow parameters to it, in the following format:

DECLARE @myParam INT = b; -- remember, b was the slow value
SELECT *
FROM myTable
WHERE myColumn = @myParam ;

But now, the query was actually fast! Wait, what? So the exact same query from inside the function, with the same parameter value was running faster than running the function with the parameter? How is that possible? Luckily, Brent Ozar made a quick video to explain why SQL Server can have different execution plans for both scenarios:

To summarize: executing a query with local variables forces SQL Server to not inspect the contents of the variable and use the correct estimates.
This is good:

SELECT *
FROM myTable
WHERE myColumn = b;

This is bad:

DECLARE @myParam INT = b;
SELECT *
FROM myTable
WHERE myColumn = @myParam;

But in my case, the exact opposite was true! The plan optimized for a random variable value was faster (hash join) than the plan optimized for the parameter value b (nested loops). Even SQL Server can have an off-day. And this leads us to the title of this blog post. With stored procedures, you can specify the hint OPTIMIZE FOR UNKNOWN. The concept is explained in this blog post by Kendra Little: Optimize for… Mediocre?.

Unfortunately, you cannot specify OPTIMIZE FOR UNKNOWN for an iTVF. So how do we solve this? We just call the iTVF using local variables!

DECLARE @myParam INT = b;
SELECT * FROM dbo.myFunction(@myParam);

Now, SQL Server doesn’t use the plan specific for the value b, but rather the general plan for an average value, which just happens to use the hash join. Hooray.

Keep in mind this is likely a short-term fix. As the data in the table changes, the distribution of the values might change as well and at some point in time SQL Server might – incorrectly – decide to go for the nested loops plan for the “average value”. This means I might have to refactor that iTVF into something that has more consistent performance.

p.s.: hat-tip to Brent for assisting with this issue

A couple of years back Itzik Ben-Gan (the T-SQL guru) wrote the book Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions. It’s has been one of my most favorite technical books (I included it in this list): it was really an eye-opener to how useful and powerful window functions can be. It teaches the basics extremely well and also proposes solutions to common use cases.

Recently, a second edition was published titled T-SQL Window Functions – For data analysis and beyond (a much better title imo). For those of you wondering, are there any changes to window functions in SQL Server since SQL Server 2012? Why yes there are.

• support for batch mode was introduced, and in SQL Server 2019 batch mode on rowstore was released as well. This can lead to different performance optimization choices.
• a couple of new functions were introduced, such as STRING_AGG.

Even more than in the first edition, Itzik explains the power of window functions in the SQL standard, even if the implementation is not (yet) supported in SQL Server. There is for example a whole chapter on row-pattern recognition, which would definitely be awesome if SQL Server supports it one day.

The power of the book is still the same as in the first book: it is quite short (just over 300 pages) but it explains all of the concepts really well.

You start with a short chapter introducing you to window functions: the background, the different elements and logical query order. The second edition also adds an evolution of window functions over the various versions of SQL Server.

The second chapter explains all the different types of window functions: window aggregate, ranking ,statistical (called distribution in the first edition) and offset functions. The first two chapters lay a good foundation and are already a good start for learning window functions and you’re only 100 pages into the book.

The third chapter dives into ordered set functions, which aren’t supported in SQL Server except for LIST_AGG (called STRING_AGG in SQL Server). If you’re just here for the T-SQL, you can skip this chapter but it’s an interesting read nonetheless as it gives you an idea how powerful the SQL standard is. Chapter four (an entirely new chapter) is the same: it talks about row-pattern recognition which is not present (yet) in SQL Server. Again, if you only use SQL Server you can skip it, but it’s a very interesting and mind-challenging read. I’d advise to read it.

Chapter 5 and 6 are the more practical chapters: the first one dives into optimization of window functions, while the last one presents solutions for certain use cases using T-SQL window functions. In the optimization chapter more content is added about emulating the NULLS LAST functionality, while the last chapter has more content about emulating IGNORE NULLS (which exists in Snowflake for example) and a solution for a trimmed mean.

In short: don’t have either of the books? Definitely buy the second edition. If will improve the quality and effectiveness of your T-SQL programming. I wouldn’t advise this book for absolute SQL beginners, but it is a book you have to read somewhere in your SQL Server career. Doesn’t matter if you’re a DBA, a database developer or a BI developer, everyone will benefit from this book.

If you already have the first edition: I would recommend buying this book if you’re interested in the evolution of window functions, how they interact with columnstore indexes and batch mode and maybe for those extra solutions. But the extra theoretical content (e.g. row-patter recognition) is also very interesting.

Happy reading!

p.s.: I did not receive any copy of this book for this review. I got it at a promotion from Microsoft Press. I do not regret buying it
p.p.s.: the links in this blog post are Amazon affiliate links.

I’m delighted to announce I’ll be giving a webinar for MSSQLTips.com about SQL Server Development Best Practices. Aka writing T-SQL and stuff

The webcast is the 13th of June 2019 at 7PM UTC. In a nutshell, I’ll be talking about the stuff I would tell my 10-year younger self about T-SQL development (if I could travel in time, of course). The abstract:

As a database developer I have had many years to learn the right way and the wrong way of doing things with SQL Server. As SQL Server continues to roll out new features I need to continue to refine my database development skills with all of the new tools Microsoft offers. In addition, I need to keep in mind all of the things I have learned over the years to ensure I still follow best practices as well as things I know work best from my own experience.

In this webcast I will share some of the development skills I have honed over the years and things that I wish I knew when I first started working with SQL Server which could have saved a lot of time and headaches.

Join us for this free webcast and learn things about database design, T-SQL development, optimizing for performance, BI development and more. 

You can register here. Full disclosure: the webinar is sponsered by Idera.