How to reordering or removing one identity number

• azdeji

  SSCrazy

  Points: 2535

  More actions

  August 29, 2013 at 12:21 am

  #277442

  Hi,

  I have set the identity columns to -1,1 but I want remove the number zero from the identity sequence example below.

  Currently --

  Identity column

  -1

  0

  1

  2

  3

  4

  And so on.

  What I want is --

  -1

  1

  2

  3

  4

  5

  And so on.

  Basically removing the the first zero. Is it possible?

  I don't mind if I have to truncate the table.

  Thanks

• MD21

  SSC Rookie

  Points: 46

  More actions

  August 29, 2013 at 1:42 am

  #1645864

  Hope this helps.

  create table tblname (id int identity(-1,1),name varchar(2))

  DECLARE @MAXID INT

  SELECT @MAXID = MAX(id) FROM tblname

  --if @MAXID + increment value = 0

  if(@MAXID+1=0)

  BEGIN

  SET identity_insert tblname ON

  insert into tblname(id,name) values('1','d')

  SET identity_insert tblname OFF

  END

  ELSE

  BEGIN

  insert into tblname values('d')

  END

  SELECT * FROM tblname

• baabhu

  SSCertifiable

  Points: 6205

  More actions

  August 29, 2013 at 2:03 am

  #1645869

  azdeji (8/29/2013)

  ---

  Hi,

  I have set the identity columns to -1,1 but I want remove the number zero from the identity sequence example below.

  Currently --

  Identity column

  -1

  0

  1

  2

  3

  4

  And so on.

  What I want is --

  -1

  1

  2

  3

  4

  5

  And so on.

  Basically removing the the first zero. Is it possible?

  I don't mind if I have to truncate the table.

  Thanks

  You can do this.

  You can delete the row from the table and reinsert only that row if that order is not important for you. Else you can truncate and reinsert the data.

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  August 29, 2013 at 2:10 am

  #1645872

  baabhu (8/29/2013)

  ---

  Else you can truncate and reinsert the data.

  A truncate will reset the IDENTITY values to the initial settings. Reinserting the data after a truncate will again use the 0 value for the identity column at the second insert.

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• azdeji

  SSCrazy

  Points: 2535

  More actions

  August 29, 2013 at 2:13 am

  #1645876

  Thanks the attempt but it still showing the zero instead of -1 then 1.

  idname

  -1d

  0e

  1f

  2g

  It should be –

  idname

  -1d

  1e

  2f

  3g

• HanShi

  SSC-Dedicated

  Points: 33507

  More actions

  August 29, 2013 at 2:18 am

  #1645879

  If you have good control over the insert statements you could issue a RESEED command after the first insert.

  DBCC CHECKIDENT ("{tablename}", RESEED, 0);

  Or create a table with IDENTITY(1,1) and use the "SET IDENTITY_INSERT {tablename} ON" statement with the first insert to enter the value of -1.

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• ScottPletcher

  SSC Guru

  Points: 101119

  More actions

  August 29, 2013 at 3:19 pm

  #1646155

  Is it possible?

  No.

  You can't make an IDENTITY skip a specific value(s).

  If you start at -1 and increment by 1, there is always the possibility you will get a 0 in the values inserted into the table.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".