views:

437

answers:

3

Does anyone know of any work around by which i can save unsigned integers (0 to 4294967295) simply using 4 bytes instead of using 8 bytes and bigint?

I know we can create user defined datatypes and create a constraint on them to not allow negative values but that still does not allow me to enter values over 2147483647. I only want to use 4 bytes but be able to save integer values greater than 2147483647 but less than 4294967295.

Possible duplicate: http://stackoverflow.com/questions/306291/4-byte-unsigned-int-in-ms-sql

+2  A: 

There is no unsigned type available to you, so you could create one using the UDT, or opt for the larger data type. If you do it in a UDT you are going to exceed the 4 bytes again.

The extreme hack would be to apply an offset automatically to your stored value after you read it, by adding -2^31 but this is a real hacky way to go about it and confusing for anyone viewing the code etc, not to mention the potential for mistakes / things being missed. I wouldn't recommend the hack at all.

Andrew
A: 

This solution may be slightly confusing but will work for your purposes. Storing values bigger than 2^31-1 as integers causes overflow and appears as negative number. You can still store your values as integers and reinterpret them in code.

whatnick
+1  A: 

Use a Binary(4). Wrap it in a UDT if you want.

RBarryYoung
This actually uses 6 bytes: the 4 data bytes + 2 bytes overhead for the binary data type.
Jeff Meatball Yang