Consider the following:

declare @dt datetime, @dt2 datetime2, @d date
set @dt  = '2013-01-01'
set @dt2 = '2013-01-01'
set @d   = '2013-01-01'

select convert(varbinary, @dt) as dt,
       convert(varbinary, @dt2) as dt2,
       convert(varbinary, @d) as d


dt                    dt2                     d
------------------    --------------------    --------
0x0000A13900000000    0x07000000000094360B    0x94360B

Now, I already understand from the documentation that datetime has a smaller range, and starts from 1753-01-01, while datetime2 and date use 0001-01-01 as their start date.

What I don't understand though, is that datetime appears to be little-endian while datetime2 and date are big-endian. If that's the case, how can they even be properly sortable?

Consider if I want to know how many integer days are represented by a date type. You would think you could do this:

declare @d date
set @d = '0001-01-31'
select cast(convert(varbinary, @d) as int)

But due to the endianness, you get 1966080 days!

To get the correct result of 30 days, you have to reverse it:

select cast(convert(varbinary,reverse(convert(varbinary, @d))) as int)

Or, of course you can do this:

select datediff(d,'0001-01-01', @d)

But that means internally somewhere it is reversing the bytes anyway.

So why did they switch endianness?

I only care because I'm working on a custom UDT in SQLCLR and the binary order of the bytes does seem to matter there, but these built-in types seem much more flexible. Does SQL Server have something internal where each type gets to provide it's own sorting algorithm? And if so, is there a way I can tap into that for my custom UDT?

See also, a related (but different) question on StackOverflow.

  • Have you tried implementing IComparable? You shouldn't need to dig into the internal representation of the data types, ever. – Jon Seigel Jul 26 '13 at 23:05
  • According to this (scroll down to "Implementing a UDT with a User-Defined Format"), you can implement IComparable, but it is only used client-side. SQL Server ignores it and goes off the byte order. – Matt Johnson-Pint Jul 26 '13 at 23:08
  • Oh. Well that's annoying. – Jon Seigel Jul 26 '13 at 23:11
  • @PaulWhite - That is indeed useful. At least it's confirmation of what I am experiencing. Thanks! – Matt Johnson-Pint Jul 28 '13 at 17:41
  • @PaulWhite - The part he doesn't address in that article is how to remove the leading byte for the null. Why should an int need to be stored in 5 bytes? – Matt Johnson-Pint Jul 28 '13 at 17:46

SQL Server does not rely on the binary order for its "own" data types. For CLR datatypes you could use the iComparable interface, but as @MattJohnson mentioned, SQL Server ignores it:


Microsoft does not publish the details about how the different datatypes are stored and worked with. However Books Online explicitly states that you cannot rely on a specific binary format for a specific datatype and that the format they use might change any time. So it is a good idea to store an INT as just that and not as VARBINARY, because you might not be able to read your data anymore after the next SP.

As for the sorting: Most of the SQL Server core is written in C++. I assume internally a method similar to an iComparable is used. But again, there is no publicly accessible documentation about this available. Even if it were, you probably would not be able to exploit it because of the inherent differences between .NET and C++.

| improve this answer | |
  • The other issue number mentioned there was also informative. But do you have any details regarding how the internal types do it? – Matt Johnson-Pint Jul 27 '13 at 17:24
  • @MattJohnson, see my update above. I am afraid it is not what you were looking for... – Sebastian Meine Jul 28 '13 at 10:54
  • So, you're recommending using a SQL int as the backing field of my CLR UDT? Do you have an example of how to do that? The CREATE TYPE statement will take a base_type, or an external assembly - but not both. – Matt Johnson-Pint Jul 28 '13 at 17:44
  • No, that was just an example. What I am saying is that you need to find a way to serialize you UDT so that it can be binary-sorted, as there is no current way to implement an iComparable interface (or similar) and have SQL Server use it. – Sebastian Meine Jul 28 '13 at 22:07

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.