A value of Decimal(4,0) requires 5 bytes to store in SQL Server. This is according to the documentation that says all decimal and numeric types with precision 1-9 take 5 bytes. Also the DATALENGTH function confirms:
select DATALENGTH(convert(Decimal(4,0),256)) result result ----------- 5 (1 row(s) affected) However, when I convert to binary(5) and then back to decimal(4,0) it truncates data.
DECLARE @myval decimal (4, 0); SET @myval = 257; SELECT CONVERT(decimal(4,0), CONVERT(varbinary(5), @myval)) result result --------------------------------------- 1 (1 row(s) affected) The last byte of the number is chopped off. However, if I convert to binary(6) or more... I get the correct results:
DECLARE @myval decimal (4, 0); SET @myval = 257; SELECT CONVERT(decimal(4,0), CONVERT(binary(6), @myval)) result result --------------------------------------- 257 (1 row(s) affected) What is happening? If I need to store a binary representation of a decimal value, how do I know how many bytes will be needed? Specifically, what is the generic formula to determine smallest number x of bytes required to convert decimal(p,s) to binary(x)?
I need to do some binary data marshaling into a service broker message so I need to convert some data of various types to a binary string. Is there a more robust way to store decimal values in binary than using cast/convert?
5 Answers
Answers 1
Let's start from DATALENGTH. From MSDN:
DATALENGTH is especially useful with varchar, varbinary, text, image, nvarchar, and ntext data types because these data types can store variable-length data.
The minimum length of decimal is 5 bytes, maximum - 17 bytes. Decimal(p,s) is NOT variable-length data. It has fixed length according to precision. For example if length of numeric is from 1 to 9 number then DATALENGTH will always return 5
select DATALENGTH(convert(Decimal(38,0), 1)) -- result 5 select DATALENGTH(convert(Decimal(38,0), 1234567890)) -- result 5 if length of numeric is from 10 to 19 number then DATALENGTH will always return 9
select DATALENGTH(convert(Decimal(38,0), 12345678901)) -- result 9 select DATALENGTH(convert(Decimal(38,0), 111111111111111)) -- result 9 so, result of DATALENGTH will depend of numeric's lenght but it is not real lenght.
When you convert decimal(4,0) to binary(5) you will get 0x04 00 00 01 00 In this case, only the last byte is left for your number. The maximum number that you can store in 1 byte is 255 (255 in HEX equals FF)
In that way everything works fine:
DECLARE @myval decimal (4, 0); SET @myval = 255; SELECT CONVERT(decimal(4,0), CONVERT(binary(5), @myval)) result, CONVERT(binary(5), @myval) result HEX --------------------------------------- ------------ 255 0x04000001FF Now, try number 256 instead of 255. 256 in HEX equals 100, we can't store 100 in 1 byte (HEX should be 0x04 00 00 01 00 1 but there is not room for 1)
DECLARE @myval decimal (4, 0); SET @myval = 256; SELECT CONVERT(decimal(4,0), CONVERT(binary(5), @myval)) result, CONVERT(binary(5), @myval) HEX result HEX --------------------------------------- ------------ 0 0x0400000100 If you want to store numbers from 0 to 9999 then you need at least 6 bytes. Look at 257 (in HEX equals 101)
DECLARE @myval decimal (4, 0); SET @myval = 257; SELECT CONVERT(decimal(4,0), CONVERT(binary(6), @myval)) result, CONVERT(binary(6), @myval) HEX result HEX --------------------------------------- -------------- 256 0x040000010101 here we have 6 bytes 0x04 00 00 01 01 01 and 01 01 in the end Then 9999 (HEX equals 270F)
DECLARE @myval decimal (4, 0); SET @myval = 9999; SELECT CONVERT(decimal(4,0), CONVERT(binary(6), @myval)) result, CONVERT(binary(6), @myval) HEX result HEX --------------------------------------- -------------- 9999 0x040000010F27 6 bytes 0x04 00 00 01 0F 27 and 27 0F in the end. (read from right to left)
Hope it will help
Answers 2
The documentation states that it uses 5 bytes of storage. It does not state that it takes 5 bytes when converting it to varbinary(N) using CAST or CONVERT. Infact, the documentation warns you NOT to cast numeric types to/from binary with the following note:
Do not try to construct binary values and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between versions of SQL Server.
I suspect that when converting a numeric to varbinary it is including precision and scale information, in addition to the data bits. When stored in a table, this information is defined by the schema so it doesn't need to store the precision and scale for each value, only the data bits.
Answers 3
Binary datatype in SQL obviously uses an encoding scheme which depends upon the source datatype. Try running the following and see what results you get:
SELECT CAST(256 AS Binary(8)), CAST(CAST(256 as decimal(4,0)) AS Binary(8)), CAST(CAST(256 as decimal(9,6)) AS Binary(8)), CAST(CAST(256 as float(2)) AS Binary(8)) Note that you get the following results:
0x0000000000000100 0x0400000100010000 0x090600010040420F 0x0000000043800000 This shows that when a decimal is converted to a Binary, the binary contains encoding showing how long the decimal is and how many decimal places. You can also see that you get a different binary representation depending upon these. It is also clear that integer and floats are encoded differently. I think this explains why you are getting truncation.
Not quite an answer to why but if you can change your table then you could specify the column as a sql_variant:
declare @test sql_variant SET @test = 'I''m a string' SELECT @test As Col, SQL_VARIANT_PROPERTY(@test,'BaseType') AS 'Base Type', SQL_VARIANT_PROPERTY(@test,'Precision') AS 'Precision', SQL_VARIANT_PROPERTY(@test,'Scale') AS 'Scale' Result
Col Base Type Precision Scale I'm a string varchar 0 0 Now try with a decmal:
SET @test = 1.2 SELECT @test As Col, SQL_VARIANT_PROPERTY(@test,'BaseType') AS 'Base Type', SQL_VARIANT_PROPERTY(@test,'Precision') AS 'Precision', SQL_VARIANT_PROPERTY(@test,'Scale') AS 'Scale' Result
Col Base Type Precision Scale 1.2 numeric 2 1 Answers 4
when you cast to varbinary you not only dump value (which can take up to 5 bytes in your case) but also information about type hence you need extra byte to fit everything in your case.
Consider that if you switch the source data type from decimal (4,0) to decimal (5,0) you will get different varbinary result.
All in all I recommend to not play with varbinary :)
Answers 5
You should not rely on a bit pattern unless you can control its format!
If I understand you correctly, you want to transfer large amount of data and you want to do this as fast and secure as possible.
The only way I know to get a reliable bit pattern is a defined conversion to string.
FOR XML (and starting with 2016 FOR JSON) uses well defined string formats to guarantee type safety.
The following example will create an XML with various types, cast this to NVARCHAR(MAX) and cast this intermediate string to a binary. This binary is plain unicode and will be interpreted by (almost) any system correctly.
Just to demonstrate, that this bit pattern is safely re-casteable and re-readable into typed results I show the reading process too:
DECLARE @Source NVARCHAR(MAX)= CAST( ( SELECT 'Some varchar string' AS SimpleString ,N'Some nvarchar string with foreign characters: слов в тексте' AS WideString ,CAST(100.0/3.0 AS DECIMAL(10,4)) AS Decimal_10_4 ,CAST(100.0/3.0 AS FLOAT(12)) AS Float_12 ,CAST(100.0/3.0 AS FLOAT(53)) AS Float_53 ,GETDATE() AS SimpleDateTime ,CAST(GETDATE() AS DATETIME2) AS ExtDateTime FOR XML RAW,ELEMENTS ) AS NVARCHAR(MAX)); SELECT @Source; --this is the intermediate XML
/* <row> <SimpleString>Some varchar string</SimpleString> <WideString>Some nvarchar string with foreign characters: слов в тексте</WideString> <Decimal_10_4>33.3333</Decimal_10_4> <Float_12>3.3333332e+001</Float_12> <Float_53>3.333333300000000e+001</Float_53> <SimpleDateTime>2017-03-20T09:28:10.873</SimpleDateTime> <ExtDateTime>2017-03-20T09:28:10.8730000</ExtDateTime> </row> */ --Now we can cast this to VARBINARY in order to send it somewhere
DECLARE @binaryToMarshal VARBINARY(MAX)=CAST(@Source AS VARBINARY(MAX)); --The rest is the type-safe! reading side:
DECLARE @Target NVARCHAR(MAX)=CAST(@binaryToMarshal AS NVARCHAR(MAX)); DECLARE @ReCastes XML=CAST(@Target AS XML); SELECT @ReCastes.value('(/row/SimpleString)[1]','varchar(max)') AS SimpleString ,@ReCastes.value('(/row/WideString)[1]','nvarchar(max)') AS WideString ,@ReCastes.value('(/row/Decimal_10_4)[1]','decimal(10,4)') AS Decimal_10_4 ,@ReCastes.value('(/row/Float_12)[1]','float(12)') AS Float_12 ,@ReCastes.value('(/row/Float_53)[1]','float(53)') AS Float_53 ,@ReCastes.value('(/row/SimpleDateTime)[1]','datetime') AS SimpleDateTime ,@ReCastes.value('(/row/ExtDateTime)[1]','datetime2') AS ExtDateTime Hint 1
You might use FOR XML RAW,ELEMENTS,XMLSCHEMA to create an inline schema. This schema allows the target system, to know all needed details for a typed reading.
Hint 2
As you want to save bytes, you might use minimal element names and JSON.
And - if you do not include special characters - you might send only half the size by using CAST ... AS VARCHAR(MAX). In this case you do not send utf-16 but 1-byte-codes *(extended ASCII)* which should be readable on any system unless you do not need any code page, collation, whatever...
0 comments:
Post a Comment