none
can i do auto increment on two tables RRS feed

  • Question

  • Hi I want to create a table with two auto increments . The first one is id which will auto increment by 1 , second column is justauto and i want to increment by 10 each time. When i tried to add both it says only one IDENTITY is allowed. Is there advice on how i can add the second column with out declaring as IDENTITY?

    Thank You

    Friday, July 26, 2019 7:47 PM

Answers

  • Hi I want to create a table with two auto increments . The first one is id which will auto increment by 1 , second column is justauto and i want to increment by 10 each time. When i tried to add both it says only one IDENTITY is allowed. Is there advice on how i can add the second column with out declaring as IDENTITY?

    Thank You

    Good day Lisa,

    If the two columns has direct dependency (second column always includes the the value of the first column multiple by 10) , then you can use computed column as Jingyang suggested.

    You can have as many auto increment columns as you want, without any dependency between them by using sequence and default value. You can use IDENTITY for the second column if this is what you want and sequence for the first column.

    Quote: "A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created."

    For example:

    CREATE SEQUENCE dbo.CountBy1  
        START WITH 1  
        INCREMENT BY 1 ;  
    GO 
    CREATE SEQUENCE dbo.CountBy10
        START WITH 1  
        INCREMENT BY 10 ;  
    GO  
    
    DROP TABLE IF EXISTS myTable
    GO
    create table myTable(
    	CountBy1 int DEFAULT (NEXT VALUE FOR dbo.CountBy1),  
    	UsingIDENTITY int IDENTITY(1,10),
    	CountBy10 int DEFAULT (NEXT VALUE FOR dbo.CountBy10),
    	txt NVARCHAR(10)
    )
    GO
    
    INSERT myTable(txt) values ('J1'),('J2'),('J3')
    GO
    
    SELECT * FROM myTable
    GO



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, July 27, 2019 8:32 PM
    Moderator
  • Check the sample I posted.

    You may need to adjust your first number, in the sample we use 78 as you provided.

    For inserts, no value was provided for either columns.

    By the way, learn how sequence works and you may have another option do things as pituach told you earlier.
    Monday, July 29, 2019 2:16 PM
    Moderator
  • Thanks Jingyan and Ronen, i already have the table and updating it with new records , the ID field stops at 77 so my

    [id] [bigint] IDENTITY (78,1)NOT NULL takes care of it , but my other column 

     justauto is not a multiple of 10 like id*10, the corresponding value of justauto is 440 when id=77

    so my next id starts at 78 and my next justauto should start at 450 , hope i made sense.

    Thanks

    create table test (id int identity(78,1),  col int, id10 AS 440+(Id-78+1) * 10)
    
    Insert into test (col) values (1)
    Insert into test (col) values (2)
    Insert into test (col) values (11)
    Insert into test (col) values (22)
    
    Select id,id10 from test
    
    drop table test

    This is VERY problematic solution!

    Using this computed column you might get very strange values in the id10 column, which has nothing to do with 10 times of the value in the id column

    For example, let's say that someone will add id=1... the result is that in the computed column you will not have 10 but -320

    * By the way, for most cases it make no sense to have two column which have the value "x" and "10*x". There is no sense in duplicate rows in most cases. If you do need these two columns then I must assume that for most cases these two columns do not have direct dependency between them.

    Anyhow, here is a full example of the problematic with using: id10 AS 440+(Id-78+1) * 10.

    create table test (id int identity(78,1),  col int, id10 AS 440+(Id-78+1) * 10)
    Insert into test (col) values (1)
    Insert into test (col) values (2)
    Insert into test (col) values (11)
    Insert into test (col) values (22)
    GO
    
    Select id,id10,col from test
    GO -- all seems ok... is it really well designed for all scenarios?
    
    -- Let's add new row with id- 1
    
    -- SET IDENTITY_INSERT to ON.  
    SET IDENTITY_INSERT dbo.test ON;  
    GO
    Insert into test (id,col) values (1,1)
    GO 
    SET IDENTITY_INSERT dbo.test OFF;  
    GO
    
    Select id,id10,col from test
    GO

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Monday, July 29, 2019 3:24 PM
    Moderator
  • > Is there advice on how i can add the second column with out declaring as IDENTITY?

    First of all - if you have two identities on the table - it didn't have any suitable reason. 

    Any time you require second identity value you simply multiply first by 10. That's all. 

    But if you still want to have pre-calculated value in the table - create a trigger for insert/update(?) and fill second column by calculated value. Yes, you will have single Identity, but you will have two fields as you requested. 


    Sincerely, Highly skilled coding monkey.

    • Marked as answer by LisaKruger Tuesday, July 30, 2019 1:34 PM
    Monday, July 29, 2019 3:36 PM
  • When you touch up the identity insert, it becomes different issue.

    Even the hard-coded 78 value is a problem in the first place. But just for her question with a limit solution.

    • Marked as answer by LisaKruger Tuesday, July 30, 2019 1:34 PM
    Monday, July 29, 2019 3:45 PM
    Moderator

