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.

Latest materials from SQL Saturday Raleigh, 2025-05-17 (~1 MB)
Latest materials from DBAVUG, 2021-06-09 (~ 1 MB)
Latest materials from NESQL, 2021-02-10 (~ 1 MB)

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