Microsoft KB Archive/139528

= INFO: Table Alias Syntax Checking in SQL 6.0 and Later is ANSI-Compliant =

Article ID: 139528

Article Last Modified on 3/14/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q139528



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 ... is exposed ... if and only if the does not specify a.

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.

Additional query words: sql6 windows nt syntax correlation alternate

Keywords: kbinfo kbusage KB139528

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.