Site icon Glenn's SQL Server Performance

T-SQL Tuesday #151 – T-SQL Coding Standards

Glenn Berry

Introduction

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 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”.

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.

Final Words

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!

Exit mobile version