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

Last reviewed: May 2, 1997
Article ID: Q139528

The information in this article applies to:

Microsoft SQL Server, version 6.0

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 : kberrmsg

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.