Well I'll leave you all with that quote .

Alright, you could use the current identity then... Of course if you have big gaps, you'll have a few updates where next to now rows will be updated. Also...

NP... Don't wanna get into that when he doesn't know how to select .

Make sure you catch what are the values of the parameters. This is usually a recreatable situation.

5K

Stay BASIC please .

For the force index part >>

Select id from dbo.SysObjects

Select id from dbo.SysObjects with (index (ncSysObjects))

you can also use the indid of the index instead of its name... but the name...

I don't have time to scan all this ATM. Fast pointer >> everytime you see a scan (table, clustered index or index scan). That can be the reason...

It would take time to do the add column, but it would speed up the loop process greatly >>

Add a new identity(1,1) column, INDEX IT

then something like this :

Declare...

Remember this if it happens again.

Add it in the suggestion section of the site.

It adds a little overhead, but you get very little more locking on the tables, so your current tasks wouldn't be affected. So in essence that thing could run...

Well if you need to run batches here's how I would do it ::

Fetch next valid id

Insert into Table (Select columns, @validId + Count(*) from table self join on id...

That's where you're getting the problem >>

SET SHOWPLAN_TEXT ON

GO

CREATE TABLE #test (a int not null)

select * from #test

drop table #test

GO

SET SHOWPLAN_TEXT OFF

this option seems to only fetch the plan without...

I wouldn't go with that route first. I'd first make sure that the plan is bad first and I doubt it's the case here.

Can you copy the code of the proc in QA and execute it directly...

If you get the same error I'm afraid you'll have to use a table variable instead of...