INFO: Table Alias Syntax Checking in SQL 6.0 and Later is ANSI-Compliant (139528)

SUMMARY

When working with a table alias (a "correlation name" in ANSI terminology,) the syntax checking in Microsoft SQL Server version 6.0 and later has changed to comply with the ANSI specification. ANSI states,

A <table name> ... is exposed ... if and only if the <table reference> does not specify a <correlation name>.

In previous versions of Microsoft SQL Server, you could qualify columns of a table by using either the table name or a table alias. In SQL Server version 6.0 and later, if an alias has been provided for a table name in the FROM clause, you can only use the alias to qualify columns from the table; the table name cannot be used elsewhere in the statement because they are flagged as syntax errors.

MORE INFORMATION

As an example of the difference in behavior, assume this script has been executed:

   use pubs
   go
   select authors.au_lname from authors aa where au_lname like 'W%'
   go
   select aa.au_lname from authors aa where authors.au_lname like 'W%'
   go

In both SELECT statements, notice the use of "authors" to qualify the column "au_lname" even though an alias, "aa", has been provided to substitute for the table name. On previous versions of Microsoft SQL Server, the results of each of these SELECT statements is:

   au_lname
   ----------------------------------------
   White

   (1 row(s) affected)

Whereas on Microsoft SQL Server 6.0 and later the following error message is given:

Msg 107, Level 15, State 1
The column prefix 'authors' does not match with a table name or
alias name used in the query.

In SQL Server 6.0 and later the following SELECT statement is equivalent to the ones above:

   select aa.au_lname from authors aa where aa.au_lname like 'W%'

This behavior is NOT affected by the current setting of Trace Flag 204.