KEY generator based on another column value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

KEY generator based on another column value

Author

Topic

subysri
Starting Member

2 Posts

Posted - 2010-06-03 : 01:47:15

Hi,

In my table I have 2 columns ID and DEPT(Composite Keys). ID is an auto incremented column. I need the ID value to increment based on the DEPT column.

Say, I have 2 DEPT values as XXX and YYY. So when incrementing the ID value, it should start from 1 to n for XXX and similarly 1 to n for YYY.

Is it possible to handle this in the table level itself instead of quering in the code for max value based on the DEPT.

Thanks,
Subha

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-03 : 01:55:05

You can not use the identity option (auto increment) for this. You'll instead need to handle it through the ROW_NUMBER() function.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog