July’s T-SQL Tuesday is brought to us by Amit Banerjee (Blog|Twitter), and he has chosen T-SQL Best Practices as the topic for this month’s T-SQL Tuesday. Amit discusses best practices in light of their impact on performance and states that “Sometimes, the most obvious things are the easiest to overlook!”
I’m not going to try for a comprehensive list. Instead I’ll put down the first three best practices that came to mind when I saw this topic. Just for fun, I added a fourth issue that might stir things up a bit.
- Put your T-SQL scripts in a version control solution. I’ve mentioned this before but I’ll repeat it. If you’re a DBA and don’t know where to start with this, talk with your developers. It will be a bonding moment with them. I know that’s a frightening thought, but it could save your bacon someday.
- Go out of your way to make T-SQL scripts re-runnable in an elegant and graceful manner. Seriously, these are the words I use when discussing this. Ask yourself how you can make scripts more like a ballet dance and less like a freeway pileup. Consider what can happen when running your script. Does your script drop a database? Check for users in that database and handle appropriately, e.g. exit the script with a message output that the database was not dropped because it had users. Going to create a schema? Check for its existence and if it already exists then don’t create it. Output a message informing that the schema already exists and didn’t have to be recreated. You’ll need to use Dynamic SQL to do this. Fine, do that. Your output messages should include the object names and a timestamp so that you’ll know when it was run. If you use the OBJECT_ID function to check for existence, include the optional object_type parameter so that you aren’t just relying on the name (even if you use naming conventions based on object type, you can’t assume that everyone always followed that convention everywhere). Take foreign keys and other dependency relationships into account and never assume that your scripts will be executed in the proper order. Sure, you could say “It’s not my job to make sure others use my scripts correctly.” But you know what? You could make things easier for someone else, even if they don’t notice it. And if someone else does notice it, they will think highly of you. Yes, it’s extra work. But you will learn things. And it will make you a better database professional. I guarantee it (disclaimer: this not an actual guarantee).
- Ask if the functionality even belongs in T-SQL. Sure, I like stored procedures and the SQL language. But there are other layers to most solutions than just the database. Don’t be afraid to ponder the possibility that something belongs in another layer. For example, when I encounter a user-defined function that’s used to format a number to appear a certain way, my first question is “where the heck is this being used?” Same thing with stored procedures that are doing a heavy amount of aggregation. Maybe that’s not a bad thing, but it could be. Situations are different all the time, so I think the easiest general rule is to be open to the possibility that a thing might belong elsewhere, even if it has already been implemented in T-SQL.
- I’m going to step on some toes and say that not all cursors are evil abominations to be completely and totally disallowed. Some are different than others. While they shouldn’t be the first solution considered in SQL Server, they can work just fine. Once upon a time, I was told to go through all of a company’s stored procedures and remove all the cursors. When I mentioned how long this could take, the DBA said “It’s easy. Just do what the cursor does, but put everything in a temp table, then iterate through the temp table with a WHILE loop.” I demonstrated that declaring the cursors as LOCAL and FAST_FORWARD consistently beat the performance of his temp table and loop solution. But it didn’t matter. He explained that some day he would be a manager and would fire anyone who used cursors. Interesting. There might be absolutes in the world, but I don’t think making “never use cursors” a best practice is one of them.
I’m looking forward to reading the contributions to this month’s T-SQL Tuesday and reading the best practices discussions of others. Thanks to Amit Banerjee for hosting T-SQL Tuesday #20, and thanks to Adam Machanic (Blog|Twitter) for creating this monthly blog event!