20 December 2019

1 Comment

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.

20 December 2019

1 Comment

Using Aliases in SQL Prompt

Phil Factor explores the role of table aliases, explaining when they are required, and their general purpose otherwise, the need for sensible naming of aliases, and how SQL Prompt handles them.

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.

The primary purpose of table aliases, aside from cases where they are required, is to make SQL queries easier to read and understand. No, this isn’t an error in my typing. Aliases aren’t there to make it easier and faster to type in SQL queries. This is especially true if you are using SQL Prompt because it does all that typing for you.

If you enable its Assign aliases option, SQL Prompt will auto-suggest one-character or two-character aliases, as you type in your SQL statements, just because it must provide something but cannot provide meaning. It doesn’t do it because short meaningless names are better. It just means that it is up to the database developer to improve that default alias to provide meaning.

Where can I use table aliases?

In standard SQL, aliases are only used in SELECT statements. It would be truer to say that you can only use them in statements that have a FROM clause, because SQL Server has syntax that allows it to use a FROM clause in UPDATE and DELETE statements. When you use a table alias, you are just providing a specific name for the instance of a table reference, not the actual table. It is only that reference that is aliased.

Do I always need to qualify column names?

In SQL, you only need to qualify column names with the reference to the table source if the column name by itself is ambiguous, in that it could have come from one or more table sources in the FROM clause. With SQL Server, the use of the qualified column names makes things easier for the parser anyway and makes the query easier to read.

However, if there are no JOIN clauses, then the adding any sort of table qualifier, alias or not, to the column name is entirely superfluous and shouldn’t be used.

Do I always need to use a table alias?

To qualify a column to its table, you generally don’t have to use table aliases (also known as correlation names in ANSI SQL). You can just use the name of the table source that supplied the column.

The use of aliases is only obligatory if you are referencing a table source that doesn’t have a name, such as a derived table or rowset function, or if you are using a named table source, such as a table, view or table-valued function, more than once in the same query. In general terms, if a table source has already got a suitable name, then why provide another? Almost always, you do it only to make your query clearer in its intention. If it doesn’t, then it just gets in the way.

When are aliases required?

One reason for needing to use an alias is when the name of the base table has illegal characters in it. You might look outraged and say you’d never stoop so low as to do that, but on the other hand ‘@’ isn’t legal at the start of a table name qualifier, so table variables are a problem. For example, if you don’t give a table variable an alias, then you must provide the square bracket delimiters to refer to it by its full name. Of course, if the table really has illegal characters, then a table alias provides a blessed relief from those pesky square brackets.

You must also qualify a column with a table alias, rather than rely on the table name, if it is in a table that is being used twice in a JOIN. For example:

This has sys.objects joined with sys.objects so this couldn’t be made to work without using aliases. By choosing meaningful names for them, we also avoid mistakes.

If you are joining to a table source such as a query, then you need to provide a name. No default name exists. Here we’ll create two table sources from multi-line values, and then join them.

In our first example we used the AS keyword to show that we were introducing an alias. It is optional, so you can leave it out, but that’s a bad practice because it makes your intentions less clear.

How SQL Prompt handles aliases

SQL Prompt will, with the Assign Aliases options enabled (SQL Prompt > Options > Inserted code > Aliases), automatically assign aliases to your table sources. However, it can’t guess the story behind your SQL, so it can’t provide you with meaningful aliases, only abbreviations. Unless you provide your current naming conventions as custom aliases, it uses the first letter of the table or view name to generate an alias, whenever possible. Where that initial character is already taken, it will use two characters.

If you are unfortunate enough to be stuck with a ‘tibbling’ convention, where all your tables have a tbl prefix, you can tell SQL Prompt to ignore them (by adding them to the Prefixes to ignore list). If the name has underscores or hyphens, or is composed of two CamelCase words, such as CustomerAccounts, then SQL Prompt will take this into account when generating a two-letter alias.

Where there would otherwise be ambiguity, SQL Prompt creates additional aliases for the same table when it is used in self-joins. Here is the same code we saw earlier, to get the names of tables and their constraints, but with SQL Prompt’s alias suggestions. It removes the ambiguity, but would you be so confident, in reading this version, that the ON clause is correct, or even in understanding what you were aiming to deliver and how?

