Error Message:
Server: Msg 107, Level 16, State 3, Line 1
The column prefix ‘t1’ does not match with a table name or alias name used in the query.
Severity level:
16.
Description:
SQL Server allows to specify a table alias for each table involved in your query. Typically this is in the form
SELECT < list >
FROM table AS alias_name_goes_here
However, SQL Server must able be able to unambiguously resolve column names. If you use use a column prefix for which SQL Server cannot find either a table or a table alias, this error is raised.
Consequences:
The SQL statement cannot be parsed and further execution is stopped.
Resolution:
Error of the severity level 16 are generated by the user and can be fixed by the SQL Server user.
Versions:
All versions of SQL Server.
Example(s):
SELECT t1.OrderID
FROM Northwind..Orders
Remarks:
As long as SQL Server can resolve the column name in a query, it is valid to write a statement such as:
SELECT OrderID
FROM Northwind..Orders t1
Even though we have given the Northwind..Orders table the alias t1, we don’t use it in the SELECT list and still get a resultset returned. But what works in such simple scenarios is likely to break in complex queries. So, as a matter of fact, if you decide to use a table alias in a query, apply that alias consequently to all column of that table you reference in that particular statement. Not only do you reduce the likelihood of errors in code, but also you greatly improve its readability and maintenability.