Linq and Sql Server Timestamp Columns – Why Binary?

As explained a bazillion times before on the net, the timestamp column in sql server is not a datetime or anything to do with time.  It is a binary(8) column which contains a big number which is incremented on every edit of any row of any table in the database. It is also identical to the rowversion column.

Now for my gripe. I’ve used strongly typed datatables in past projects and with some manipulation, you can get the datatable to treat the timestamp column as a bitint (Int64). Why does this matter? Because I’ve used it for some simple version tracking in the past, and this requires I perform range checking on these versions. A simplified explanation:

-Use the largest timestamp in my cached table
-Query the database table for any timestamps larger than this one
-Retrieve these new or updated items to be merged back into the original dataset

This normally is not useful for 2 or N tier apps, but in my instance I am transferring data over the net via a webservice so would like to only retrieve the new or changed records to the client.

Gripe time: why are these columns Binary? The only way I was historically able to query on them is to force sql server to cast them as a bigint, through some magic in the typed datatable. But now Im using Linq as my data access layer. I love linq so far, but I have tried every trick I can think of and it will not make this work- Linq will always fail with a illegal cast type error when it attempts to load the record.

So, whyt exactly are these binary? Wouldnt it be much easier to make use of rowversion and timestamps if they were represented as a real datatype like int64/bigint? I know I wouldnt have wasted half a day yesterday on this if this were the case.

So now it looks like Ill switch to using a datetime column that I hope is udpated with every edit or insert. Should work ok, but not my preference.


4 Responses to “Linq and Sql Server Timestamp Columns – Why Binary?”

  1. John Says:

    A Binary is a immutable range of bytes. It uses defensive copying meaning that ToArray will return a copy of the internal array of bytes. Be mindful when working with large binaries as they end up with a significant copy overhead.

    Timestamps are small, so you can ignore the copy overhead albeit be it unnecessary.

    The ToString method of a Binary is the fastest way to actually serialize and deserialize a Linq.Binary. But for some strange reason the Binary is surrounded by double quotes. Which is very irritating.

    Non the less if you substring the result you can use that to pass around a string representation of the binary and deserialize the Linq binary through:

    string s = “\”AAAAAAAAD6E=\””;
    int less = s.Length – 2;
    Convert.FromBase64CharArray( s.ToCharArray( 1, less ), 0, less );

  2. bradymoritz Says:

    John, thanks for the note. I’m actually able to query and use the binary version of the timestamp data, however when I want to linq query the database based on a range of timestamps, such as this pseudocode:

    “select all records with timestamp > MyOldtimestamp”

    Linq doesnt allow this comparison on the timestamp-typed column. (This query is useful becaues it would find all rows that have been updated or created since the timestamp I currently have)
    This is the reason I’m trying to fool linq into thinking this column is really a different data type in the database (via casting or cacluated columns etc), so that this query would then be allowed (ie, if it could cast it to int64, which I managed to get an old-school datatable to do in the past).

    An interesting query you can do directly in sql is this:

    Select MyTimestamp + 0 from MyTable

    This query will actually return a long int/int64 version of the timestamp!

    Thus all this still makes me wonder, why not just use int64’s natively in the db since this would store the exact same data, in the exact same # of bytes, but in a format that could be queried without all these hoops etc. The only reason I can think of is to make it difficult on purpose to discourage it from being over-used for all sorts of unintended purposes. That, or leave it binary so it could potentially grow to Binary(16) or larger down the road without breaking stuff 😉

  3. Diego Says:

    I just posted a blog entry about this topic with a solution 🙂

  4. paintball barrel Says:

    thanks, curious if you have any updates.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: