Thank you Sal & Vladan,

You are right about the values, I typed them in wrong. My apologies.

I'm afraid I cannot change the data structures. This is also for many clients...

Hi Andrew,

I was hoping that the "lowest available number" was a req we could drop but alas, we needed to keep it. It turns out that gaps in this "check out"...

Sorry, without the Recompile of course.

Thank you for your help Ian.

How about this?

** Retrieve a new assign_no from the assignno table
*/
CREATE PROCEDURE [siriussp_get_assignno]
  @tnSalesPoint int,
  @tnNum_Source int,
  @NewAssign char(17) output
  WITH RECOMPILE
AS
set nocount on
 
declare @nfound    ...

We have decided to go with below. The real change from your suggestions is the repeatableread and readpast hints in the subquery. Incredible speed improvement!

We are about to implement this...

What about this?

1. Make the PK nonclustered

2. Drop the index on ckd_out_to

3. create idx1 on (ckd_out_to asc,num_source asc, num_assign asc)

UPDATE a with (rowlock)

SET a.ckd_out_to = @tnSalesPoint, @NewAssign = num_assign

from assignno...

There is an index on ckd_out_to. Funny that that didn't come over from QA's Script Object to Clipboard command ...