25 July 2018

2 Comments

25 July 2018

2 Comments

SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)

If you declare a column alias using equals SQL Prompt will raise a violation of a style rule (ST002). It's best to follow the ANSI-SQL92 standard of assigning aliases using the AS keyword. If the alias is not a standard identifier, delimit it with double quotes. using single quotes has long been deprecated (DEP021).

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

There was a time when column aliases in SQL Server and Sybase were only declared by an assignment expression, and some people are still fond of the old way of doing it, in much the same way as some of us still like line-dancing, or singing madrigals dressed in tights. Nowadays, the other relational database systems such as PostgreSQL, MySQL, SQLite and Oracle, all follow the use of the AS syntax for aliases, as defined in the ANSI-SQL92 standard for assigning a name to the column of a result.

However convenient the archaic syntax is for you, it is generally better to stick to this official syntax. Firstly, it is likely that someone in the future will have to maintain your code, and will blink in incomprehension at the non-standard syntax, not having been reared on it. Secondly, Microsoft could easily decide that it is no longer convenient to support the old Sybase syntax in SQL Server and your code won’t then transfer to those new versions.

Column alias violations

There are plenty of ways of doing it that SQL Server will tolerate, for old times’ sake. Some of these following examples use the old-style of declaring the column alias using equals, which causes SQL Prompt to raise a violation of a style rule (ST002), and others use string literals (‘Tables’) as the column alias, which has long been deprecated, and so violates one of SQL Prompt’s deprecated syntax rules (DEP021).

All these give the same result.

The right ways to alias

However, of these 12 varieties, only two follow the official ANSI syntax for the SELECT statement which is to use the AS keyword, and to put the identifier, if required, in double-quotes. The use of ‘string_alias’ = expression was flagged as being deprecated in SQL Server 2016, but this really applies to any use of single quotes.

I reckon the safest approach is to use the ANSI syntax. If the literal doesn’t follow the rules for a SQL Server identifier, or is a reserved word, then it should be in double-quotes, not the single-quotes string delimiters. SQL Server’s preferred use of square brackets to delimit alias names comes from Sybase.

I realise that the AS keyword is optional, but it’s a good habit to use optional ANSI keywords, if it makes the code easier to read and understand, though it is possible to overdo it. All of us balk at using NATIONAL CHARACTER VARYING( n ), instead of NVARCHAR(n).

So, the best forms, I believe, are …

… or …

The old-fashioned assignment syntax should be avoided unless you are particularly keen on it, because it isn’t standard SQL and is only tolerated to accommodate legacy code written for previous versions of SQL Server.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Andy Hammer

    When I use the AS “My Tables” format from your last example SQL Prompt complains that “String literals as column aliases are deprecated”. Seems odd that the format you recommend is flagged. I am not complaining, just trying to understand how to write code that SP doesn’t flag.

  • Hakim

    I prefer this style:

    select Tables = Schema_Name(schema_id)+’.’+[name] FROM sys.tables

    When you have multiple columns in the select statement, they line up neatly on the left. Makes searching for aliased names and troubleshooting easier.

    select MySchema = TABLE_SCHEMA
    , MyTables = TABLE_NAME
    , MyType = TABLE_TYPE
    from INFORMATION_SCHEMA.tables