Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I need to store ulong values as a primary key in SQL Server. Since SQL Server doesn't have a ulong datatype (or for that matter anything unsigned), the only way to go is to have column as varbinary(8).

Now, having a varbinary as a PK doesn't sound like a good idea. So what are the alternatives to store ulong values?

share|improve this question
3  
Why ulong? Do you expect to exceed the 9,223,372,036,854,775,807 that can be stored as bigint? Are these surrogate key values? If so how are they generated? – Martin Smith Feb 3 at 17:11
2  
In theory they could - sounds like you should get confirmation on specifics rather than design a system based around "in theory"... IMHO bigint should be more than enough for anything practical. – Aaron Bertrand Feb 3 at 17:30
2  
Well I'm not sure what other workarounds you're expecting. SQL Server doesn't have a ulong type, so you're going to need to pick a data type SQL Server does has, and deal with its limitations for your purpose, or choose an RDBMS that has ulong. – Aaron Bertrand Feb 3 at 18:30
2  
@billinkc - SQL Server can seek on varbinary data fine. Of course have to make sure that you are seeking for the right thing, If the number is stored as 0x01 then seeking on 0x0000000000000001 won't return anything. Which means the PK could actually contain semantic duplicates when converted back to ulong – Martin Smith Feb 3 at 19:16
2  
So... if I'm understanding this conversation right, the full range of a 64-bit value is enough, but half of that isn't? That's pretty gutsy for estimation accuracy, IMO. In that position, I would probably abandon any 64-bit type on the grounds that it simply isn't going to be safe enough to scale. Is the ulong code already set in stone, or is it possible to use something else? – Jon Seigel Feb 3 at 23:44
show 12 more comments

1 Answer

up vote 1 down vote accepted

I'm assuming by ulong that you mean a 64-bit unsigned integer.

Your best option if you truly need to span the entire range of a ulong is to use NUMERIC(20,0) or larger. Alternately, you could use VARBINARY(8), but your application would have to be base-aware.

share|improve this answer
I don't think it's appropriate to answer yet, given the activity in comments. Didn't you see the high rep users and mods commenting? – Phil Feb 3 at 23:49
@Phil I don't see what that has to do with the validity of the answer. The comments are directed towards the reasonableness of needing a 64-bit unsigned value, which is tangental to the question of how best to represent one for a primary key. I would agree with them that an unpredictable UInt64 indicated questionable system design, but DBAs don't always get to decide these things. It's possible the system was originally developed against Oracle, where NUMBER(20,0) is not even that unusual. – Bacon Bits Feb 4 at 0:00
2  
Ok, maybe it's just the way I work on this site. I wouldn't answer a question if there was a dialogue going on. Wouldn't want to appear rude. I'm rude enough as it is :) – Phil Feb 4 at 0:02
Numeric(20,0) is a workable solution, with the hassles that I mentioned in my comments. Not sure why anyone would down vote this. @Phil: You made my smile! – Mrchief Feb 4 at 5:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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