{"id":1870,"date":"2014-09-15T00:00:00","date_gmt":"2014-09-15T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/119-sql-code-smells\/"},"modified":"2024-08-30T12:51:51","modified_gmt":"2024-08-30T12:51:51","slug":"119-sql-code-smells","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/resources\/books\/simple-talk-published-books\/119-sql-code-smells\/","title":{"rendered":"119 SQL Code Smells"},"content":{"rendered":"
\n <\/p>\n

DevOps, Continuous Delivery & Database Lifecycle Management<\/strong>
Continuous Integration<\/p>\n<\/div>\n\n

Written with advice, help or contributions from over 25 SQL Server professionals<\/em>
\nDownload the free PDF<\/strong> from Red Gate<\/a><\/p>\n

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 \u00a0so you can see what to avoid and why.<\/p>\n

Kent Beck and Massimo Arnoldi seem to have coined the term ‘CodeSmell’ in the ‘Once And Only Once’<\/a> 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).<\/p>\n

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’<\/a> and ‘Code Deodorants for Code Smells’<\/a> by Nick Harrison.) Plamen Ratchev’s wonderful article ‘Ten Common SQL Programming Mistakes’<\/a> 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.<\/p>\n

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:<\/p>\n

\n