SQL Server 2005中“select 123.866”的数据类型是什么？

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:

最初,我想要做的就是避免编写演员声明,假设123.866隐式为小数。所以我想测试这两个语句是否相同:

select cast(123.866 as decimal) 
select 123.866

4 个解决方案

#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.

它隐式输入为十进制(6,3) - 强制转换为此值,您将看到二进制值匹配。它似乎在最小的tpe使用十进制来拟合值 - 在这种情况下,6位数包括小数点后的3位数。

#2

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:

除了@David M的答案之外,这里有一种直接找到类型的方法,在这里找到:less than dot:如何使用sql_variant_property在SQL中实现typeof运算符:

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.

请注意,我在SQL Server 2008上测试了这个,而不是2005年。我希望该功能在2005年可用。

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

这里进一步描述了数字:数据类型(Transact SQL),以及:decimal和numeric(Transact-SQL)。

#3

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?

在您的测试中,您正在使用union all。您是否知道所有值都将转换为联合查询中的相同数据类型?

SQL Server Precision and Scale problems

SQL Server精度和规模问题

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

本文还演示了将各种数据类型组合在一起时会发生什么。

#4

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.

你确定要在查询中加注它吗?当您选择结果时,接下来要处理的是什么?如果它是一个应用程序,请让代码正确确定并转换类型(如果需要)。如果是另一个查询,您可以将其传递而不会出现任何问题。

#1

#2