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


sql-code-smells.png

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.

Tags: , , , , , , ,

  • 38098 views

  • Rate
    [Total: 0    Average: 0/5]
  • Anonymous

    download link not found
    I can’t find download free pdf link

  • Andrew Clarke

    Re: Download link not found
    The booklet is on the Red Gate site. This means that we can’t put the link in the heading as we usually do in article, but it should work if you click on the link ‘Download the free PDF from Red Gate’ in the article above. We added a link to the image of the front cover in the hope of making it all a bit clearer.

  • Robert Sterbal

    test for smells
    Is there a depository of scripts to look for smells?

    Is there a smell by smell discussion somewhere?

  • Phil Factor

    Re: Test for Smells
    Robert. Good to hear from you. Dave Ballantyne has been working on an interesting project called TSQL Smells SSDT. https://tsqlsmellsssdt.codeplex.com/
    which covers the automated testing of 43 of them. SQL Enlight does even more with an SSMS add-in and also do a command-line version.
    I am eventually hoping to put the source of the booklet on GitHub in AsciiDoc. I’m very conscious that this is a community effort and it should evolve as such. I’ve already had several comments that mean doing alterations to the text!

  • Robert Sterbal

    Is the GitHub page set up yet?
    It would be nice to have a notification system for the comments here….

    Is the Github page set up yet?

  • Andrew Clarke

    Re: Is the GitHub page set up yet?
    We’re working on the GitHub page at the moment and will leave a comment on this page when it is live.

    I think that the reason that the notification system isn’t working for you is that you weren’t signed in when you commented, and so the system doesn’t know where to send the notification!

  • melanietownsend

    The GitHub page
    We’ve put together the GitHub page:
    https://github.com/red-gate/SQL-code-smells

    There’s still some cleaning up of the formatting, but the book’s contents is on the page.

  • Steve O’Shaughnessy

    You should consider re-wording #68. Using reserved words IN names is not the same as using reserved words FOR names. date is a poor name for a variable or procedure. But get_date is perfectly acceptable.