INF: Table Alias Syntax Checking in SQL 6; Now ANSI Compliant

ID: Q139528

The information in this article applies to:

SUMMARY

When working with a table alias (a "correlation name" in ANSI terminology), the syntax checking in Microsoft SQL Server 6.0 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 6.0, 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 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 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.

Additional query words: sql6 windows nt syntax correlation alternate 


Keywords          : kbusage SSrvTran_SQL 
Version           : 6.0
Platform          : WINDOWS 
Issue type        :

Last Reviewed: April 21, 1999