Code analysis incorrect - alias

ggeier

The code analysis for aliases isn't correct. For example, this query:

select my_alias = o.name
from sys.objects o

Gives this issue: Old-style column alias via EQUAL sign. It is recommended to specify alias via AS. SQL uses the AS keyword to specify an alias. The assignment syntax is deprecated.

It's not a deprecated feature though according to: https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017

'string_alias' = expression is deprecated, but not column_alias = expression.

Tianjiao_Li

Hi @ggeier ,

Thanks for reporting it! We've logged it as a bug and please keep an eye on our release note!

way0utwest

Very true, I'll pass along.

ggeier

Great, thank you!

FreWeh

It would also be nice to be able to use the equal alias when formatting the code in SQLPrompt.

way0utwest

What do you mean? You can use the equal alias. Do you mean somehow Prompt puts a value there? Can you provide an example of pre and post formatting?

FreWeh

I'm so sorry, My misstake. I was looking in the formatting styles section and didn't find it there. Now I found it under Format Styles. So please discard my previous comment.

JoDouglass

Any news on this fix?

Under Style rules, I think ST002 (Old-style column alias via EQUAL sign) is wrong for the same reason. The help claims that this style of alias is deprecated - it isn't, and afaik there is no reason to flag this style up as problematic.

The only argument I'm aware of for not using it is that it's SQL Server specific. If the rule is going to remain, could the help files perhaps be updated to reflect this?

I know it's a less popular style, but I greatly prefer it for readability reasons - and I'm in good company.

http://sqlblog.com/blogs/aaron_bertrand/archive/2012/01/23/bad-habits-to-kick-using-as-instead-of-for-column-aliases.aspx

way0utwest

Sent a note to the team.

Phil_Factor

I'm afraid that, despite pining for the 'good old' assignment syntax, I count this as a 'SQL Smell'. SQL Syntax uses the AS keyword to specify an alias. Microsoft recommend the AS syntax. Nowhere does the current MS documentation suggest using the old Sybase dialect that uses the assignment = operator. This is retained by SQL Server for the time being purely for backward compatibility. As Joe Celko says 'The ANSI Standard is <expr> AS <alias>; this is used in many other places, too. Your dialect (Sybase Dialect) overloads the equal sign, destroys portability and it is difficult to read if you know SQL.'
One detail in the description ought to be clarified. It is only the 'string_alias' = <expression> syntax that is deprecated. For now the <column_alias> = <expression> is still there but is liable to be deprecated in future releases because it is non-standard. The SQL standards and Microsoft both say we should use the AS syntax.

FreWeh

Is this insider information from Microsoft? It's nowhere to be find when reading the docs. It would be a big misstake, in my opinion, to deprecate it since it's the best option for readability. And since it's not yet deprecated you shouldn't flag it as if it was!

JoDouglass

Likewise, I'd be interested to know if there's evidence this is likely to be deprecated in future, or where MS are saying we should avoid using it. I've not seen anything suggesting either, and the list of deprecated features even suggests it as a suitable alternative to the deprecated 'string_alias' = <expression> syntax.

It would be a real shame if it happened as it's so much more readable having the aliases first in an aligned list rather than having to find them at the end of the columns. I'm a late convert to this style, having moved over from Oracle, and then used the standard syntax the first couple of years I was on SQL Server. Despite years of using the AS syntax I find this much easier to read and a big time saver.

I've never given the portability issue much weight - in the highly unlikely event I had to move all my SQL to a different platform, changing alias styles would be the least of my worries! And I could switch them all using SQLPrompt before migrating.

Obviously if it were deprecated I'd have to switch at some point regardless of it being beneficial, so it would be good to know if I've missed something.

way0utwest

I haven't seen anything about alias=expression being deprecated. Just 'alias=deprecated.

I wouldn't count it as a code smell. It's not ANSI, but the expression as alias isn't remotely readable in complex code. Once you have expressions that grow and don't format well in the horizontal space, finding the alias can be cumbersome.

Phil_Factor

I think we're all agreed that 'string_alias' = <expression> has been deprecated, but that <column_alias> = <expression> is still supported, though it is not in the SQL standard, nor currently recommended by Microsoft.
Most of these checks are merely advisory, and SQL Prompt evidently allows you to switch on and off all the Code checks individually. It is always down to individual judgement and preferences. After all, some people still like using the old Sybase inner join syntax which is still likewise supported by SQL Server and free from deprecation (unlike the sybase outer join syntax!) . I agree that the description should be elaborated to point out that <column_alias> = <expression> is still supported.
Was either 'string_alias' = <expression> or <column_alias> = <expression> ever in the SQL Standard? I've looked back to 1999 but can't find it.

Oddvar

<column_alias> = <expression> may not be recommended by Microsoft, but they are still using it themselves (see e.g. definition of sys.sql_modules), although it is probably old Sybase legacy code...

Phil_Factor

(Nervously looking through some of my own articles and blogs)

RobinHC

So ... How can I disable this in SQL Prompt until it is corrected? It makes it hard to see valid concerns when the page is full of "old style column alias via equal sign" false alarms

way0utwest

https://voiceofthedba.com/2018/01/29/managing-sql-prompt-code-analysis-rules/

There's a menu option

tomcorty

Hi, I like the tool as part of the SSMS. However, we've a created a set of Custom Static Code Analysis Rule extension to Microsoft SQL Server Data Tools (SSDT) with great results. One IDE for all kind of projects and we can have one solution containing multiples projects

Tianjiao_Li

Hi @ggeier

This issue is fixed now. Please upgrade to the latest version!

Thanks.