Automating T-SQL Code Analysis

With all the options available within T-SQL these days, it’s more and more imperative that our code be clear and consistent. For example, there are clustered indexes and nonclustered indexes. Oh, but those are rowstore indexes. You also have clustered columnstore and nonclustered columnstore indexes. When you write T-SQL that says “CREATE INDEX MakeMyQueriesFast ON dbo.MySlowTable” which one do you mean?

Well, the default there will be a nonclustered rowstore index. How do I know that? I checked the documentation. Oh, same thing will work with a columnstore index. You don’t have to specify that it’s nonclustered, but doesn’t that seem unclear to you? It does to me.

Clarity and Understanding

You can write T-SQL a bunch of ways. Further, you can do some pretty crazy stuff with it that makes your code very hard to understand (or even downright evil, see Rob Volk and his session “Revenge, the SQL”). Code that is hard to understand is code that is going to slow development and troubleshooting. The last thing we need in this age of DevOps, automated deployments and continuous delivery is to slow things down. That means we must attain clarity when writing code in order to communicate properly.

However, it goes beyond what is effectively syntactic sugar (love that phrase). It can be vitally important that our code is written a certain way, not just for clarity and understanding, but for actual function. What if you’re very old school in your approach to T-SQL? Then using != is perfectly acceptable… as long as you’re running on old versions of SQL Server. However when you write that stuff in modern versions of SQL Server or on Azure, you’re in trouble.

Wouldn’t it be great to be automatically alerted when you start to violate good coding standards or use out of date syntax?

SQL Prompt and Code Analysis

Redgate has you covered.

Not only does SQL Prompt:

• Do T-SQL formatting for you (so the commas are put on the right like they should be)
• Make it possible for you to share a common style across your enterprise
• Create and consume snippets to ensure common practices and approaches
• Speed up your T-SQL writing while simultaneously making you more accurate
• Supply standard alias naming
• Put semicolons into your code in the right spots
• Remove those nasty brackets from where they’re not needed (or add them if you’re one of those people).

All this enterprise level functionality is just not enough.

SQL Prompt 9 now does Code Analysis. It’s cool.

I’ll get into all that cool stuff at our upcoming SQL in the City Streamed event taking place on Wednesday, December 13, 2017. Click here now to get registered. We’ll also teach you about DevOps, monitoring, GDPR compliance and a whole lot more. All this done the Redgate way.