Category: Bad Habits & Best Practices

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.

Last Thursday I presented my "Bad Habits to Kick" presentation at the New England SQL User Group; attached is the slide deck.

By way of an example, see why most of what you hear about SQL Server is only true some of the time, at best.

Talking about the common misconception that a while looper is faster than a cursor (and why it's still a cursor).

See why I prefer alias = expression over the more standards-compliant expression AS alias syntax.

See several examples supporting the idea that you should use catalog views, not INFORMATION_SCHEMA, in SQL Server.

Learn why you shouldn't use BETWEEN for date range queries, even with the date data type.

Read about some date/time shorthand you should avoid.

See why you should use sp_executesql instead of EXEC() for running dynamic SQL strings.

See an example that defies a generalization about performance of getting the largest value in a column.