SQL Server Performance

Create Primary Key Based on Identity Value

Discussion in 'SQL Server 2008 General Developer Questions' started by baburk, Dec 8, 2009.

  1. baburk New Member

    Consider I am having a table ([DBO].[WBCustomer]) with IDENTITY column ID.

    Truncate table [DBO].[WBCustomer]
    DBCC CHECKIDENT ('[DBO].[WBCustomer]', RESEED, 1)
    WHEN I give

    select IDENT_CURRENT('[DBO].[WBCustomer]')

    gives me 1

    After inserting a record, it gives me 1.

    Since I having another column PID which is primary key column in the table([DBO].[WBCustomer]). I have to insert like E1, E2

    Since both the time it returns the same value(i.e. after reseed and inserting a single record) primary key violation error occurred.

    Thanks.
  2. Adriaan New Member

    Seems like you're attempting to do autonumbering without gaps. DBCC CHECKIDENT is not something you want to be doing all the time, also seeing that it requires pretty elevated permissions for the current user.
    In all, better not to use the actual values of the identity column, just use it as your criteria to find the previous PID:
    DECLARE @LastPID VARCHAR(10)

    SELECT @LastPID = c.PID
    FROM dbo.WBCustomer c
    WHERE c.ID = (SELEC MAX(x.ID) FROM dbo.WBCustomer x)

    Now you have the previous PID, so you can increment that whichever way you need to.
  3. baburk New Member

    Since max(id) will produce wrong value on deletion. When deleting the last record i am getting the same id once again.
    So help me with producing by using IDENT_CURRENT
  4. Adriaan New Member

    My suggestion was to no longer use the ID value for creating the PK value.
    Only use MAX(ID) value to find the most recently added row, read the PID value from that row, then increment the number part of that PID.
  5. venkatesanj@hcl.in New Member

    I have tried to insert some records in an identity column and got confused on the below scenario. It seems to be a bug in sql server :0 I think so,DROP
    TABLE Venkattempcreate
    table Venkattemp (id int identity(1,1),VAL INT)INSERT
    INTO Venkattemp(VAL) VALUES (1)Truncate
    table VenkattempDBCC
    CHECKIDENT ('Venkattemp', RESEED, 1)INSERT
    INTO Venkattemp(VAL) VALUES (1)INSERT
    INTO Venkattemp(VAL) VALUES (1)select
    IDENT_CURRENT('Venkattemp')+1DELETE
    Venkattemp WHERE ID =3select
    IDENT_CURRENT('Venkattemp')+1-- Here, why its inserting value 3 in the identity column. This is an unexpected behaviour of sql serverINSERT
    INTO Venkattemp(VAL) VALUES (5)SELECT
    * FROM Venkattempselect
    IDENT_CURRENT('Venkattemp')INSERT
    INTO Venkattemp(VAL) VALUES (5)INSERT
    INTO Venkattemp(VAL) VALUES (5)INSERT
    INTO Venkattemp(VAL) VALUES (5)select
    IDENT_CURRENT('Venkattemp')DELETE
    Venkattemp WHERE ID =6select
    IDENT_CURRENT('Venkattemp')-- Here, why its not inserting 6 in the below insert statement. This is an expected behaviour of sql serverINSERT
    INTO Venkattemp(VAL) VALUES (5)SELECT
    * FROM Venkattemp
    http://venkattechnicalblog.blogspot.com/2010/01/peculiar-nature-of-identity-insert-in.html
    Thanks and Regards,
    Venkatesan Prabu .J
  6. Adriaan New Member

    Well, it's not a bug if you're not using the proper syntax ...
    The following does not reseed the identity column:
    select IDENT_CURRENT('Venkattemp')+1
    This query only retrieves the highest identity value inserted into the table, adds 1 to that value, and doesn't do anything with either value, nor with the identity column.
    You seem to be looking for the DBCC CHECKIDENT syntax. This sort of data manipulation is not normally associated with "live" production systems, although it might be done during off-line maintenance work.
    Other than that, you should not be concerned with the actual identity value.

Share This Page