Guys, rather than repeatedly lamenting how terrible it is that you got the question "wrong" (as did I) even though it works in SQL 2000 / 2005, can anyone explain why it works???

if you run the following, then you get an error:

Select 1

union Select 2

union Select 3

union Select 4

union Select 5

union Select 6

union Select 7

UNION Select 'A'

union Select 'B'

union Select 'C'

union Select 'D'

That is normal / expected SQL Server behaviour. I believe the first result set in the UNION defines the data type that will be used for the rest of the UNION statement, but there is probably more information in the link attached to the question.

Now as soon as you add an INSERT (to a table with appropriate type) in SQL 2000, the error goes away:

Create Table Test(col varchar(10))

GO

Insert into Test

Select 1

union Select 2

union Select 3

union Select 4

union Select 5

union Select 6

union Select 7

UNION Select 'A'

union Select 'B'

union Select 'C'

union Select 'D'

Is this because of some compiler optimization?? Logically, you would expect the UNION clauses to be evaluated first, any duplicates removed, and then afterwards convert the resulting single resultset into the necessary types for insert into the destination table - but somehow the destination type is being used during the evaluation of the UNION clauses.

Does anyone have details on how this happens?