How is the metadata of the result of a UNION determined?

Hi,

A colleague and I have just found a slightly strange situation that we don't understand.

We had (effectively) the following query:

select cast(1 as decimal(38,10))
union all
select cast(1 as decimal(38,4))

And the result contained 2 rows, each with a a scale of 4. This surprised us, we expected that the metadata of the result would be determined by the topmost query.

So we reversed them and tried this:

select cast(1 as decimal(38,4))
union all
select cast(1 as decimal(38,10))

and got exactly the same result. 2 rows with a scale of 4.

We can't understand why the scale always gets determined to be 4 regardless of the order of the queries.

Any explanation would be much appreciated!

Thanks

Jamie

Doesn't matter. The answer's here: http://msdn2.microsoft.com/en-us/library/ms190476.aspx

-Jamie

|||

In addition to the "Precision, scale and length" topic, the UNION operator topic also documents how the data conversions happens if the various SELECT statements contain columns with different data types. See link below for more details:

http://msdn2.microsoft.com/en-us/library/ms180026(SQL.90).aspx