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 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
Bad habits to kick : relying on undocumented behavior
Video : Datetime shorthand (and other tricks)
Video : Using BETWEEN for 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 : Giving out the sa password
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 : 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 "!="?