June 15, 2009 at 11:06 am
I have an identity integer column to one of my table and its increament value is 1. On inserting a row to this table, if any errors occur like primary key exception or invalidcast, no row is inserted into the record but the identity wil be incremented. On inserting another row the identity column skips one no... Can this be changed.
June 15, 2009 at 11:11 am
This is the default behaviour of identity column. You can force a specific value in identity column by setting identity_insert property to ON.
Other way to achieve this is by dbcc checkident command.
June 15, 2009 at 12:09 pm
No. It can't be changed.
If you need perfectly synchronized incrementally increasing numbers, you should come up with a mechanism to create them. That's not the purpose of the Identity column. It will have gaps. You can try filling them in by inserting manually, but that's a lot of work. Better to simply build a different incremental mechanism if the gaps are going to cause you problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 15, 2009 at 12:18 pm
You can handle this by a view and a ROW_NUMBER. A IDENTITY is not a sequence.
Here a little sample
USE tempdb
GO
CREATE TABLE TestSequence
(
Id INT NOT NULL IDENTITY
PRIMARY KEY CLUSTERED,
SomeInt INT
)
GO
CREATE VIEW vw_TestSequence
AS
SELECT
Id,
SomeInt,
ROW_NUMBER() OVER (ORDER BY Id) Sequence
FROM TestSequence
GO
INSERT INTO vw_TestSequence (SomeInt)
SELECT 1
UNION ALL SELECT 5
UNION ALL SELECT 10
DELETE FROM vw_TestSequence WHERE SomeInt = 5
INSERT INTO TestSequence (SomeInt)
SELECT 9
SELECT * FROM vw_TestSequence
GO
DROP VIEW vw_TestSequence
DROP TABLE TestSequence
Flo
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply