Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-02-09
5
Medium Priority
?
417 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 600 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 70

Accepted Solution

by:
Scott Pletcher earned 1200 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 200 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

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.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

618 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