It’s T-SQL Tuesday again, and although I seldom write on Tuesday, I attempt to participate. The brainchild of Adam Machanic (Blog|Twitter), T-SQL Tuesday invites new and existing SQL Server bloggers to post about the same topic on the same day. This time around, the topic is “T-SQL Best Practices”, hosted by Amit Banerjee (Blog|Twitter).
T-SQL is a proprietary top-down procedural programming language. It was originally developed jointly by Microsoft and Sybase for Sybase SQL Server on UNIX until 1993. From that point forward, Microsoft SQL Server was developed for NT Server. T-SQL is not an object-oriented programming language with object or methods and it does not compile into binaries. It is, however, a Server Side processing code used to query data. When I develop T-SQL, I treat it as any other programming language.
User Defined Functions
I have always been a fan of functions. To “bring out the Fun in Functions”, I learned that in any programming language, if you write it more than once, write a function; if you write it three times, stop programming and start a new career. In T-SQL, a UDF can return a single value (Scalar Function) or return a set of data (Table Value).
Use naming conventions
Use common naming conventions to name your variables. This standardization allows any programming to read your code and understand what is being processed. First, figure out the purpose of the variable, then give the variable a precise name, and finally, imply its Data Type. For instance, the date variable @BirthDate [datetime] is easy to understand. However, if you named it @BornColumn [datetime], somebody down the line would be thinking “Huh?”It would not be so interpretive.
Legible Code
Like most word processing programs, SSMS will automatically wrap your lines of code we all write lengthy blocks of code if you don’t press at certain points you could theoretically have only one long line of code the best thing to do is to turn off word wrap for better legibility to do this, select Tools, Options, Text Editor, Transact-SQL, under Settings Turn off Word Wrap for legibility.
In T-SQL, make concise comments describing the stored procedure or trigger. Best practice for commenting code is to make it understandable when you revisit this code years later. Dates and initials on comments can also alleviate problems.
— Two hyphens create one line for commenting
GO
CREATE PROCEDURE sp_TSQL2sDay
/* This is a block of comments
is an asterisk and front slash */
GO
ALTER PROCEDURE sp_TSQL2sDay
Version Control
SVN, Mercurial, Git, and SourceSafe are a few well-known version control systems to apply to source code. Although how do you source control T-SQL? It is not common to build the scripts in a programming IDE (Integrated Development Environment). Red-Gate has a product to source control schemas and data, but T-SQL saves in the database as a Server Side Script. I don’t use Red-Gate’s product, but I recommend that you save the scripts, whether stored procedures, triggers, or UDF, as a .sql file, and add them to your version control of choice.
If ifs and buts were candy and nuts, we’d all have a Merry Christmas. If pigs could fly, we’d all need stronger windshield wipers….As much as I wish this could be a feature rich post on how to create a UDF or implement version control on .sql files, it’s not. My sincere hope is that this will soon lead to that. In the meantime, these are just a few of my most recommended rules of thumb when developing T-SQL.
musings of extofer