SQL Server Database Engine

The new “metadata-only column changes” feature in SQL Server 2016 is useless!

2020-05-042023-05-23
SQL Server Database Development, SQL Server Database Engine, T-SQL
2 Comments

You might have seen this blog post by Paul White: New Metadata-Only Column Changes in SQL Server 2016. It talks about how, when a table is compressed, you can change (i.e. increase) a column’s data type without having to create a new table and migrate the data into it, and without having to refactor all of the underlying data. But, not everything is rainbows and unicorns…

Troubleshooting Long-Running SHRINK Operations

2020-04-072022-12-31
SQL Server Database Engine, Tools and Scripts
9 Comments

Ever had your SHRINK operation in SQL Server get “stuck” and didn’t know why, or how to solve it? It’s more common than you think. Check out this post for more info, tricks and tips.

Are you using Linked Servers? They may be in serious danger!

2020-01-022022-12-25
SQL Server Database Engine

Linked Servers are an easy way to allow direct access between different SQL Servers. But, by using this feature, you may have created a wide-open backdoor into your servers! Read on for more details…

T-SQL Tuesday #118 – I wish SQL Server had better Table Partitioning

For this month’s T-SQL Tuesday, Kevin Chant asked us to dream up our fantasy SQL Server feature. I love this topic and so I had to join in on this as a late (and first-time) contributor.

My ask from Microsoft for SQL Server is something that…. Well… It should be simple. It should’ve been implemented YEARS ago. I’m thinking somewhere around the year 2005.

The LOOP, HASH and MERGE Join Types

2018-10-282022-12-11
SQL Server Database Engine, SQL Server Performance

Today I’ll talk about the main physical JOIN operator types in SQL Server (Nested Loops, Hash and Merge Joins), their differences, best practices, and complexity (which determines when the SQL Server optimizer would use them in execution plans).

These are not the duplicates you're looking for

The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes

Unlike foreign keys, primary keys and unique indexes cannot be “untrusted”. There’s no “NOCHECK” option for unique constraints. The closest… Read More »The "IGNORE_DUP_KEY" option in Primary Keys and Unique Indexes

The Curious Case of… Collations and Performance in SQL Server

2018-10-28
SQL Server Database Engine, SQL Server Performance

One fateful night, cold and dreary, I’ve stumbled upon how collations can significantly affect performance in SQL Server… Yes, really!

While normalizing a table in their database, one of my clients had weird and inconsistent performance problems and couldn’t find a solution for it for quite a while.

Using Reporting Services as your Software Frontend

2018-10-28
SQL Server Database Engine, Tools and Scripts

Microsoft SQL Server Reporting Services is an excellent and easy-to-use platform for creating, viewing, managing, and subscribing to reports of… Read More »Using Reporting Services as your Software Frontend