You can get into a situation where you have two tables with values associated with date ranges. What’s worse, those date ranges don’t necessarily have to align, which can make joining them a seemingly complex task, but it is surprisingly simple when you learn how to think of overlapping date ranges, along with this relatively simple T-SQL join pattern.
Continue readingTips
Indexing for substring search
A question from a client got me thinking. Relational databases (at least the ones I know and love) can’t really index for queries that use LIKE queries for a substring of a column value. If you want to search for strings beginning with a given string, a regular rowstore index will have you covered. If you’re looking for entire words or sentences, a full text index might be a good call. But because of the very way indexes work, you’ll never get great performance searching for just arbitrary parts of a string.
So today I’ll put on my lab coat and do a little rocket surgery, just to prove to the world that it can be done.
Professional driver on closed roads, always wear protection. Your mileage may vary.
Continue readingA quick look at SQL Server UTF-8 collations
A client asked me about SQL Server collations, and if they should consider the new UTF8 collations (new since SQL Server 2019). I tried to hide my blank stare of ignorance, and promised them I’d look it up and get back to them.
Not gonna lie, I think UTF and Unicode can be pretty confusing at times, so I did some googling and some testing, and here’s what I found.
Continue readingI ❤️ QUOTENAME()
An underrated, and perhaps less well-known T-SQL function is QUOTENAME(). If you haven’t heard of it, chances are that it could do wonders for your dynamic SQL scripts.
To quickly recap quoting, consider the following script:
SELECT N'DROP PROCEDURE '+OBJECT_SCHEMA_NAME([object_id])+N'.'+[name]+N';'
FROM sys.procedures
WHERE [name] LIKE N'%test';
What happens if one of your object names contains a space, a quote, an apostrophe, a square bracket, etc? You’ll end up with a syntax error, or even worse, a SQL injection attack (pretty elaborate, but still quite possible). To solve for this, we quote the object names. In SQL Server, you can surround schema and object names with double quotes (if you’ve set QUOTED_IDENTIFIER) or square brackets.
Simple, right?
SELECT N'DROP PROCEDURE ['+OBJECT_SCHEMA_NAME([object_id])+N'].['+[name]+N'];'
FROM sys.procedures
WHERE [name] LIKE N'%test';
But just adding a [ before and a ] after won’t work if your evil user as embedded square brackets or a semicolon in the object name. What if your object name is “Testing [quoting]; test”?
Continue readingHow to put tempdb on your Azure VM temp disk
Almost all Azure virtual machine sizes come with a temporary disk. The temporary disk is a locally attached SSD drive that comes with a couple of desirable features if you’re installing a SQL Server on your VM:
- Because it is locally attached, it has lower latency than regular disks.
- IO and storage are not billed like regular storage.
As the name implies, the temporary disk is not persistent, meaning that it will be wiped if you shut down your VM or if the VM moves to another VM host (as part of maintenance or troubleshooting). For that reason, we never want to put anything on the temporary disk that we need to keep.
tempdb could be a good fit
Since tempdb is wiped and recreated every time we start a SQL Server instance, it could be a great candidate to have on the temporary drive, provided a few prerequisites are met:
Continue readingQuerying a single table can use multiple indexes
Can SQL Server piece together two different indexes in a single-table query, rather than just giving up and scanning a suboptimal clustered index? The short answer is: yes, in a fairly narrow band of conditions.
Continue readingUse Ctrl+F1 as a “preview” button in SSMS
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:
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
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:
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.
STRING_SPLIT(), but for quoted names
Can you apply gaps and islands logic on a string? Sure you can.
Continue readingA simple database deploy pipeline using sqlpackage
I did some googling to see just how simple I could make a database deployment pipeline. I keep the DDL scripts in a git repository on the local network, but I can’t use Azure DevOps or any other cloud service, and I don’t have Visual Studio installed, so the traditional database project in SSDT that I know and love is unfortunately not an option for me.
So I googled a little, and here’s what I ended up doing.
Continue readingHow to add “created” and “updated” timestamps without triggers
You have a table that you want to add “created” and “updated” timestamp columns to, but you can’t update the application code to update those columns. In the bad old times, you had to write a trigger to do the hard work for you. Triggers introduce additional complexity and potentially even a performance impact.
So here’s a nicer way to do it, trigger-free.
Continue reading
sqlsunday.com 