A while back, I did a blog about SQL Code Smells, based on suggestions from a number of SQL experts. I later extended it and published it as a booklet. SQL Smells are just indications of where one should focus efforts on improving code. They are not supposed to be condemnations of a particular practice in SQL. More recently, I had to tidy up some databases that were sunk in 'technical debt', so I devised a simple query that checked for twenty obvious problems in tables. I ended up putting this in a Blog post on Table Smells.

Being an untidy and rather busy person, I forgot to put in a few obvious checks such as the use of VARCHAR(1) or VARCHAR(2), NTEXT, TEXT, MONEY, FLOAT, REAL or SQLVARIANT in a datatype, having too many indexes, or having columns in different tables with the same name but different datatypes. Someday, I'll get around adding them!

When adopting the practice of Continuous Integration, it is getting more common to do some sort of automated code review at the time of integration. In general, for application code, it is useful to have a measure of technical debt, or of cyclomatic complexity. There are several tools to determine some sort of measure of general code quality. However, there is not much around for SQL Server, though I've found SQL Enlight to be useful. Nevertheless, once a database is built from version control as part of CI, it is a great opportunity to do automated code reviews the SQL way.

I'm not a great fan of SQL code policies, best practices or the like. The reason I have SQL queries to find SQL Smells is purely for my own use, to check code for which I'm responsible. If I have to check for things that need dealing with, it is so much better to do it efficiently with code, rather than search a database through an IDE. To extend its use beyond this to enforcing standards across teams doesn't really appeal to my liberal instincts. The thing about SQL is that even the oddest SQL practices can be done deliberately for good reason.

So here comes the question: if you were do decide on SQL practices that were always wrong, what would you include? What practices could justifiably be prescribed for production use? I was pondering this the other day and thinking that maybe the use of ISNUMERIC() is never justified, and I've never found a good use for SQLVARIANT in a production table column. Am I wrong? Have you any other suggestions for anything else that should be banned from any respectable SQL Server database?

Phil Factor