25 July 2018

2 Comments

25 July 2018

2 Comments

SQL Prompt code analysis: avoid non-standard column aliases (ST002 and 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

  • Article

    Quick SQL Prompt tip - picking the columns you need for speedy results

    One of the poor practices that I see so many people doing in code is using SELECT *. This is poor practice because it takes more time to complete for clients, it’s an unnecessary use of resources on the client, server and network, and it can fill the buffer cache with rarely-used data. For example,

  • Article

    Cleaning up common T-SQL coding issues with SQL Prompt

    Some SQL coding habits are just annoying. Commas in front of column names? No way! Others are actively harmful; they’ll make your code error prone, harder for others to read and understand, and even harder to edit without making mistakes. This article covers five harmful problems that I see regularly in T-SQL code, and shows

  • Article

    Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

    SQL Prompt’s Code Analysis feature helps you discover code issues and hidden pitfalls during code development, as you type. It also provides tips for improving your code, and includes links to documentation that offers advice on the about each of the supported rules, so you can decide on the best course of action. This is

  • Article

    Driving up database coding standards using SQL Prompt

    Most of us in the data management industry will have learned to adapt, in recent years, to ‘agile’ development and deployment practices. Many organizations have invested heavily in the tools and processes they hope will allow them to deliver new functionality to users more frequently and reliably, while also maintaining quality standards. To achieve this,

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly

  • 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