Secure your temporal table history

/ Daniel Hutmacher / 3 Comments

You may have already discovered a relatively new feature in SQL Server called system-versioned temporal tables. You can have SQL Server set up a history table that keeps track of all the changes made to a table, a bit similar to what business intelligence people would call a “slowly changing dimension”.

CREATE SCHEMA App;

CREATE TABLE App.Customers (
    Company_ID      int IDENTITY(1, 1) NOT NULL,
    CompanyName     nvarchar(250) NOT NULL,
	Email           varchar(250) NOT NULL,
	Valid_From      datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,
	Valid_To        datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (Company_ID),
	PERIOD FOR SYSTEM_TIME (Valid_From, Valid_To)
) WITH (SYSTEM_VERSIONING=ON);

What happens behind the scenes is that SQL Server creates a separate table that keeps track of previous versions of row changes, along with “from” and “to” timestamps. That way, you can view the contents of the table as it was at any given point in time.

But how to you version the contents of a table, while hiding things like deleted records from prying eyes?

Continue reading →

I made a new demo/workshop database

/ Daniel Hutmacher / 19 Comments

For a couple of years now, I’ve taught my SQL Server workshops using a demo database that I constructed way back in 2014. The old demo database I used was modelled on a fictional airline, but the data was made up, and the modelling.. wasn’t great.

Chicago, the city of complicated parking. Photo credit: Sawyer Bengtson on unsplash.com

So I spent a couple of hundred hours to build a new database based on a public dataset from the City of Chicago with parking tickets issued between 1997 and 2017. I’ve also refined this dataset with more details from other public sources – from ZIP codes, to socioeconomic indicators, to weather information and more.

Continue reading →

Watch out for Merge Interval with date range Index Seeks

/ Daniel Hutmacher / 4 Comments

In my last post, I found that DATEDIFF, DATEADD and the other date functions in SQL Server are not as datatype agnostic as the documentation would have you believe. Those functions would perform an implicit datatype conversion to either datetimeoffset or datetime (!), which would noticeably affect the CPU time of a query.

Well, today I was building a query on an indexed date range, and the execution plan contained a Merge Interval operator. Turns out, this operator brings a few unexpected surprises to your query performance. The good news is, it’s a relatively simple fix.

Continue reading →

DATEDIFF performs implicit conversions

/ Daniel Hutmacher / 3 Comments

As I was performance tuning a query, I found that a number of date calculation functions in SQL Server appear to be forcing a conversion of their date parameters to a specific datatype, adding computational work to a query that uses them. In programming terms, it seems that these functions do not have “overloads”, i.e. different code paths depending on the incoming datatype.

So let’s take a closer look at how this manifests itself.

Continue reading →

Computing the modulus from very large numbers

/ Daniel Hutmacher / 3 Comments

… and what of this all has to do with IBAN numbers.

The modulus is the remainder of a division of two integers*. Suppose you divide 12 by 4, the result is 3. But divide 11 by 4, and the result is 2.75. This could also be expressed by saying that 11/4 is 2 with a remainder of 3. Computing that 3 is the work of the modulo operator, which in T-SQL is represented by the % operator.

Let’s explore how to compute the modulus of large numbers in SQL Server, and how this is useful in the real world.

Continue reading →

T-SQL template parser

/ Daniel Hutmacher / 1 Comment

Just for the heck of it, I scratched together a template parser for T-SQL GitHub. The usage of this function is similar to the STRING_SPLIT() function, except instead of splitting a string by a delimiter character, we want to split a string according to a defined template.

SELECT *
FROM dbo.Template_Split(
    'Wingtip Toys (Bethel Acres, OK)',
    '% (%, %)'
);

… will generate the following output:

Notice how the “%” wildcard character denotes how the string is split. Unlike the fancy stuff you can do with regular expressions, T-SQL wildcards don’t allow you to define capture groups, so this function is unfortunately constrained to just using “%”. I hope it will still come in handy to someone out there.

That’s it, that’s the post. Enjoy!

Turn your list into human-readable intervals

/ Daniel Hutmacher / 1 Comment

If you’ve worked with reporting, you’ve probably come across the following problem. You have a list of values, say “A, B, C, D, K, L, M, N, R, S, T, U, Z” that you want to display in a more user-friendly, condensed manner, “A-D, K-N, R-U, Z”.

Today, we’re going to look at how you can accomplish this in T-SQL, and what this has to do with window functions and gaps and islands.

Continue reading →

Use Ctrl+F1 as a “preview” button in SSMS

/ Daniel Hutmacher / 13 Comments

Every time I set up SQL Server Management Studio, I take the time to add a shortcut to the “Query Shortcuts” section of the options:

Tools -> Options -> Environment -> Keyboard -> Query Shortcuts

On the surface, these query shortcuts are just what the name implies – a key combination that you can press to run a command or execute a stored procedure. But there’s a hidden super power: whatever text you’ve selected in SSMS when you press the keyboard combination gets appended to the shortcut statement.

So if you select the name of a table, for instance “dbo.Votes”, and press Ctrl+F1, SSMS will run:

SELECT TOP (1000) * FROM dbo.Votes
Preview the contents of a table

This allows you to create a keyboard shortcut to instantly preview the contents of a table or view.

And you can select not just the name of one table, but any other query text you want to tack on:

Preview the contents of two joined tables.

Because we’ve selected both the name of a table and the next line, pressing Ctrl+F1 in SSMS will effectively run the following command:

SELECT TOP (1000) * FROM dbo.Votes AS v
INNER JOIN dbo.VoteTypes AS vt ON v.VoteTypeId=vt.Id

You can go on to include as many joins, WHERE clauses, ORDER BY, as long as the syntax makes sense:

Remember that query shortcuts only apply to new windows, so if you change them, you’ll have to open a new window for the change to take effect.