All replies

  • You can use a computed column.

    Here is a sample:

    create table test (id int identity(1,1),  col int, id10 AS Id * 10)
    
    Insert into test (col) values (1)
    Insert into test (col) values (2)
    Insert into test (col) values (11)
    Insert into test (col) values (22)
    
    Select id,id10 from test
    
    drop table test

    Friday, July 26, 2019 7:52 PM
    Moderator
  • Hi I want to create a table with two auto increments . The first one is id which will auto increment by 1 , second column is justauto and i want to increment by 10 each time. When i tried to add both it says only one IDENTITY is allowed. Is there advice on how i can add the second column with out declaring as IDENTITY?

    Thank You

    Good day Lisa,

    If the two columns has direct dependency (second column always includes the the value of the first column multiple by 10) , then you can use computed column as Jingyang suggested.

    You can have as many auto increment columns as you want, without any dependency between them by using sequence and default value. You can use IDENTITY for the second column if this is what you want and sequence for the first column.

    Quote: "A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created."

    For example:

    CREATE SEQUENCE dbo.CountBy1  
        START WITH 1  
        INCREMENT BY 1 ;  
    GO 
    CREATE SEQUENCE dbo.CountBy10
        START WITH 1  
        INCREMENT BY 10 ;  
    GO  
    
    DROP TABLE IF EXISTS myTable
    GO
    create table myTable(
    	CountBy1 int DEFAULT (NEXT VALUE FOR dbo.CountBy1),  
    	UsingIDENTITY int IDENTITY(1,10),
    	CountBy10 int DEFAULT (NEXT VALUE FOR dbo.CountBy10),
    	txt NVARCHAR(10)
    )
    GO
    
    INSERT myTable(txt) values ('J1'),('J2'),('J3')
    GO
    
    SELECT * FROM myTable
    GO



    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Saturday, July 27, 2019 8:32 PM
    Moderator
  • Thanks Jingyan and Ronen, i already have the table and updating it with new records , the ID field stops at 77 so my

    [id] [bigint] IDENTITY (78,1)NOT NULL takes care of it , but my other column 

     justauto is not a multiple of 10 like id*10, the corresponding value of justauto is 440 when id=77

    so my next id starts at 78 and my next justauto should start at 450 , hope i made sense.

    Thanks

    Monday, July 29, 2019 1:20 PM
  • Thanks Jingyan and Ronen, i already have the table and updating it with new records , the ID field stops at 77 so my

    [id] [bigint] IDENTITY (78,1)NOT NULL takes care of it , but my other column 

     justauto is not a multiple of 10 like id*10, the corresponding value of justauto is 440 when id=77

    so my next id starts at 78 and my next justauto should start at 450 , hope i made sense.

    Thanks

    Hi Lisa,

    I gave you the answer in my previous response. Please re-read it!

    You can use SEQUENCE for your needs

    You can create SEQUENCE which START WITH 440 (or any other value) and INCREMENT BY 10 (or any other value);


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Monday, July 29, 2019 1:25 PM
    Moderator
  • Thanks Jingyan and Ronen, i already have the table and updating it with new records , the ID field stops at 77 so my

    [id] [bigint] IDENTITY (78,1)NOT NULL takes care of it , but my other column 

     justauto is not a multiple of 10 like id*10, the corresponding value of justauto is 440 when id=77

    so my next id starts at 78 and my next justauto should start at 450 , hope i made sense.

    Thanks

    create table test (id int identity(78,1),  col int, id10 AS 440+(Id-78+1) * 10)
    
    Insert into test (col) values (1)
    Insert into test (col) values (2)
    Insert into test (col) values (11)
    Insert into test (col) values (22)
    
    Select id,id10 from test
    
    drop table test


    Monday, July 29, 2019 1:40 PM
    Moderator
  • Thanks jingyang ,

    Sorry for this stupid question, once i create the table i am doing an insert . Should the fields id and justauto will be part of the insert statement or they get populated automatically?

    Thnks

    Monday, July 29, 2019 2:10 PM
  • Check the sample I posted.

    You may need to adjust your first number, in the sample we use 78 as you provided.

    For inserts, no value was provided for either columns.

    By the way, learn how sequence works and you may have another option do things as pituach told you earlier.
    Monday, July 29, 2019 2:16 PM
    Moderator
  • Thanks Jingyan and Ronen, i already have the table and updating it with new records , the ID field stops at 77 so my

    [id] [bigint] IDENTITY (78,1)NOT NULL takes care of it , but my other column 

     justauto is not a multiple of 10 like id*10, the corresponding value of justauto is 440 when id=77

    so my next id starts at 78 and my next justauto should start at 450 , hope i made sense.

    Thanks

    create table test (id int identity(78,1),  col int, id10 AS 440+(Id-78+1) * 10)
    
    Insert into test (col) values (1)
    Insert into test (col) values (2)
    Insert into test (col) values (11)
    Insert into test (col) values (22)
    
    Select id,id10 from test
    
    drop table test

    This is VERY problematic solution!

    Using this computed column you might get very strange values in the id10 column, which has nothing to do with 10 times of the value in the id column

    For example, let's say that someone will add id=1... the result is that in the computed column you will not have 10 but -320

    * By the way, for most cases it make no sense to have two column which have the value "x" and "10*x". There is no sense in duplicate rows in most cases. If you do need these two columns then I must assume that for most cases these two columns do not have direct dependency between them.

    Anyhow, here is a full example of the problematic with using: id10 AS 440+(Id-78+1) * 10.

    create table test (id int identity(78,1),  col int, id10 AS 440+(Id-78+1) * 10)
    Insert into test (col) values (1)
    Insert into test (col) values (2)
    Insert into test (col) values (11)
    Insert into test (col) values (22)
    GO
    
    Select id,id10,col from test
    GO -- all seems ok... is it really well designed for all scenarios?
    
    -- Let's add new row with id- 1
    
    -- SET IDENTITY_INSERT to ON.  
    SET IDENTITY_INSERT dbo.test ON;  
    GO
    Insert into test (id,col) values (1,1)
    GO 
    SET IDENTITY_INSERT dbo.test OFF;  
    GO
    
    Select id,id10,col from test
    GO

     


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Monday, July 29, 2019 3:24 PM
    Moderator
  • > Is there advice on how i can add the second column with out declaring as IDENTITY?

    First of all - if you have two identities on the table - it didn't have any suitable reason. 

    Any time you require second identity value you simply multiply first by 10. That's all. 

    But if you still want to have pre-calculated value in the table - create a trigger for insert/update(?) and fill second column by calculated value. Yes, you will have single Identity, but you will have two fields as you requested. 


    Sincerely, Highly skilled coding monkey.

    • Marked as answer by LisaKruger Tuesday, July 30, 2019 1:34 PM
    Monday, July 29, 2019 3:36 PM
  • When you touch up the identity insert, it becomes different issue.

    Even the hard-coded 78 value is a problem in the first place. But just for her question with a limit solution.

    • Marked as answer by LisaKruger Tuesday, July 30, 2019 1:34 PM
    Monday, July 29, 2019 3:45 PM
    Moderator