When SQL Prompt assigns an alias, it remembers it within the current query editor window, as you will see from the suggestions provided for you in the suggestion box as you work on the query and add clauses to it.

If existing code uses joins and doesn’t use table aliases then SQL Prompt will underline the table references with green squiggles, indicating violation of its ST010 code analysis style rule. Personally, I prefer to disable this rule, unless working to a style guide that requires every SELECT involving more than one table source to use aliases as qualifiers. More straightforwardly, Prompt also underlines the unqualified column names for violation of MI003.

If you add a table alias and then run Format SQL command, it will automatically use the alias to qualify the relevant column names (if the Qualify object names action is enabled).

Adding Custom Aliases to SQL Prompt

You can specify user-defined aliases for table or views, based on your established naming conventions. SQL Prompt doesn’t currently support the import or export of custom aliases, separately from the other Prompt options, so you must key them in one-at-a-time.

To add a user-defined alias, the head for the Aliases window in the Prompt ‘Options’ window. Under the heading Custom aliases, click New and then enter the name of the object and its alias, and hit Save.

Even if you could provide meaningful custom aliases, their added length might be enough to prevent them fitting the suggested ‘ON expressions’ in the Suggestions Box. In the following screenshot, I’ve used a silly alias, TheTablesAsListedInTheSystemViews, just to illustrate what I mean. It overflows the suggestion box.

An easier solution in such circumstances is to use search and replace to change the defaults that Prompt provides once the query or routine is completed, but before it is saved.

Summary

Table aliases are sometimes essential and are usually useful for helping to clarify the intention of more complex SQL queries. However, they aren’t always required or necessary, and there is nothing to be gained from using them in FROM clauses if there is no JOIN.

With SQL Prompt, I opt for changing the suggested short alias that it provides for something more explanatory. It holds no grudge, but instead provides me suggestions based on my chosen custom alias. This saves a great deal of monotonous typing, for which I am grateful.

  • Chingyin Kong

    I strongly recommend using it to avoid catastrophic issues and do the task with less typing. Here is one example happened on one testing environment a while ago, although there was no explicit join in it:

    — create tables
    create table #table1
    (col1 int ,
    col2 datetime,
    col3 varchar(50)
    )
    create table #table2
    (col4 int,
    col5 datetime,
    col6 varchar(50)
    )
    — some data
    insert into #table1
    values(1, ‘2019-12-20’, ‘order1’)
    ,(2, ‘2019-12-21’, ‘order2’)

    insert into #table2
    values(3, ‘2018-12-20’, ‘order100’)
    ,(4, ‘2018-12-21’, ‘order200’)

    — update incorrectly: all were updated
    update #table1
    set col2=getdate()
    where col1 in (select col1 from #table2)–Note: col1 does NOT exist on #table2, since it exists on #table1, it’s OK to the parser

    select *
    from #table1

    if doing aliasing or with full table name, the error would be avoided due to syntax error
    — correct update
    update #table1
    set col2=getdate()
    where col1 in (select t2.col1 from #table2 t2)
    update #table1
    set col2=getdate()
    where col1 in (select #table2.col1 from #table2)

    I learned this from unexpected update.

You may also like

  • Article

    Enhancing your database development using coding standards

    Development and IT departments face increasing demands to deliver changes faster, often with increasing complexity and less tolerance for downtime, and without a corresponding growth in team size to handle the extra work. Standardizing database development practices by establishing coding standards can help achieve this by removing blockers to understanding code, easing the implementation of

  • Article

    SQL Prompt Code Analysis: Table does not have clustered index (BP021)

    If SQL Prompt alerts you to a table without a clustered index, investigate the reason for its absence carefully. It is rare indeed to find a table where data retrieval is faster without one.

  • Community event

    PASS Deutschland eV - Berlin chapter

    Redgate will be remotely sponsoring the October meeting of the PASS Deutschland – Berlin chapter. PASS Deutschland eV is the German Microsoft Data Platform Community, ie topics from this environment are treated within the framework of the association. Not only do we rely on the SQL Server and its many services, tools and components, we also deal with

  • Article

    Choosing Between Table Variables and Temporary Tables (ST011, ST012)

    Phil Factor demonstrates the use of temporary tables and table variables, and offers a few simple rules to decide if a table variable will give better performance than a temp table (ST011), or vice-versa (ST012).

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly