Solved

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

Posted on 2014-02-09
5
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:
Scott Pletcher 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

733 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