Hi I need to create an INSERT query that will create new rows with a normal identity column, and in another integer column: increment (and rollover) a three-digit number. I'm using MSSQL Server 2008 r2.

Similar to the (incorrect) code below, I have a table and I need an INSERT procedure that will increment the MBatch column from 1 to 999, then rollover back to 1 on the next row insert. For example: the last row (identity=1111) may have MBatch=999, so when I insert new row (1112) I need MBatch=1).

When I call the INSERT procedure, I will pass it the "@MStatus" value, and would like the proc to return the Identity column value and the MBatch value.

My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Having said that, are all of your inserts one at a time and are they all through this procedure? You may run into issues if they aren't.In 2012 you could use a sequence with cycling as the default value on the batch.