backend - SQL Server: Will my IDENTITY primary key run out in a large location tracking table? - Stack Overflow

admin管理员组

文章数量:1344946

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 grow significantly.

My concerns:

• Will the id INT IDENTITY(1,1) run out of values? Should I switch to BIGINT?

• Are there performance risks with frequent inserts? Would indexing or partitioning help?

• What are the best practices for managing large location tracking data in SQL Server?

Any insights on database optimization and long-term storage solutions would be appreciated.

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 grow significantly.

My concerns:

• Will the id INT IDENTITY(1,1) run out of values? Should I switch to BIGINT?

• Are there performance risks with frequent inserts? Would indexing or partitioning help?

• What are the best practices for managing large location tracking data in SQL Server?

Any insights on database optimization and long-term storage solutions would be appreciated.

• location
• backend
• ssms
• tracking

Share Improve this question Follow edited 12 hours ago marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked 13 hours ago Ganesh MohaneGanesh Mohane 3144 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

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.

本文标签： backendSQL Server Will my IDENTITY primary key run out in a large location tracking tableStack Overflow