This month’s T-SQL Tuesday#20 – T-SQL best Practices is a tall order to write about. Best practices can cover coding style, documentation, performance, and so many other areas that whole books can and have been written about them. When I consider the poor practices I’ve seen in T-SQL code I think about the basic flaws that seem to be commonly introduced by developers; the misuse of subqueries when joins are appropriate, the overuse of distinct when it’s not necessary, or the usage of Select * almost by default, among others.
SQL is Set-Based
The worst case of poor T-SQL practice I have come across in the last few years was by a client that had written a stored procedure in a non-set based manner. The 3000 lines of code in a key SP iterated through a number of select statements that handled one entity at time in a procedural manner. Data was queried in small chunks by incrementing row numbers in loops, placed in temp tables while other data was acquired, functions were then called and other code logic was performed, until finally the next set of data was processed. A normal execution of this SP included anywhere from 1k – 10k database calls. The developer’s rationale for coding it this way was that it would be faster to get smaller pieces of data each time the database was queried and not put as much of a burden on the database engine.
SQL Server likes Joins
The SP in question was completely rewritten to perform 3 large queries each with 15-20 joins and performance improved by a factor of 50. The SQL Server database engine is tuned to perform joins very well, especially those with good indexing and appropriate foreign keys. Trying to work around the engine got this company and developer in trouble, and made the code very slow and much harder to work with.
Don’t turn T-SQL into a procedural language. Do make use of it’s really strong capacity to process data in sets.