SQLBlog.org home

© 2006 - 2024
Aaron Bertrand

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
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.