Bad Habits and Best Practices
Bad Habits and Best Practices
 

Going back more than a decade, I've been writing and presenting about what I call "bad habits" – typically lazy shortcuts or sub-optimal ways to do things in SQL Server. Often users just don't realize these things are bad or that there is a better way.

Here is an ongoing list of articles that I consider to be along these lines – eradicating bad habits or at least promoting best practices. Not all are explicitly framed as a "bad habit," but they do all present things I wish I observed less often.

Date Post
Don’t use DISTINCT as a “join-fixer”
One use case for NOT using schema prefixes
Find 40 Problems in this Stored Procedure
T-SQL Tuesday #158 : Worst Practices
2022
T-SQL Tuesday #156 : Production Code
T-SQL Tuesday #154 : SQL Server 2022 (anti-pattern XE)
T-SQL Tuesday #152 : Soapboxing (on leading commas)
Why shouldn't I create 15,000 partitions in advance?
T-SQL Tuesday #151 : Controversial Coding Conventions
Why I always start CTEs with a statement terminator
FORMAT is a convenient but expensive function, part 2
FORMAT is a convenient but expensive function, part 1
2021
Bad Habits : Avoiding NULLs
Bad Habits : Abusing bit columns
Yes, data type names can still be case sensitive
Why I don't like the WHILE (1=1) pattern
Finding problematic NOLOCK patterns – Part 4
Finding problematic NOLOCK patterns – Part 3
Finding problematic NOLOCK patterns – Part 2
Finding problematic NOLOCK patterns – Part 1
Deprecated features, part 3 : text / ntext / image
Deprecated features, part 2 : SQL Server Profiler
How SQL Server handles the date format YYYY-MM-DD
Deprecated features, part 1 : sysprocesses
2020
Bad Habits to Kick : relying on undocumented behavior
Video : Datetime shorthand (and other tricks)
Video : Using BETWEEN for date range queries
Video : Regional datetime formats
Video : Date/time data type guidance
Creating a date dimension or calendar table in SQL Server
Use caution with sys.dm_db_database_page_allocations
2019
Bad Habits to Kick : avoiding the schema prefix
How to Alter User Defined Table Type in SQL Server
Four SQL Server Syntax Rules I Always Follow
Simplify Date Period Calculations
NOLOCK Anomalies, Issues and Inconsistencies
Five Things I Wish I Knew When I Started My SQL Server Career
Make SQL Server DMV Queries Backward Compatible
Four ways to improve scalar function performance
2018
Using T-SQL to find events that overlap (or don't) in SQL Server
Improve Performance in SQL Server 2019 with Scalar UDF Inlining
Execute a Command in the Context of Each Database Part 2
Execute a Command in the Context of Each Database using sp_ineachdb
Making a more reliable and flexible sp_MSforeachdb
Use Caution with SQL Server's MERGE Statement
Be Careful with Key Order in Missing Index Recommendations
T-SQL Tuesday #104: Code I Would Hate to Live Without
Do tabs vs. spaces affect performance in SQL Server?
2017
DateTime Best Practices
Performance Myths : Oversizing string columns
Safeguard to Avoid Costly Mistakes for UPDATE or DELETE Statements in SSMS
Performance Myths : Clustered vs. Non-Clustered Indexes
Harmful, Pervasive SQL Server Performance Myths
Follow-up #1 on leading wildcard seeks
One way to get an index seek for a leading %wildcard
Benefits of SCHEMABINDING in SQL Server
Performance Surprises and Assumptions : GROUP BY vs. DISTINCT
2016
Top 5 Reasons for Wrong Results in SQL Server
Can comments hamper stored procedure performance?
#BackToBasics : CAST vs. CONVERT
#BackToBasics : Why I use lower case for data type names (now)
Avoid ORDER BY in views
Pattern Matching : More Fun When I Was a Kid
#BackToBasics : Naming Stored Procedures
#BackToBasics : Great Debates : Unicode
Paying Attention to Estimates
#BackToBasics : An Updated "Kitchen Sink" Example
Performance Surprises and Assumptions : DATEADD()
#BackToBasics : Dating Responsibly
#BackToBasics : The "Runaway" Query
Subjectivity : Naming Standards
#BackToBasics : Common Table Expressions (CTEs)
2015
Phase out CONTEXT_INFO() in SQL Server 2016 with SESSION_CONTEXT()
T-SQL Tuesday #72 : Models Gone Wild!
Maintaining a grouped running MAX (or MIN)
Bad Habits : Being CarELesS about cAsE
FORMAT() is nice and all, but…
How not to call Hekaton natively-compiled stored procedures
Protecting Yourself from SQL Injection in SQL Server – Part 2
Protecting Yourself from SQL Injection in SQL Server – Part 1
Bad Habits : Using MDF/LDF Files as 'Backups'
Best Practices : Properly referencing columns
Should I use NOLOCK against #temp tables?
Developers need to know "DBA stuff" – and vice-versa
Fun with THROW : Avoid % and use semi-colons!
Bad habits : Focusing only on disk space when choosing keys
2014
Bad habits : Counting rows the hard way
Bad habits : Another case for semi-colons and schema prefix
Bad habits : Using AttachDBFileName
Bad habits : Clinging to old compatibility levels
Bad habits : Putting NOLOCK everywhere
Stop making SQL Server do your dirty work
Bad habits : Looking for optimizations in all the wrong places
Bad habits : Using (certain) metadata "helper" functions
T-SQL Tuesday #56 : SQL Server Assumptions
Dirty Secrets of the CASE Expression
Avoid using NOLOCK on UPDATE and DELETE statements
For the last time, NO, you can't trust IDENT_CURRENT()
2013
Improve Efficiency by Switching to INSTEAD OF Triggers
Generate random integers without collisions
Performance Surprises and Assumptions : DATEDIFF
Performance Surprises and Assumptions : Arbitrary TOP 1
Follow-up on Summer Performance Palooza 2013 (Habits & Practices Q&A)
Don't just blindly create those 'missing' indexes!
Another argument for stored procedures
Break large delete operations into chunks
Generate a set or sequence without loops – part 3
Generate a set or sequence without loops – part 2
Generate a set or sequence without loops – part 1
2012
Is the sp_ prefix still a no-no?
What impact can different cursor options have?
How much impact can a data type choice have?
Splitting Strings : Now with less T-SQL
SQL Server Queries With Hints
Deciding between COALESCE and ISNULL
Minimize plan cache bloat
Checking for potential constraint violations before entering TRY / CATCH logic
Bad Habits to Kick : Believing everything you hear or read
Bad Habits to Kick : Thinking a WHILE loop isn't a CURSOR
Bad Habits to Kick : Using AS instead of = for column aliases
2011
T-SQL Tuesday #25 : T-SQL Tips 'n' Tricks
The case against INFORMATION_SCHEMA views
What do BETWEEN and the devil have in common?
Bad Habits to Kick : Using shorthand with date/time operations
Bad Habits to Kick : Using EXEC() instead of sp_executesql
T-SQL Tuesday #21 : Crap Code
T-SQL Tuesday #20 : T-SQL Best Practices
Avoid External Dependencies in SQL Server Triggers
2010
Use Consistent SQL Server Naming Conventions
Bad Habits to Kick : Inconsistent naming
Bad Habits to Kick : ignoring I/O
Bad Habits to Kick : creating the uber-view
Bad Habits to Kick : using ancient copies of Books Online
Bad Habits to Kick : inconsistent table aliasing
Bad Habits to Kick : ignoring the principle of least privilege
Bad Habits to Kick : blind SQL Server installs
Bad Habits to Kick : putting an IDENTITY column on every table
When you don't follow your own 'bad habits' advice…
2009
Bad Habits to Kick : mis-handling date / range queries

SQL Server training provider Webucator created a video based on this post.

Bad Habits to Kick : using the visual designers
Bad Habits to Kick : using alias types
Bad Habits to Kick : abusing triggers
Bad Habits to Kick : choosing the wrong data type
Bad Habits to Kick : making assumptions about IDENTITY
Bad Habits to Kick : inconsistent naming conventions
Bad Habits to Kick : using SELECT * / omitting the column list
Bad Habits to Kick : declaring VARCHAR without (length)
Bad Habits to Kick : using SELECT or RETURN instead of OUTPUT
Bad Habits to Kick : using dashes and spaces in entity names
Bad Habits to Kick : using old-style JOINs
Bad Habits to Kick : using table aliases like (a, b, c) or (t1, t2, t3)
Bad Habits to Kick : using loops to populate large tables
Bad Habits to Kick : ORDER BY ordinal
Ladies and gentlemen, start your semi-colons!
2008
My stored procedure best practices checklist
Which to use: "<>" or "!="?
By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.