none
Reseed Identity Column when other column, value change

    Question

  • hello

    I have a table with four fileds. The first and second field are primary key. Second field of primary key is a identity.

    when the first field changed, I  need the second field Reseed  to 1.

    Example

    1,1

    1,2

    1,3

    2,1

    2,2

    n,1

    Thank's a lot

    Tuesday, November 19, 2013 5:49 PM

Answers

  • I do not think reseeding the value will work. What if  you insert 1,3 after 2,1....(of course, this will cause fragmentation, if you have clustered index on it)

    instead of trigger might work... sample below.. create a table "stest" with three columns (sno, sno1,sname) and make sno ans sno1 as primary keys). create the below trigger on it...

    create trigger trg_insert on stest instead of insert as declare @a int select @a=isnull(max(a.sno1),0)+1 from stest a inner join inserted B on a.sno= b.sno insert into stest(sno,sno1,sname) select sno,@a,sname from inserted

    insert into stest (sno,sname)
    values (1,'stan')



    Hope it Helps!!


    Tuesday, November 19, 2013 6:54 PM
  • Hallo tgarijo,

    you won't find a feasible solution although Stan has give a pretty good starting point. The problem(s) with your requests are:

    • Inserts will be handled as Stan has given you an example
    • What will happen if you want to insert a new value in between?
    • What happens to the numbering if you delete a record?
      Should all given numbers be renumbered?
    • do you have foreign key relations with cascading updates?

    I would recommend to have a clustered index on a contigious Id (IDENTITY). Than build your groups by using a non clustered index on your Field1 and use the solution from RSingh in a view by using the windowing function ROW_NUMBER(). This solution will give you all the above flexibility from above without any complicated logic behind.


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Wednesday, November 20, 2013 6:50 AM

All replies

  • I do not think reseeding the value will work. What if  you insert 1,3 after 2,1....(of course, this will cause fragmentation, if you have clustered index on it)

    instead of trigger might work... sample below.. create a table "stest" with three columns (sno, sno1,sname) and make sno ans sno1 as primary keys). create the below trigger on it...

    create trigger trg_insert on stest instead of insert as declare @a int select @a=isnull(max(a.sno1),0)+1 from stest a inner join inserted B on a.sno= b.sno insert into stest(sno,sno1,sname) select sno,@a,sname from inserted

    insert into stest (sno,sname)
    values (1,'stan')



    Hope it Helps!!


    Tuesday, November 19, 2013 6:54 PM
  • Another option to generate Col2 as,

    SELECT *,ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) Col2_New FROM CheckTbl


    Regards, RSingh

    Wednesday, November 20, 2013 6:09 AM
  • Another option to generate Col2 as,

    SELECT *,ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) Col2_New FROM CheckTbl


    Regards, RSingh

    Hallo RSingh,

    your suggested solution may not work because the col2 need to be part of the clustered index ("The first and second field are primary key")! Neither in table definition nor in indexed views are windowing functions allowed!


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Wednesday, November 20, 2013 6:36 AM
  • Hallo tgarijo,

    you won't find a feasible solution although Stan has give a pretty good starting point. The problem(s) with your requests are:

    • Inserts will be handled as Stan has given you an example
    • What will happen if you want to insert a new value in between?
    • What happens to the numbering if you delete a record?
      Should all given numbers be renumbered?
    • do you have foreign key relations with cascading updates?

    I would recommend to have a clustered index on a contigious Id (IDENTITY). Than build your groups by using a non clustered index on your Field1 and use the solution from RSingh in a view by using the windowing function ROW_NUMBER(). This solution will give you all the above flexibility from above without any complicated logic behind.


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Wednesday, November 20, 2013 6:50 AM