0

Possible Duplicate:
SQL server identity column values start at 0 instead of 1

Inserted identity value starts from "0"

Here I'll create a database and create a table, now I'll try to delete the records in the empty table and reset the identity. When i insert the records now its starts from identity value "0". 

CREATE DATABASE test

GO

USE test

CREATE TABLE [dbo].[table1]
  (
      [Rollno] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](max) NOT NULL,
    CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED
    (
        [Rollno] ASC
    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  )ON [PRIMARY]

USE test

DELETE TABLE table1

DBCC CHECKIDENT('table1', RESEED, 0)

INSERT INTO table1 VALUES('Sachin')

SELECT * FROM table1

Could any one please help me to resolve this.

Community
  • 1
  • 1
Sanjaybxl
  • 63
  • 8
  • 1
    Resolve what? You didn't ask a question. What issue are you trying to solve? – Oded Feb 01 '12 at 12:59
  • Help you to resolve what? I'm not sure what the question is... You've said what you're trying to do, and you've posted some code, and then made a comment that seems to suggest it's been successful. What's the problem? – Anthony Grist Feb 01 '12 at 12:59
  • DBCC CHECKIDENT('table1', RESEED, 0) is correct. The next value is taken. Just tested. DELETE TABLE table1 is not valid syntax. – deadcrab Feb 01 '12 at 13:03

2 Answers2

0

Try using DBCC CHECKIDENT('table1', RESEED, 1)

The last argument is the next value to use for the identity column.

Marnix van Valen
  • 13,265
  • 4
  • 47
  • 74
0

You are reseeding the identity value to 0, so this is the expected behaviour.

devdigital
  • 34,151
  • 9
  • 98
  • 120