Manual Auto-Increment

No, this increment is based on CenterId and ItemYear for InWardNumber (This is a logical number).
Already I have an identity column as a Primary Key which is InWardId.

Hi,

I would say you will get in troubles with this kind of increment.

Let's say the year is 2009 and the CenterID is 1. Your resulting number would be 20091. Then, a few records later, the CenterID is 10, resulting number would be 200910. In 2010 and CenterID 1 you number would be 20101.
The table would look like this:

20091
20092
20093
20094
20095
20096
20097
20098
20099
20101
200910

Your trigger asks for a record of this table where the CenterID and year is equal to the one which was inserted. First question is: Where does the CenterID in the inserted record come from? Normally you would expect that this is a new number because it is an INSERT command. If it is a new number, the result of the SELECT would be an empty record, so ISNULL...+1 would be 1, in every case of a new CenterID. If CenterID is not a new number, the result would be the next number after CenterID and because the SELECT only returns one number the MAX does always return this one number. Even if the WHERE would be changed, you would see that there is a problem between 20099, 20101 and 200910 as the last one is the maximum one.
Next is that you test if the CustomerNo is empty in an inserted record - normally I would expect that this is the case in every new record as you want to generate a new number. So the result would be the trigger is never executed.
Next is the many users. "Max" is always a problem in a multi-user environment as you never know if the record is the max record in the next microsecond. Between two commands there can be a difference in an environment with heavy load.

So, you said you already have an ID on this table which cannot be used for auto-increment the custom number. As this is the case I would do it in this way:

1. You already have two fields for CenterID and year which are the base of the new number - let it in this way and don't add an extra field to calculate an assembled number and store it in the database. For the example above your table could look like this:

InwardID    Year     CenterID
34567         2009      8
34568         2009      9
34569         2009      10
...
34970         2010      1
34971         2010      2

2. now let the client do the work: If you open a record of this table, display the fields Year and CenterID as one number like "2009-8" or whatever you like. This is no problem in any client. For the user there is no difference of displaying a precalculated field or displaying an assembled field. And in SQL you have a lot more possibilities to select the right records as you can select records of a year and sort the records with the CenterID and so on.

3. now the INSERT: Again let the client do the work. In the moment of the insert you can test the table with MAX(CenterID) if there is already a CenterID "2" in year 2010 - which is the case here in the example, another user was faster. If this is the case, loop until MAX(CenterID) doesn't return a result. Then try to use the new ID for the INSERT command and catch the error if there is one. If there is one it means again another user was faster, so loop again. You will need a VERY high load to let the user wait longer than one or two seconds to save the new record.

Another possibility is to insert a new record when the user begins to write one and display the new CustomerNo in the client application. The number is reserved on this way and to get the number you can use the same loop as described above. This would only move the time to wait for a new number to the beginning instead of the time when the user is saving the record. The disadvantage of this method is, if the user decides to close the record without saving the number was blocked for other users which got a higher number in the meantime. So you must delete the unused one when closing the form and maybe get the not used number for the next user - this is difficult and not so easy as the first method.

Cheers,

Christian

Hi Bitsqueezer,

Thanks for your longer response. But I think you did not understand what I meant above or I did not write as you can understand well. Sorry for that.

Actually I did not mention anything about Combining those column value into one as you mentioned 20091, 20092 etc. In my case, my InwardNumber is going to be unique for each Center and Year, as I clearly mentioned in the trigger query.

My question was, what is the best alternative for the above method? How it will affect the performance?

I hope I clarified you.

trigger is not advisable unless, it is very important as it make database performance slow. as of your case where the data is 2 miliom, It's not at all advaisable to have such trigger.

Other Alternative could be : use identity columne  

Or use the manula Increment through your front-end client side program instead on database.

3rd Option I will suggest if both above is not suite for your program then use a stored procedure for insert statement, and make manual increment within stored procedure instead of trigger.

1. I cannot use Identity Column because I want to increment this column for Each Center for Each Year.
2. If I make this code in front end application, then how about if more than one user press Save for this page? Only one user will get the new number.
3. If I make this code as a SP, again same as point 2, if n number of user call this sproc at the same time.

So, which other alternative is the best?