Splitting Strings With OPENJSON

Starting with SQL Server 2016, Microsoft provided a STRING_SPLIT function. It is a table-valued function that splits a string into rows of substrings, based on a specified separator character....

The Silent Bug I Find in Most Triggers

I don’t have a problem with triggers. They get the job done when you need to implement business logic in a hurry, and you’re not allowed to change the...

Parsing JSON Data In SQL Server

Microsoft added support for JSON data beginning with SQL Server 2016. JSON is an open-standard file format consisting of attribute–value pairs and array data types. It is commonly used...

Using IGNORE NULLS With SQL Window Functions to Fill Gaps

I found a very interesting SQL question on Twitter recently

Removing multiple patterns from a string

Recently one of my clients had a request that was a challenge to do effectively. I don’t think it’s a very common requirement but I still decided to blog...

When Window Functions are Too Slow

Bert Wagner shows a scenario where a window function ends up performing poorly: If you’ve used FIRST_VALUE before, this query should be easy to interpret: for each badge Name, return the...

Get the Datetime

Drop Database Objects Practically Risk-Free!

FIRST OF A TWO PART SERIES Some months ago, a fellow DBA came to me and expressed her concern over the normal housecleaning process that occurred at her company. ...
The...

Find Occurrences of a String Anywhere on an Instance

Have you ever had a conversation like this? Developer: “Hey, we’d like to know every place a particular column is used”.  You: “On which database?” Developer: “Ummm….all of them, please.”

The Sins of SELECT * (BP005)

If Prompt warns you of use of the asterisk, or 'star' (*), in SELECT statements, consider replacing it with an explicit column list. It will prevent unnecessary network load...