119 SQL Code Smells

Once you've done a number of SQL code-reviews, you'll be able to identify signs in the code that indicate all might not be well. These 'code smells' are coding styles that, while not bugs, suggest design problems with the code. In this PDF, Phil's put together 119 of those code smells, some generic, and some particular to SQL Server, so you can see what to avoid and why.

DevOps, Continuous Delivery & Database Lifecycle Management
Continuous Integration

Written with advice, help or contributions from over 25 SQL Server professionals
Download the free PDF from Red Gate

Once you’ve done a number of SQL code-reviews, you’ll be able to identify signs in the code that indicate all might not be well. These ‘code smells’ are coding styles, some generic, and some particular to SQL Server, that, while not bugs, suggest design problems with the code. In this PDF, Phil’s put together 119 of those code smells  so you can see what to avoid and why.

Kent Beck and Massimo Arnoldi seem to have coined the term ‘CodeSmell’ in the ‘Once And Only Once’ page of www.C2.com, where Kent also said that code ‘wants to be simple’. Kent Beck and Martin Fowler expand on the issue of code challenges in their essay ‘Bad Smells in Code’, published as Chapter 3 of the book ‘Refactoring: Improving the Design of Existing Code’ (ISBN 978-0201485677).

Although there are generic code smells, SQL has its own particular habits that will alert the programmer to the need to refactor code. (For grounding in code smells in C#, see ‘Exploring Smelly Code’ and ‘Code Deodorants for Code Smells’ by Nick Harrison.) Plamen Ratchev’s wonderful article ‘Ten Common SQL Programming Mistakes’ lists some of the SQL Server code smells along with out-and-out mistakes, but there are more. The use of nested transactions, for example, isn’t entirely incorrect, even though the database engine ignores all but the outermost, but their use does flag the possibility the programmer thinks that nested transactions are supported.

For a booklet like this, it is best to go with the established opinion of what constitutes a SQL Code Smell in SQL Server. There is little room for creativity. In order to identify only those SQL coding habits that could, in some circumstances, lead to problems, I must rely on the help of experts, and I am very grateful for the help, support and writings of the following people in particular:

  • Dave Howard
  • Merrill Aldrich
  • Plamen Ratchev
  • Dave Levy
  • Mike Reigler
  • Anil Das
  • Adrian Hills
  • Sam Stange
  • Ian Stirk
  • Aaron Bertrand
  • Neil Hambly
  • Matt Whitfield
  • Nick Harrison
  • Bill Fellows
  • Jeremiah Peschka
  • Diane McNurlan
  • Robert L Davis
  • Dave Ballantyne
  • John Stafford
  • Alex Kusnetsov
  • Gail Shaw
  • Jeff Moden
  • Joe Celko
  • Robert Young

And special thanks to our technical referees, Grant Fritchey and Jonathan Allen.

DevOps, Continuous Delivery & Database Lifecycle Management
Go to the Simple Talk library to find more articles, or visit www.red-gate.com/solutions for more information on the benefits of extending DevOps practices to SQL Server databases.