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

1 Answer 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.