How do you create an compound primary key where the 2 column in the key is identity?

emaduddeen

Well-known member
Joined
May 5, 2010
Messages
171
Location
Lowell, MA & Occasionally Indonesia
Programming Experience
Beginner
Hi Everyone,

I have a table that has a 2 column primary key. I set up the key where the 2nd column is an identity column.

When I ran my VB application I discovered the ID (2nd column) did not increment the correct way. I was hoping it would increment like this:

VB.NET:
Category            ID

50001                1

50001                2

50001                3

50002                1

50002                2

Instead it did this:
VB.NET:
Category            ID

50001                1

50001                2

50001                3

50002                4

50002                5

I'm using Visual Studio 2010 to do this.

Can you tell me what I did wrong? I used to use Clarion and it worked when telling it that the 2nd column was an auto incrementing column but I'm still new to SQL Server.

Thanks.

Truly,
Emad
 
You did nothing wrong. SQL server will never work in the way you expect by default. If you want it to appear like that, write your own stored procedure to manage the sequence numbers.

You could just run this when reporting:

VB.NET:
select
a.category, a.id - b.min_id as id
from
tbl a
inner join
(select category, min(id) - 1 as min_id from tbl group by category) b
on a.category = b.category
 
Back
Top