Add Identity Increment per user

Hi
I have a Jobs Table: Job_ID,User_ID,Job_Info,...
Job_ID is the Primary Key.
I want to add [Job_ID_PerUser] so I wiil get:
Job_ID Job_ID_PerUser User_ID
1 1 A
2 1 B
3 1 C
4 2 A
5 3 A
6 2 C
Thanks

1) Set the key as primary key...

2) In the coloum properties there in Identity Specification

3) Set it to Yes

4) And set Increment Seed As 1

|||

I have a Primary Key with the Increment Seed=1 named [Job_ID]

I want another ID for a job [Job_ID_PerUser] that will start the count from 1 for every new user

and will increment by 1 every time this new user will add a job

|||

Assuming that you are using SQL Server, you can create the a SQL Server function the will calculate the next value of for the customer and add that as the column for the server.

CREATE FUNCTION dbo.GetCustomerMax (@.userIdint)RETURNSintASBEGINRETURN SELECT MAX(Job_ID_PerUser) + 1WHERE User_Id= @.userIdEND

In SQL Server Management Studio, you modify the table, add the column of type int.

Then create a stored procedure for the insertion of the record and call the GetCustomerMax function.

|||

Thanks it works greate.

do i need to put all of that in one transaction?

Activity in one DB causes time-outs in another

We have several SQL 2000 databases on one server.
One of the applications I'm responsible for has batch jobs that run for an hour; all activity is on the database. During this hour, other applications that use other databases on the same server experience time-outs. One of my coworkers did a count(*) on an empty table and it took 11 seconds.

We pay people to keep our servers up and running. Is this something they might solve by reconfiguring the server? It seems strange to me that a single database is allowed to hog all server resources.
We are meeting with them later this week, and I'd like to have some knowledge about this; we don't want to BS'ed into buying a new server.personally, I would look at optimizing your hour long batch process if at all possible. If you follow the directions in Brett's sticky at the top, I am sure some one would be happy to look over your code for you.|||There's that,yes. Right now it's cursor driven, because, "you know, if we just make some changes to the previous version we'll be done quicker."

Data is read from a file (created at a specific point in time) into a single table, and the batch process consists of two stages: stage one is comparing the new data to what we already have, and create an entry in the log table for every difference. Stage two is changing our data so that it reflects the data in the file, for that particular point in time.

I'm pretty sure that stage one can be done set-based, but I'm not so sure about stage two. The actions required for each row in the new data depend on what's already there in our db.