This month’s T-SQL Tuesday is being hosted by Mala Mahadevan (b | t). The subject is T-SQL Coding Standards. As Mala describes it, “What are the T-SQL coding rules that are appropriate for where you work and what you do? “. This post will be my contribution for T-SQL Tuesday #151 – T-SQL Coding Standards.
This is a very wide subject area, that gives everyone plenty of possibilities for blog topics. Being more focused on one narrow topic can be bad, since it may discourage some people from writing a post. Just writing a useful blog post and getting quality links back and forth from other good blogs in the SQL Server community is good for everyone who participates!
BTW, reading and then commenting on the T-SQL Tuesday blog posts of other participants helps both you and the other blogger. They get views and comments on their blog, and you get backlinks to your blog. T-SQL Tuesday also helps encourage people to blog more often, which is good.
In my case, I am going to talk about some of my thoughts about T-SQL Coding Standards.
T-SQL Tuesday #151 – T-SQL Coding Standards
Most of the organizations that I have worked at have not been big proponents of having or strongly enforcing T-SQL coding standards. I have usually worked at smaller organizations with tiny DBA teams. Despite this, I think it is important to be consistent and explicit when you are writing or editing T-SQL code.
Like most DBAs, I have my own set of preferences for how I write and format T-SQL. Of course, if you get ten DBAs together, each one will have a different set of preferences. That is another reason why coding standards are useful.
How About Some Examples?
Here are a few of my personal preferences and “rules”.
- Use upper case for T-SQL keywords
- Have spaces after commas and before and after operators
- Put your commas between column names to the right
- This means trailing commas
- Schema-qualify object names
- End statements with a semicolon
- Use the optional AS for column and table alias’s
- Use square brackets around column alias names
- Spell out INNER JOIN instead of just JOIN
This is an example of how I typically do my formatting:
-- Get VLF Counts for all databases on the instance (Query 34) (VLF Counts) SELECT db.[name] AS [Database Name], li.[VLF Count] FROM sys.databases AS db WITH (NOLOCK) CROSS APPLY (SELECT file_id, COUNT(*) AS [VLF Count] FROM sys.dm_db_log_info (db.database_id) GROUP BY file_id) AS li ORDER BY li.[VLF Count] DESC OPTION (RECOMPILE);
This is obviously an example from one of my Diagnostic Information Queries. Now, as I look at them more closely, I see lots of room for better formatting and consistency…
BTW, I use the problematic WITH (NOLOCK) hint and OPTION (RECOMPILE) on purpose in most of these queries, to minimize the impact of my queries on the system they are run on.
Is This Worth Going To War Over?
I don’t think so! Unfortunately, I have seen people get in very serious disputes about T-SQL coding standards and formatting preferences. This usually is a waste of time.
I think it is much more important to have functional, readable code that performs well and is easier to maintain. Worrying about whether you use spaces or tabs is not that important.
What I think is more important is trying to avoid writing T-SQL code that uses patterns that are more likely to have performance issues. Things like using table variables, using scalar-UDFs, not matching datatypes, etc. That is where static code analyzers can be useful. I have some fond memories of the old SQL Server 2000 BPA.
I hope that this post has been interesting and useful. I also want to thank Mala for hosting this month!
If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!
5 thoughts on “T-SQL Tuesday #151 – T-SQL Coding Standards”
Yes, indeed good advices. 🙂
I am curious as to why you refer keeping the “AS” and “INNER”. I’ve always removed them (and “OUTER” too, of course) as just being extra noise. I’m also a “leading commas” guy. I’m even a “Alias = ColumnName” instead of a “ColumnName AS Alias” guy.
But I’m open to re-evaluating given a solid reason. And I agree, none of this is a hill worth dying on.
I generally think that being explicit and using the full keyword instead of the shorter form makes code more readable. But I also agree that it is not a hill worth dying on either.
@m60freeman, about rules for AS and INNER, which I also practice, I have some good justifications. I spent many hours reading discussions, I was even emotionally involved in, but now I agree with Glenn: “This usually is a waste of time”. It’s slightly better to use these rules, so it’s better, but only slightly.
AS: if you use this in code, you have a visible separator between name and alias. From my point of view that code is easier to read. As we know from history, in ancient times we didn’t use dots and other interpunction marks, and texts were readable, but for some reason, we are still using these “graphical sugar” added by the next centuries.
INNER: sometimes when I try to find why I don’t see data from joined tables, I simply replace INNER JOIN with OUTER JOIN. Fast and effective, so I always use full syntax.