Phil Factor

20 December 2019

1 Comment

• SQL Prompt
• SQL Formatting and Styles
• Exploring and Refactoring Databases

Using Aliases in SQL Prompt

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.