SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2010
    Posts
    193
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    how high will a sql srv primary key id go

    I have a ms sql table with 3 columns: id (pk), sku and quantity.

    Once a day this table is truncated, then populated with new data. Every time new data goes in, the auto increment id never starts at 1, it keeps incrementing, starting at the highest + 1 pre-truncated id value.

    My question is, will it ever stop incrementing, causing insert of new data to stop?

  2. #2
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,258
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    If the id (pk) is an int, then it will continue incrementing until 2^32 / 2 - 1, which is 2,147,483,647.
    If the id is a bigint, then 2^64 / 2 - 1, which is 9,223,372,036,854,775,807.
    And if either the int or bigint is unsigned, then you get double those numbers.

    (For some perspective, in order to reach that second number, you'd have to accumulate more than 600 million records every year for as long as the universe has existed.)
    "First make it work. Then make it better."

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2010
    Posts
    193
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    ok, thanks. yea it's an int. I'm good for a while, every day the tables gets about 3000 new rows

  4. #4
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,258
    Mentioned
    18 Post(s)
    Tagged
    0 Thread(s)
    In that case, it'll be a couple thousand years before you run out of PKs.
    "First make it work. Then make it better."

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,241
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    And if either the int or bigint is unsigned, then you get double those numbers.
    nice feature, but it doesn't exist in microsoft sql server
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •