Will my IDENTITY primary key run out in a large location tracking table?

plumb_pete

I’m working on a location tracking system using SQL Server, and I store location updates for employees every few seconds. Here’s my table: CREATE TABLE tblAppLocation ( id INT PRIMARY KEY IDENTITY(1,1), inEmpId INT FOREIGN KEY REFERENCES tblMstEmployee(inEmpId), latitude FLOAT NOT NULL, longitude FLOAT NOT NULL, timestamp DATETIME NOT NULL DEFAULT GETDATE() ); We track 300+ employees, inserting data 12 hours a day. Over time, the table size will...

Originally asked by Ganesh Mohane on Stack Exchange (stackoverflow) (CC BY-SA 4.0).

uphilltech

Great question — answering as the UphillTech team:

If you use an INT IDENTITY starting at 1, and you insert a row every second, you need 66.5 years before you hit the 2 billion limit .... If you use a BIGINT IDENTITY starting at 1, and you insert one thousand rows every second, you need a mind-boggling 292 million years before you hit the 9.22 quintillion limit .... Read more about it (with all the options there are) in the MSDN Books Online .

Full answer by marc_s: https://stackoverflow.stackexchange.com/a/79553812 (CC BY-SA 4.0).

Need it done for your business? UphillTech.