What is the data type of "select 123.866" in SQL server 2005?

Question

If I just write something like

select 10.00;

What type does this give me?

Here's a test I ran, to test the binary representation of these types. The big surprise here is that none of the casts actually match the first row!

select cast(123.866 as binary) union all 
select cast(cast(123.866 as real) as binary) union all 
select cast(cast(123.866 as float) as binary) union all 
select cast(cast(123.866 as decimal) as binary) union all 
select cast(cast(123.866 as numeric) as binary) union all 
select cast(cast(123.866 as money) as binary) union all 
select cast(cast(123.866 as smallmoney) as binary)

--------------
0x0000000000000000000000000000000000000000000006030001DAE30100
0x000000000000000000000000000000000000000000000000000042F7BB64
0x00000000000000000000000000000000000000000000405EF76C8B439581
0x00000000000000000000000000000000000000000000120000017C000000
0x00000000000000000000000000000000000000000000120000017C000000
0x00000000000000000000000000000000000000000000000000000012E684
0x00000000000000000000000000000000000000000000000000000012E684

Can anyone explain this?

---

Originally, all I wanted to do was avoid having to write the cast statement, assuming 123.866 was implicitly a decimal. So I thought to test if these two statements were the same:

select cast(123.866 as decimal) 
select 123.866

Answer 1

It's implicitly typed as decimal(6,3) - cast to this and you'll see the binary values match. It seems to use decimal at the smallest tpe to fit the value - in this case, 6 digits including 3 after the decimal place.

Answer 2

Are you sure you have to cast it in your query? When you select the result, what's next in line to process it? If it's an application, have the code properly determine and cast the type (if needed). If it's another query, you may be able to pass it along without any issues.

Answer 3

In addition to @David M's answer, here's a way to find the type directly, found here: less than dot: How to implement a typeof operator in SQL by using sql_variant_property:

select
    CAST(SQL_VARIANT_PROPERTY(123.866, 'BaseType') AS VARCHAR(20)) AS Type,
    CAST(SQL_VARIANT_PROPERTY(123.866, 'Precision') AS INT) AS Precision,
    CAST(SQL_VARIANT_PROPERTY(123.866, 'Scale') AS INT) AS Scale

Which gives me this answer:

Type     Precision  Type
numeric  6          3

Note that I tested this on SQL Server 2008, not 2005. I hope that function is available in 2005.

numeric is further described here: Data Types (Transact SQL), and here: decimal and numeric (Transact-SQL).

Answer 4

In your test, you are using union all. Did you know that all values will be cast to the same data type in a union query?

SQL Server Precision and Scale problems

This article also demonstrates what happens when you combine various data types together.