T-SQL Tuesday – Bad Habits – Fixed by SQLPrompt

TSQL2sDay150x150T-SQL Tuesday again. And it’s been a few months since I last blogged, let alone for this blog-party, but, hey, I’m here now. This time, Aaron Bertrand (blog|@aaronbertrand) has given two options – and, given the amount time I’ve been spending lately talking about, seeing and performing in operas, I thought I would talk about T-SQL. And, yeah it’s another redgate-related SQLPrompt Post. <disclaimer/disclosure>They don’t pay me, they’ve not asked me to write this, but they do provide me with software, and I would buy this tool myself if I wasn’t on the FoRG programme…

Bad Habits

Aaron has been busy collecting a list of many many bad T-SQL / SQL Server habits, and some of them are scary.

The one I’m writing about – Using AS instead of = for Column Aliases – is the subject of a holy war, despite being mostly seen as stylistic.

Some of you DBA weirdos insist on writing code like

SELECT x = col1...

rather than the proper, ANSI-standard form of

SELECT col1 AS x...

Even though using “AS” conforms with the ANSI standard, Aaron is against it. He’s wrong, and I guess there are plenty of other deviants out there, but there are plenty of us right-minded people who prefer the proper version.

If you’re working in a big team, though, you can run into real problems where there’s a mix of styles in use.

SQLPrompt’s “Code Smells”

Redgate’s SQLPrompt has new functionality to identify what it calls “code smells” – bits of non-standard functionality, or deprecated usages. It highlights these smelly lines of code with a green wavy line, like you see under dodgy grammar in Word.

If you put your cursor into the green wavy line, the smelly bit is highlighted and a new blue icon appears in the left hand gutter. Click on that, or press CTRL, and up comes a window that describes the problem, and gives you a link to redgate’s documentation about it. OK, it’s a bit sparse, but that’s not the point of this post.

De-stinking code

If you’ve got a lot of code to review, and want to fix this easily, fortunately you can apply this fix automatically using SQLPrompt as part of its reformat sweep.

You’ll need to check the “Apply column alias style” box, and then pick the right style (above), but that’s all you need to do. (OK, if you’re a deviant like the guy I work with, you can set it to use one of the other WRONG formats, but don’t do that because it’s WRONG…)

A quick CTRL+K, CTRL+Y later, and you get properly formatted code:

And as for my strange friend, if he wants to see it in the wrong format, he can set up his own preferences. Or he could, if he had a licence for SQLPrompt, which he doesn’t… Yet…

This entry was posted in T-SQL Tuesday and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.