sql composite key with identity

Question

Is it possible to create a composite key in sql 2000 of the form:

event_id id2
adf             1
adf             2
adf             3
cfg             1
cfg             2
edf             1

where the id2 restarts the count at each change of event_id. I need the numbering to be exactly like that either by creating a table or other SELECT statement trickery.

Edit: I may not have been clear I only temporarily need this table for a join.

Answer 1

I would avoid calling this an identity, since it's not and would be confusing to most anyone else.

I would think you'd have to do this via an insert trigger and transaction locking around the sequence incrementing.

Answer 2

You can't get SQL to do this automatically for you.

If you really don't need this, then skip it, you'll need to query the table to get the next value then have to insert the row, while locking the selects, so no one else queries to get the same next one.

I'd call it a SEQuence and not an ID, however you'd have a better PK with a regular identity only, and you can still have a SEQ column.

Answer 3

You might be able to do this using an INSTEAD OF trigger. I think you could make that work for the case where rows are inserted one at a time, but I can't think of a way to do it for multiple row inserts because SQL 2000 doesn't have the ROW_NUMBER() function.

I think your best approach would be to use a stored procedure. Something like:

CREATE PROC insert_event(@event_id char(3)) AS
DECLARE @max int;
SELECT @max = MAX(identity) WHERE event_id = @event_id;
INSERT INTO [table] (event_id, identity) VALUES (@event_id, @max + 1);

Answer 4

Sort of like this:

CREATE TABLE #Temp (val char(3))
INSERT INTO #Temp VALUES ('adf')
INSERT INTO #Temp VALUES ('adf')
INSERT INTO #Temp VALUES ('adf')
INSERT INTO #Temp VALUES ('cfg')
INSERT INTO #Temp VALUES ('cfg')
INSERT INTO #Temp VALUES ('edf')

SELECT 
    Val,
    ROW_NUMBER() OVER (PARTITION BY Val ORDER BY Val)
 FROM #Temp

DROP TABLE #Temp

Answer 5

This is an answer I gave to another poster, but it demonstrates exactly what you are asking for:

SELECT
   seqid = identity(int, 1, 1),
   event_id,
   S.name
INTO #EventNames
FROM
   celcat200809.dbo.CT_EVENT_STAFF ES
   LEFT JOIN celcat200809.dbo.CT_STAFF S ON ES.staff_id = S.staff_id
ORDER BY
   event_id,
   S.name

SELECT
   EN.event_id,
   Max(CASE seqid - minseqid WHEN 0 THEN EN.name ELSE '' END))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 1 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 2 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 3 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 4 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 5 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 6 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 7 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 8 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 9 THEN EN.name ELSE NULL END, ''))
   + Max(Coalesce(' + ' + CASE seqid - minseqid WHEN 10 THEN EN.name ELSE NULL END, ''))
FROM
   #EventNames EN
   INNER JOIN (
      SELECT event_id, minseqid = Min(seqid) FROM #EventNames GROUP BY event_id
   ) X ON EN.event_id = X.event_id
GROUP BY EN.event_id

You can ignore the 10 Max() expressions. The key parts are: - Stick the values into a temp table with an identity column, sorted in order by group. - Get the min value per group with an aggregate query (derived table X above) - Subtract the min value from the identity value (and add 1 if you need to start numbering at 1) and voila you have numbers that restart at 1 for each group!

If you're going to use the results of the select more than twice, then at creation time add another int column to the temp table and update it with the new numbers, instead of calculating it each time. I'm guessing about when performance will be better to do an update... testing is in order.