Post reply

How to reordering or removing one identity number

  • 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

  • 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

  • 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.

  • 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’! **

  • 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

  • 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’! **

  • 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply