June 28, 2016 at 4:22 pm
Hi All,
Running SQL Server 2012E
I have an table which I am loading with new data
I am entering value 0 and value 999999 two separate insert statements
Inserted about 5000 records incremented 1 -5000
then insert 999999
I run:
DBCC CHECKIDENT ('TableName', RESEED, 0);
message
Checking identity information: current identity value '1001001'. Current id would be 999999
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKIDENT ('TableName');
message
Checking identity information: current identity value '0', current column value '1001001'. Current id would be 999999
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKIDENT ( TableName, RESEED )
message
Checking identity information: current identity value '0', current column value '1001003'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
increments to next highest value current column value
DBCC CHECKIDENT ( TableName, NORESEED )
insert new value it does not insert
message
Checking identity information: current identity value '1', current column value '1001004'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I have records inserted in the first 5000 ID's.
I want to start at the 5001.
I thought if you reseed the value it will search for the first open spot etc.
OK! Doing a little more research.
My identity column is a primary key column.
So I guess my question is!
Is there a way to insert data into the column that has a higher value?
Example:
ID:
1
2
5
10
Would it be possible to RESEED at 1 and have SQL fill in
3
4
6
7
8
9
11
When I enter new data the next value is either 100000 0r 100999
I am trying to get start at identity of 1 "One"
Any ideas?
June 28, 2016 at 5:23 pm
If you want to have control over the values in your IDENTITY column, I suggest that you don't use an IDENTITY column, or that you consider using SET IDENTITY_INSERT ON to force in those values which you want.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 29, 2016 at 3:12 am
My question would be, why you want this behavior especially on the primary key which is having an Identity? Why you want to avoid gaps in a identity???
June 29, 2016 at 7:23 am
Take a look at using a Sequence object instead of an Identity property. You can control Sequence values very easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply