October 13, 2009 at 1:59 pm
One of databases I develop for is being upgraded to SQL 2008 (from SQL 2000).
The upgrade advisor is flagging an issue that I don't think is an issue. I was hoping that there is documentation that this is a known issue so that my DB team will just let it pass.
The error is saying that in SQL 2008 you cannot use a table alias and a column alias together. It also says that the sprocs that use these will not compile.
Here is the different SQL Scenario's that are causing this:
select
case
when tblOneAlias.COLUMN_NAME is null then tblTwoAlias.COLUMN_NAME
else tblOneAlias.COLUMN_NAME
end as COLUMN_NAME
from tblOne tblOneAlias
join tblTwo tblTwoAlias
on tblOneAlias.JOIN_VALUE = tblTwoAlias.JOIN_VALUE
order by tblOneAlias.COLUMN_NAME, tblTwoAlias.COLUMN_NAME
select tblAlias.COLUMN_NAME as 'COLUMN_NAME'
from tblName tblAlias
order by tblAlias.COLUMN_NAME
select COLUMN_NAME = tblAlias.COLUMN_NAME
from tblName tblAlias
order by tblAlias.COLUMN_NAME
In each scenario an alias is created that matches the actual column name (not usually a good idea I agree).
However, they compile just fine in SQL 2008 (with compatibility level set to 10). I think the Upgrade Advisor is just confused because the alias is the same as the column name. I agree that there is some "less than desireable code" here. But I don't think it needs to be changed to upgrade to SQL 2008.
The fewer things we can change with this upgrade means the fewer things to look into if something breaks when when we roll out to production.
If anyone knows of any documentation saying this is a known limitation then please let me know.
Also, if I am wrong and these are not allowed in SQL 2008 somehow (though they compile just fine) then I would also like to know it.
October 13, 2009 at 2:31 pm
In the upgrade process, are you setting up a test environment and putting the database and all its procs through their paces?
If so, you'll know with total certainty that this isn't a problem, as soon as you get it into testing.
If not, then minor issues like a warning from the upgrade advisor are likely to be the least of your worries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 13, 2009 at 2:48 pm
I think I know exactly what "Column aliases in ORDER BY clause cannot be prefixed by table alias"?
The advisor isn't smart enough to say that there IS a problem just that there MIGHT be a problem and you have to go look. In all the cases I have run accross it wasn't a problem and the warning was not relevant. But you can't just assume. It is important to realize that we are talking about column aliases NOT table aliases, column aliases are defined in the SELECT clause. Read below.
This is from the upgrade advisor help file:
New: 5 December 2005
In SQL Server 2005, column aliases in the ORDER BY clause cannot be prefixed by the table alias.
Component
Database Engine
Description
For example, the following query executes in SQL Server 2000, but returns an error in SQL Server 2005:
Copy Code
USE AdventureWorks;
GO
SELECT FirstName AS f, LastName AS l
FROM Person.Contact p
ORDER BY p.l
The SQL Server 2005 Database Engine does not match p.l in the ORDER BY clause to a valid column in the table.
Exception
If the prefixed column alias that is specified in the ORDER BY clause is a valid column name in the specified table, the query executes without error; in SQL Server 2005, the semantics of the statement might be different. For example, the column alias (id) specified in the following statement is a valid column name in the sysobjects table. In SQL Server 2000, when the statement executes, the CAST operation is performed after the result set is sorted. This means the name column is used in the sort operation. In SQL Server 2005, the CAST operation occurs before the sort operation. This means the id column in the table is used in the sort operation and returns the result set in an unexpected order.
Copy Code
SELECT CAST (o.name AS char(128)) AS id
FROM sysobjects AS o
ORDER BY o.id;
Corrective Action
Modify queries that use column aliases prefixed by table aliases in the ORDER BY clause in either of the following ways:
Do not prefix the column alias in the ORDER BY clause, if possible.
Replace the column alias with the column name.
For example, both of the following queries execute without error in SQL Server 2005:
Copy Code
USE AdventureWorks;
GO
SELECT FirstName AS f, LastName AS l
FROM Person.Contact p
ORDER BY l
USE AdventureWorks;
GO
SELECT FirstName AS f, LastName AS l
FROM Person.Contact p
ORDER BY p.LastName
CEWII
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply