3

I just found that on the SQL Server 2012 MSDN documentation for binary and varbinary types, they mention the following at the end of the article:

Conversions between any data type and the binary data types are not guaranteed to be the same between versions of SQL Server.

I noticed that this does not exist in the previous versions of the article for 2005, 2008, and 2008 R2. I searched around online but didn't find any exact explanations that satisfied my question of "WHY?". (Which is the question I have here.)

The project I am working on needs to store data as varbinary(max), but based on this little note, I am afraid if I need to move the binary data and then convert it on another SQL Server version that it will not match the original data converted to binary, so answering this question more in depth would be a great help.

Thanks!

|improve this question
  • 1
    If you need to convert the data, why is it being stored in a varbinary(MAX) column? Can you describe more about your process, as I'm not sure it's clear from the question. – Jon Seigel Aug 12 '13 at 21:45
  • @JonSeigel Great question! I should have clarified that I am running into an existing design, not something that I have put in place. The field itself is storing some document data :(. – ConstantineK Mar 21 '14 at 16:55
5

Microsoft reserves the right to change the binary representation of the build-in data types like DATETIME. So if you do an

INSERT dbo.table(binaryColumn)VALUES(CAST(GETDATE() AS VARBINARY(8)));

and then you upgrade SQL Server or even do an SP apply

SELECT CAST(binaryColumn AS DATETIME) FROM dbo.table;

might get you a different date then the one originally stored.

However, if you store you own binary data that was generated outside of SQL Server (e.g. a pdf file), that data will not be affected by such a change.

This by the way is not new. Microsoft always said that they might change the binary representation at any time. They might just have made it more obvious in the new BOL version.

That all being said, it is still very unlikely that Microsoft is going to change the binary representation of say an integer. However, something like a geospatial, which is implemented as a CLR type, is very likely to change between versions.

As per Paul's comment: SQL Server does indeed guarantee that you can convert a build-in data type to binary and back within the same statement. To extend that to the same session when storing the intermediate value in tempdb however I would already consider risky - a very small risk, but a risk. Storing the intermediate value in a real table to pick it back up at a later time is a definite no-no.

|improve this answer
  • @paulwhite, what do you mean with "roundtrip conversion"? – Sebastian Meine Aug 13 '13 at 11:47
  • 1
    @paulwhite, within the same session - yes. But not if there is a service pack apply in between. – Sebastian Meine Aug 13 '13 at 13:57
  • 1
    To be clear, I mean conversion to binary and back within the same statement. My point was that the only guarantees here are very weak ones indeed. I just thought you might want to include that in your answer. – Paul White says GoFundMonica♦ Aug 13 '13 at 14:15
  • Thanks for the interesting answer. I thought it might be something that affected all versions but wasnt documented in the BOL. – ConstantineK Aug 14 '13 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.