Solved

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

Posted on 2014-02-09
5
384 Views
Last Modified: 2014-02-10
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
Comment
Question by:codequest
5 Comments
 
LVL 21

Assisted Solution

by:Dale Burrell
Dale Burrell earned 150 total points
ID: 39846500
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
 
LVL 2

Author Comment

by:codequest
ID: 39847139
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 300 total points
ID: 39848085
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 39849089
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
 
LVL 2

Author Comment

by:codequest
ID: 39849326
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now