Advanced Identity Fields

Hi!

a) PROBLEM 1:

I have set up my main database like this (of course I'm showing an abbreviation):

company - int (identity)
name - nchar

I have several companies stored in the previous table. Now I have another table with messages:

company - int (related with the company of the previous table)
messageID - int (identity)
contents - nchar

company and messageID are the main key of this table. I want to set the messageID column to change automatically. Since I declared it as identity it is working fine, but I was looking to start it on 0 on every new company:

Company messageID
0 0
0 1
0 2
1 0 <- Here the company changed, so the messageID resets
1 1
1 2
1 3
2 0 <- Again
2 1

Any suggestions?

b) PROBLEM 2:
I have my database stored locally on my computer. When I finished working with the database it has a lot of data for testing. I want to upload the database to my hosting provider or to my customer's. But the identity columns keep incrementing since the last value of my tests, so it's kind of annoying to see values as: 1250, 223, etc. when I expect to see 0,1, 2 and so on. Also, for receipts this is a very important issue.

How can I reset the identity fields?

Thank you very much for your attention and help.

CT

DBCC CHECKIDENT (tblName, RESEED, 0)

so the next record will start from 1

HTH

|||

#1 You have to create trigger on this table for insert which will automatically calculate your message ID based on number of messages for company for which you insert record.

# 2 I suppose that you would like to remove test data from your table so you will be safe using TRUNCATE TABLE to delete all records and reset identity to 1

|||I really appreciate if you can give a sample for this. I'm worried that the trigger procedure wont be able to handle concurrency.