Jeff Meatball Yang
Jeff Meatball Yang

Reputation: 39057

A 4-byte Unsigned Int for Sql Server 2008?

I understand there are multiple questions about this on SO, but I have yet to find a definitive answer of "yes, here's how..."

So here it is again: What are the possible ways to store an unsigned integer value (32-bit value or 32-bit bitmap) into a 4-byte field in SQL Server?

Here are ideas I have seen:

1) Use a -1*2^31 offset for all values

2) Use 4 tinyint fields

3) Use binary(4)

Upvotes: 1

Views: 2640

Answers (1)

Thomas
Thomas

Reputation: 64674

IMO, you have the correct answers to storing 2^32 positive values in 4 bytes: either a standard int and you do the math or a binary(4) which, contrary to what you have said, will only consume 4 bytes of space. (Only varbinary will incur an extra 2 bytes of storage). A series of tinyint or smallint columns would be unjustifiably cumbersome IMO.

Of course there is another solution for storing 2^32 positive values but it takes eight bytes: a bigint with a check constraint. Given how cheap storage and memory is today, IMO, this is the simplest and cheapest solution given the programmatic hoops you will have to jump through with the other solutions, however clearly you have a reason for wanting to save the extra 4 bytes on each row.

Upvotes: 2

Related Questions