In honor of Phil Factor's recent post on SQL Smells, I thought I would post some blogs SQL smells and why some of them are bad as well as when they may be OK.
Let's start with the top of the list that Phil has compiled.
Use of deprecated syntax such as *= (Dave Howard)
There are two problems here. First this is not in compliance with the SQL 92 standard. We should strive our code to be standards compliant whenever there is a standard available. Now this sounds like a cop out along the lines of "Because I said so" but there is a better reason.
This join syntax was standardized as it was to reduce ambiguity. One source of ambiguity stems from not being able to express a full outer join with the old syntax. It is confusing to have the join conditions blended in with the filtering conditions in the where clause. The SQL 92 syntax provides strong separation.
Denormalisation that requires the shredding of the contents of columns. (Merrill Aldrich)
This description could apply to any of several potential problems. I am not sure which problem Merrill was specifically referring to or what examples he finds most egregious.
I will speak here about the perils of so called "smart columns" These are columns that need to be parsed to get their true meaning. There are many examples. I worked on a system once that was riddled with so called CSZ fields. These were fields that stored the City State and ZipCode in a single field. You had to parse these fields to get the details because they were not stored in separate fields. Clearly a very bad design.
I also feel the same way about storing XML data in a varchar(max) field. If you must store XML data in the database, use the XML data type. XML data type will provide support for verifying that the content is XML and in the format expected.
I am not sure exactly what whoever submitted this was getting at, but I am going to take this opportunity to talk about naming conventions. There are other smells that talk about prefixing, so I will talk about how you name the database objects apart from the skipping the prefixes.
Bad naming conventions lead to confusion, making the database more difficult to work with and harder to understand. There is no need to use contrived abbreviations.
FirstName is dramatically easier to read and follow then F_NME.
Especially in SQL server the maximum size for object names is larger than you will ever need and the database preserves case, there is no need sprinkle names with underscores to separate words or shorten the names. Separate words with case changes and spell the names out. It will cut down on confusion.
Use of deprecated datatypes such as TEXT/NTEXT (Dave Howard)
You probably cannot go through your database and change every data type that needs to be changed. When you get advanced warning that a data type is going away, stop using it.
As you can, change your data types. It is better to go through and update individual fields as you can than to have to scramble when you try to upgrade the database version. Upgrading the database version can be daunting enough without such extra complications.
This covers my thoughts on the first four smells in Phil's list. Future posts will flush out my thoughts on others.
I would love to hear your thoughts on these smells.