Category: Bad Habits & Best Practices

I use a real-world example showing why you shouldn't use deprecated functionality like SQL Server Profiler.

I talk about one scenario where the system table sys.sysprocesses almost led us down the wrong path.

An index of over a decade's worth of posts and videos involving bad habits and best practices in SQL Server.

There is a very common anti-pattern you should avoid, involving updating a row if it exists and inserting it if it doesn't. See how to avoid race conditions and deadlocks.

Discover some undocumented or unsupported behavior you might not even realize you're relying on.

Originally published in 2009, I updated this in 2019 with an example showing an effect on the plan cache.

Aaron Bertrand explains four conventions he always follows when writing T-SQL queries.

Get a first-hand look at some of the ways NOLOCK can produce incorrect data.

This tip was refreshed with status updates for some of the critical bugs discovered in MERGE over the years.

This was mostly tongue-in-cheek, but I show that there is no performance difference between tabs and spaces. (There is, however, a better reason to prefer tabs: accessibility.)

I discuss a couple of potential problems that can occur when you are inconsistent about case sensitivity.

A little advice on the use of MDF/LDF files for backups.

I examine various impacts on SQL Server – not just disk space – when choosing a GUID as a clustering key.

I show some tricks for getting row counts efficiently, and explain why an accurate row count for a large table is a pipe dream.

I repeat "Bad habits" advice from the past about using statement terminators and schema references to avoid debugging troubles.

I point out that an often-used connection setting, AttachDBFileName, may be the source of many wasted hours of debugging and troubleshooting.

I treat a common scenario: behavior differs between two SQL Server databases that may be the same version, but different compatibility levels.

I discuss the NOLOCK hint in SQL Server, and why you want to avoid slapping it onto every table mentioned in every query.

I discuss ten common cases of misplaced optimization.

I explain why you should avoid helper functions, and join against the catalog views instead.

I provide links to presentations (one with colleague Kevin Kline) at SQL Bits XII.