[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

Alternatives to bigint for large file primary keys, wanting to stay with int

My app, if successful, could conceivable have 100,000 users, who use it 10 times each, generating 1000 records each time, thus generating 1 billion int type primary keys.  So conceivably the app could eventually generate enough records to exceed the limit of the int type (2B+).

Another feature is that most of these records would be effectively archived within 90 days.

GUID is overkill for sure, so the alternative is bigint.

But since bigint does not appear to be natively handled in Javascript (though there are libraries for that purpose), I was wondering if there is some SQL strategy for handling these situation, so I could stay with int.

Or would that be more complicated that using the appropriate javascript tools.

Any suggestions about this would be appreciated.

Thanks!
0
codequest
Asked:
codequest
3 Solutions
 
Dale BurrellCommented:
By far the easiest solution is to use GUID - thats what they're for... there is no such thing as overkill IMO... I would never use anything other than GUID no matter the size of my DB.

However if you can't get your head around that :) you can use a multi-part primary key e.g. year, month, count or user, count - but I would never recommend it as it is going to cause complications at every point in your system. You'll have to default it yourself, and manage it in code yourself.
0
 
codequestAuthor Commented:
Thanks for the input.  I think you answered the question by saying multi-part key on top of int.  But it seems that dealing with bigint in javascript would be easier than that.  

But continuing the reasoning what about...

>  GUID taking way more disk space than bigint?

>  Some kind of awful partitioning of the DB to allow continued use of int?
0
 
Scott PletcherSenior DBACommented:
GUIDs are a royal pita to deal with, aside from causing extremely severe fragmenting in indexes. After someone's tried to read a GUID for you to type in for, say, the third time, you'll be kicking yourself for not finding some alternative -- any alternative -- to that.

You could use a combined ( tinyint, int ) key.  The tinyint would start at, and default to, 1.  IF the int value is ever about to overflow, you change the tinyint's default value to 2, and reset the int value to 1.  You can create code that runs every day, or on another applicable schedule, which checks for that condition and makes that change if needed.  [Since tinyint holds 255 different values, the combined tinyint, int gives you roughly 500B unique key values.]

There are other ways, too, of course.  

Btw, make sure you consider the proper clustered index separately from the primary key.  Often the PK is not the best choice for the clustered index.
0
 
Anthony PerkinsCommented:
eventually generate enough records to exceed the limit of the int type (2B+).
Actually it is 4B, all you need to do seed it to  -2,147,483,648.
0
 
codequestAuthor Commented:
I blew the original assumption.  Javascript can handle very large numbers, it just can't do math well in them.  So I can use bigint and long for my primary key.  

Suggestions were useful otherwise, points granted accordingly,
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now