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...

Window Functions vs GROUP BYs

There are many options available for improving the performance of a query: indexes, statistics, configuration settings, etc… However, not all environments allow you to use those features (eg. vendor...
The...

Database Fundamentals #21: Using the JOIN Operator, OUTER JOIN

The OUTER JOIN returns one complete set of data and then the matching values from the other set. The syntax is basically the same as INNER JOIN but you...