Can SQL Server have an identity column based on another column

Question

Can SQL Server have an identity column based on another column?

I have a table design like this

Audit 
AuditId
CompanyId

Everything in the system is audited, and the table can be multi tenant, thus the CompanyId.

In the context of an audit, people like to see a sequential sequence of events and due to the multi tenant nature, the identity will skip numbers like this.

Company1 User logged in
Company1 User logged out
Company1 User logged in
Company9 User logged in
Company1 User logged out
Company9 User logged out

I would ideally keep AuditId as a normal identity but wish to have a secondary identity column that is unique to CompanyId.

Is this possible in SQL Server, or do I need to do it in code?

"In the context of an audit, people like to see a sequential sequence of events" - yes, they do. But if that's what you need (a sequence with *no gaps*), then `IDENTITY` is the wrong tool to start with. See e.g. the bold section at the bottom of [this answer](http://stackoverflow.com/a/14146237/15498) (and also, just look at the question :-)) — Damien_The_Unbeliever, Jun 09 '16 at 07:57

score 1 · Accepted Answer · answered Jul 07 '16 at 05:21

1

SQL Server can't have an identity column based on another column because there can be only one identity column in a table.

answered Jul 07 '16 at 05:21

Akshey Bhat

8,227
1
20
20

score 0 · Answer 2 · answered Jun 09 '16 at 07:16

0

You could have the companyAuditId populated by an insert trigger. It would be defined as int, but on insert the trigger will

Create trigger trAuditI for insert 

        UPDATE audit
     SET CompanyAuditId = max(companyAuditId) 

    where companyId
        = Inserted.CompanyId

answered Jun 09 '16 at 07:16

Mike

1,645
3
13
21

This trigger is broken. Triggers fire once per statement, `inserted` can contain *multiple* rows (or no rows). – Damien_The_Unbeliever Jun 09 '16 at 07:55

Can SQL Server have an identity column based on another column

2 Answers2