Press "Enter" to skip to content

Category: T-SQL

Date Calculation (and Calendar Tables) in SQL Server

Published 2022-09-15 by Kevin Feasel

Aaron Bertrand makes the case for calendar tables:

In a previous tip, Simplify Date Period Calculations in SQL Server, I described how to easily calculate certain dates, like the first day of a given month, quarter, or year. Another common scenario is trying to find the last weekday of the month, or the nth Monday or Wednesday. In this tip, I will show ways to simplify these calculations, both with and without a calendar table.

Click through for Aaron’s approach to the problem. I have a blunter approach in creating an expansive calendar table and using it. You do the heavy lifting one time and are good for life on that server.

Comments closed

IS [NOT] DISTINCT FROM

Published 2022-09-14 by Kevin Feasel

Louis Davidson likes a new operator in SQL Server 2022:

The MOST exciting change from a T-SQL standpoint is: IS NOT DISTINCT FROM. This feature solves an age-old issue for T-SQL programmers and is worth its weight in gold. It is basically an equals comparison operator like =, but treats NULL as an individual value. Unlike =, this new operator returns only TRUE or FALSE, but not UNKNOWN. Writing queries that compare to values that can contain NULL is tedious, mostly because of code like the following:

Louis is quite happy here. I like the fact that the syntax is here, though I’d be concerned about performance—the syntax is nicer but you can run into the same performance issues as you’d have with “NULL or match” type queries.

Comments closed

Compacting Window Function Definitions

Published 2022-09-14 by Kevin Feasel

Rob Farley like a syntax change:

This was fine, but it did start to become a little cumbersome.

Enter SQL Server 2022. Not only do we get the ability to ignore nulls now, making it easy to get the last non-null value from a list, but we also get a WINDOW clause – part of the SELECT query itself, dropping in between the HAVING clause and the ORDER BY clause, allowing us to predefine those OVER clause segments.

Read on for the full scope of Rob’s thoughts.

Comments closed

Working with CROSS APPLY

Published 2022-09-13 by Kevin Feasel

Paul Randal takes us through one of my favorite operators:

Introduced by Microsoft in SQL Server 2005, SQL CROSS APPLY allows values to be passed from a table or view into a user-defined function or subquery. This tutorial will cover the incredibly useful and flexible APPLY operator, such as how the CROSS APPLY and OUTER APPLY operators work, how they’re like the INNER and LEFT OUTER JOIN, and give you some examples of both. All the examples use the AdventureWorks example database.

Later in the article, I’ll also discuss a highly pervasive SQL Server performance problem—one I still encounter with customers on a weekly basis. This problem is related to using the APPLY operator against a specific type of user-defined function. It’s so problematic and can overwhelm tempdb, bringing your SQL Server instance to a crawl!

If you’re not too familiar with APPLY in its two forms, read the whole thing.

Comments closed

Building UNPIVOT Syntax

Published 2022-09-08 by Kevin Feasel

Michael J. Swart has a function:

Just like PIVOT syntax, UNPIVOT syntax is hard to remember.
When I can, I prefer to pivot and unpivot in the application, but here’s a function I use sometimes when I want don’t want to scroll horizontally in SSMS.

Click through for the function. This is an area where I wish there was built-in * logic for PIVOT and UNPIVOT. Or at least a “Select all columns but the following” as that would make things easier.

Comments closed

FizzBuzz in SQL

Published 2022-09-08 by Kevin Feasel

Cathrine Wilhelmsen does some modulo division:

This week, my coworkers and I were given a fun challenge. Using any tool or language, solve FizzBuzz! Then present and explain the solution to the rest of the team. This was a fun challenge because our team is a mix of junior-to-senior developers and data professionals, working with everything from SQL to Python to C# to DAX to PowerShell. Those who had never solved FizzBuzz before got the chance to do so, while those who had already solved it got the chance to try again using a different tool or language.

Read on for Cathrine’s solution using a tally table. This classic variant of FizzBuzz is pretty easy to do in T-SQL; building the numbers table is the trickiest part.

Comments closed

Improvements to Parameter Sensitive Plan Optimization

Published 2022-08-16 by Kevin Feasel

Erik Darling is not good at being on vacation:

Several weeks back, I blogged about a missed opportunity with the new parameter sensitive plan feature. At the time, I thought that there was indeed sufficient skewness available to trigger the additional plan variants, and apparently some nice folks at Microsoft agreed.

If we step back through the old demo, we’ll get different results.

Click through for those results.

Comments closed

Attaching All SQL Server Data Files in a Single Directory

Published 2022-08-16 by Kevin Feasel

David Fowler migrated a bunch of databases:

Have you ever had the need to attach a large number of database in one go? There’s no way to attach multiple databases in SSMS or via script, so you’re probably going to be left with the slow, arduous task of doing them one by one.

I recently had to deal with a DR situation (I won’t go into details of what happened just yet as things are still quite sensitive, but I might look at it at some point in the future) where I faced exactly that issue. For one reason or another I needed to attach several hundred databases quickly. I didn’t fancy doing that via SSMS or script each one individually so I knocked together this script to do the job for me.

Click through for that script and instructions. Alternatively, a bit of Powershell and the right dbatools command could get you to the same result but this is good in the event that you can’t leave SSMS.

Comments closed

Improvements to GENERATE_SERIES

Published 2022-08-08 by Kevin Feasel

Erik Darling notes some improvements:

With the release of CTP 2.1, the problems that we saw the first time around are all gone. But there are still a couple small caveats that you should be aware of.

There’s also been a change in the way you call the function, too. You not longer need the START and STOP identifiers in the function.

There are still some limitations but it does look like the function is considerably better in CTP 2.1.

Comments closed