Product articles SQL Prompt SQL Code Analysis
SQL Prompt code analysis: avoid…

25 July 2018

2 Comments

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.

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).

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 …

SELECT Schema_Name(schema_id)+'.'+[name] AS Tables FROM sys.tables

… or …

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.

Share this post.

  • 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

You may also like

  • Community event

    Raleigh Code Camp

    Redgate will be remotely sponsoring the Raleigh Code Camp 2019. Code Camp is a place for local software developers to come and learn from their peers. This community-driven event has become an international trend where peer groups of all platforms, programming languages and disciplines band together to bring content to the community.

  • Article

    Beyond Formatting: Improving SQL Code using SQL Prompt Actions

    Louis Davidson explains how he uses the SQL Prompt refactoring 'Actions' to qualify object names, standardize the use of aliases, and make other instant changes that improve the overall quality of his SQL code.

  • Article

    SQL Prompt by Keyboard

    Phil Factor shares a handy keyboard shortcut diagram that reveals some of the most useful of Prompt's keyboard shortcuts, and a wallchart showing every piece of Prompt functionality, what it does, where to find it in the various menus, and of course its keyboard shortcut.

  • Article

    Quick SQL Prompt tip – why you should expand the wildcard

    One of the great things about SQL Prompt is that it quickly removes the need to use so many keystrokes. That’s helpful and handy, but to become a really efficient T-SQL coder, you’ll want to practice incorporating a few tricks into your routine. Here’s a good one. Often I run into tables and can’t remember