Add SQL Server Automatically to a Composite Key

I have a table with a composite key in SQL Server 2008 (this was done for various reasons / DB design, but the main one is b / c, we go to a new schema and have to save the data).

So I created a new table with a foreign key reference and was working on data transfer. Finding and editing data is simple. Now I would like to be able to insert new records (using a given FK value) and be able to automatically increment the newly inserted record IDs and handle it all in a stored procedure.


Table 1: ( id, type make up the composite key


id   data  type(FK)
1     A      1
2     B      1
1     C      2
2     D      2
3     B      1
4     C      1
3     A      2
4     G      2


Now if I have another record (F data of type 2), I would like to be able to pass the record data ( data=F,type=2

) and have a stored proc (5, F, 2) insert .. in a thread-safe / concurrency / scalable way (i.e. . without execution SELECT MAX(id)...

). Any ideas / thoughts on the best way to do this from the SQL Server community?


source to share

3 answers

The easiest and safest solution is to simply add a column INT IDENTITY

to the table and let SQL Server handle the intricacies of updating the identity value. This will give you the real, correct and accurate value of the ID - regardless of the column value type


In SQL Server 2012, you can use a database object SEQUENCE

to create a sequence for each value type

you need - but the 2008 version, unfortunately, is still nothing like that.



This is one way to do it, assuming that the data you want to insert is in another table (Table 2):

INSERT INTO Table1(id, data, type)
FROM Table2 A
LEFT JOIN (SELECT type, MAX(id) MaxId 
            FROM Table1 
            GROUP BY type) B
ON A.type = B.type


As @marc_s said in a comment: Using the MAX (Id) approach is usually not safe in concurrency load



Why would you want to store this data when you can always get it at the time of the request and ensure it is accurate?

  [type], data, 
FROM dbo.table
ORDER BY [type], id;


How you want to do this, you will need a trigger (or more) to maintain the information in the table as the data changes. For example, what happens when you run the following queries:

DELETE dbo.table WHERE data = 'B';

UPDATE dbo.table SET data = 'Z' WHERE data = 'C';


Do not store redundant data. You think it will save you something, but all he is going to do is take extra storage and create unnecessary maintenance persecution.



All Articles