I selected an identity field as a primary key in a SQL Server table. When I delete the last record, then enter a new record, SQL Server creates a new number. However, when I insert a new record, I want SQL Server to check the maximum number in the auto- increment field, then assign the next consecutive number to prevent a gap. I think I can create a trigger that ensures an uninterrupted series of numbers. Is this solution appropriate?
You can create an INSTEAD OF trigger in SQL Server 2000 to close the number gaps. However, unless you have a compelling business case to use consecutive numbers, you should reevaluate this need. You're likely to suffer scalability and locking problems if you don't allow gaps in your incrementing counters.