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).
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).
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT Tables = Schema_Name(schema_id)+'.'+[name] FROM sys.tables SELECT "Tables" = Schema_Name(schema_id)+'.'+[name] FROM sys.tables SELECT [Tables] = Schema_Name(schema_id)+'.'+[name] FROM sys.tables SELECT 'Tables' = Schema_Name(schema_id)+'.'+[name] FROM sys.tables SELECT Schema_Name(schema_id)+'.'+[name] [Tables] FROM sys.tables SELECT Schema_Name(schema_id)+'.'+[name] 'Tables' FROM sys.tables SELECT Schema_Name(schema_id)+'.'+[name] "Tables" FROM sys.tables SELECT Schema_Name(schema_id)+'.'+[name] Tables FROM sys.tables SELECT Schema_Name(schema_id)+'.'+[name] AS [Tables] FROM sys.tables SELECT Schema_Name(schema_id)+'.'+[name] AS 'Tables' FROM sys.tables SELECT Schema_Name(schema_id)+'.'+[name] AS "Tables" FROM sys.tables SELECT Schema_Name(schema_id)+'.'+[name] AS Tables FROM sys.tables |
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 …
1 |
SELECT Schema_Name(schema_id)+'.'+[name] AS Tables FROM sys.tables |
… or …
1 |
SELECT Schema_Name(schema_id)+'.'+[name] AS "My Tables" FROM sys.tables |
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.