Migrating to MSSQL 2005 and UNION Queries
For the most part, migrating your ColdFusion site from Microsoft's SQL 2000 server to SQL 2005 is a snap. Import the databases from 2000 to 2005, re-point your data sources to the new instance using the ColdFusion Administrator and you are done. No muss, no fuss. There is very little query code that you will need to change. Sometimes you needn't change anything in your ColdFusion code at all. Here is one that I found recently however that you may run across - especially if you are a fan of UNION queries. Here's the skinny.
Let's say you have a UNION query that merges 2 queries and orders the results. In each query you are selecting the same number of columns and the same columns and column orders. The tables may or may not be the same, but the aliases you use are the same in each query. Here's an example.
SELECT P.product, 'Active' AS prodStatus,
O.ord_no, O.cost, O.shipping,
O.status, O.OrdDt
FROM Orders O JOIN Products P
ON O.product_id = P.Product_Id
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
UNION
SELECT P.product, 'Active' AS prodStatus,
O.ord_no, O.cost, O.shipping,
O.status, O.OrdDt
FROM Orders O JOIN Prod_archive P
ON O.product_id = P.Product_Id
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
ORDER BY prodStatus, P.product
In this example I have chosen a union with 2 separate tables - product and product_archive. The ordering occurs by prodStatus (a derived column) and product, a column coming from prod_archive in one instance and products in the other. This code works find in MS SQL 2000. In MS SQL 2005 however, this code throws an error that says The multi-part identifier "P.product" could not be bound. Apparently, MS SQL 2005 does not treat the table alias "P" the same way as 2000. It looks at it and tries to figure out the table rather than just tying it to columns already selected - hence the reference to "multi-part identifier" and binding. To fix it however, you can simply remove the Alias like so:
SELECT P.product, 'Active' AS prodStatus,
O.ord_no, O.cost, O.shipping,
O.status, O.OrdDt
FROM Orders O JOIN Products P
ON O.product_id = P.Product_Id
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
UNION
SELECT P.product, 'Active' AS prodStatus,
O.ord_no, O.cost, O.shipping,
O.status, O.OrdDt
FROM Orders O JOIN Prod_archive P
ON O.product_id = P.Product_Id
WHERE OrdDt Between '2005-07-18' AND '2005-07-19'
ORDER BY prodStatus,product
This Causes MSSQL 2005 to only deal with ordering the column. Semantically I like this change (I'm never anti-semantic). It makes more sense to me since the alias actually refers to different tables in some cases. In hind-site I think perhaps that different aliases should be used altogether in a union query just for clarity. But when I do that it destroys the symmetry of the query blocks - it's not as obvious that the select portion is identical - so it results in more difficult reading and possibly debugging.
5 Comments
-
That's interesting. When dealing with UNIONs in 2000, I was always getting error messages about Ordering within UNIONs. I always ended up having to do something like this:
SELECT
. . . . t.id
FROM
. . . . (
. . . . . . . . . . . . SELECT id FROM tableOne
. . . . . . . . UNION
. . . . . . . . . . . . SELECT id FROM tableTwo
. . . . ) AS t
ORDER BY
. . . . t.id ASC
I think this is, in intent, doing the same thing as the 2005 syntax - moving the ordering to the derived table, rather than referring to the inner tables columns. -
you do know that you can set the database on sql2005 to use sql2000 compatibility and bypass all this stuff. this is assuming that you have no use for the sql2005 specific features in your application any time soon. -
When you think about it, though, the lack of a table qualifier in the ORDER BY clause really makes sense. In a UNION query, you're *not* actually sorting by the values of a table's column, you are sorting all the results across the aggregate union of 2 or more tables (or WHERE clause slices). In other words, the union creates a single set of aggregate data and then sorts it, so that by definition the ORDER BY only applies to the aggregate set of data, regardless of the tables. In Mark's example, the alias 'product' refers to the 'new' column which is in the 'product' position and happens to represent both Products.product and Prod_archive.product.
Basically, it seems like 2005 is simply applying greater specificity to its use of aliases, which is very good to know as I have a few apps which will soon be making the transition as well. -
I saw this problem recently when upgrading to a 2005 server. Something else to note about upgrading to 2005 (and performance).
All queries should be scoped to the correct schema, so rather than doing:
SELECT * FROM TableOne
you should be doing:
SELECT * FROM dbo.TableOne
Rather than modifying *all* your queries (which we ended up doing anyways), you can update the default schema for all your users:
http://blogs.msdn.com/mssqlisv/archive/2007/03/23/...
Sites to Watch
Aggregators
RSS
Related Blog Entries