sqflt8，sqlmoney和其他本机SQL数据类型的二进制存储格式是什么? [英] What are the binary storage formats for sqflt8, sqlmoney and other native SQL data types?

问题描述

根据文档，可以使用以本机SQL Server数据格式设置的bcp格式导入或导出本机(二进制)数据.这些示例包括SQLFLT8，SQLFLT4，SQLMONEY或SQLNUMERIC.

According to the documentation, native (binary) data can be imported or exported with bcp formatted in the native SQL Server data formats. Examples of these are SQLFLT8, SQLFLT4, SQLMONEY or SQLNUMERIC.

是否有人知道各种类型的数据格式是什么，或者在哪里可以找到指定这些格式的文档.例如，SQLFLT8是存储为IEEE双精度数字还是其他某种格式?

Does anyone know either what the data formats for the various types are, or where documentation specifying these formats might be found. For example, is a SQLFLT8 stored as an IEEE double precision number or in some other format?

编辑:来自安德鲁带有定义的头文件.这带来了一个文件odbcdss.h; answer 我在下面发布的文件中有一些内容，看起来很有希望.

From the answers by kevchadders and Andrew I had a little epiphany did a little bit of googling for #define and typedef to see if I could find C header files with definitions. This came up with a file odbcdss.h; the answer I've posted below has some out-takes from the file, which looks quite promising.

推荐答案

我不确定该理论是否成立，但是可以使用一些SQL和一些方法来找出类型的内部存储.我对博客上的新datetime2/datetimeoffset进行了此操作，以特别获取内部二进制格式，因为我很想看看它们如何获得更高的准确性.

I'm not sure if the theory will hold, but finding out the internal storage of the types can be achieved using some SQL and a bit of figuring out. I did this for the new datetime2 / datetimeoffset on my blog to speifically get the internal binary format as I was interested to see how they got the additional accuracy.

以金钱为例

declare @test money
set @test = 12.34
select @test -- shows 12.34 as expected

declare @binaryValue binary(8)
set @binaryvalue = convert(binary(8),@test)
select @binaryvalue 

输出:0x000000000001E208

Output : 0x000000000001E208

当被认为是十进制数字时为123400，货币存储到4个小数位，因此将值指示为12.3400，从理论上反转该值，十六进制中仅1的值应为0.0001

That is 123400 when considered as a decimal number, money is stored to 4 decimal places so that would indicate 12.3400 as the value, reversing this in theory a value of just 1 in hex should be 0.0001

declare @test money
declare @binaryValue binary(8)
set @binaryvalue = 0x0000000000000001
set @test = convert(money,@binaryvalue)
select @test

输出0.0001

接下来我要检查的是负数

The next thing I would then check is the negative numbers,

declare @test money
set @test = -12.34
select @test -- shows -12.34 as expected

declare @binaryValue binary(8)
set @binaryvalue = convert(binary(8),@test)
select @binaryvalue 

输出:0xFFFFFFFFFFFEFE1DF8

Output : 0xFFFFFFFFFFFE1DF8

所以看起来它是一个带符号的8字节数字，因为它只是将数字从FF ...等带走了.快速检查-0.0001可以得到预期的所有0xFFF .... FFF，而-0.0002可以得到预期的全部0xFF .... FFE.

So that looks like it is a signed 8 byte number, since it has just take the number away from FF...etc. A quick check with -0.0001 gives out all 0xFFF....FFF as expected and -0.0002 gives 0xFF....FFE as expected.

我不确定这是否适用于BCP，但是作为一种内部存储格式，我会猜测一个带符号的8字节整数，该整数假定为小数点后4位.

Whether this holds for BCP I am not sure, but as an internal storage format I would take a guess at a signed 8 byte integer that has an assumed 4 decimal places.

这篇关于sqflt8，sqlmoney和其他本机SQL数据类型的二进制存储格式是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！