Menu
I've got a strange situation with some tables in my database starting its IDs from 0, even though TABLE CREATE has IDENTITY(1,1). This is so for some tables, but not for others. It has worked until today.
I've tried resetting identity column:
DBCC CHECKIDENT (SyncSession, reseed, 0); But new records start with 0. I have tried doing this for all tables, but some still start from 0 and some from 1.
Any pointers?
(i'm using SQL Server Express 2005 with Advanced Services)
288
Bookmark this question. Show activity on this post.
Step 1 : Create a table named school. CREATE TABLE school ( student_id INT IDENTITY, student_name VARCHAR(200), marks INT ); Here, the 'student_id' column of the table starts from 1 as the default value of seed is 1 and each row is incremented by 1. Step 2 : Insert some value into a table.
You cannot alter a column to be an IDENTITY column. What you'll need to do is create a new column which is defined as an IDENTITY from the get-go, then drop the old column, and rename the new one to the old name.
From DBCC CHECKIDENT
DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) If no rows have been inserted to the table since it was created, or all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value.
So, this is expected for an empty or truncated table.
73
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
