Hi ,

Please take a look at this article, which will help you

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Better , but your non clustered index is not being used (probably due to the 'catch-all query').

Try a query with hardcoded values to see what the 'optimal' search should be

In addition to Gail's page have a quick look at my blog http://sqlandthelike.blogspot.com/2009/06/catch-all-queries-and-indexing.html

I think we've all been there , cant quite believe what you are seeing , then realising the massive cock-up.

Careful with uncommitted reads http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

As frustrating as it sounds, that is correct.

Double check every small little point.

Tony Rogerson and Simon Sabin run the UK User Group, always informative meetings with free beer and pizza courtesy of Microsoft. Meetings and live meetings at various...

Are you sure you didnt alter the procedure within a transaction that then got rolled back ?

Presumably there is job responsible for creating the objid's , could that not insert the priming row ?

I guess that it could put a small strain on the server , not anything i would particularly worry about.

Here's another method anyway

create table tabx

(

AccNo integer

)

go

insert into tabx values(0)

go

Declare @AccNo...

Hi,

Adding indexes will obviously have a negative impact when inserting data. Its a balancing act.

But you still havent identified which portion of your query is causing your issues....

select datediff(month,getdate(),'01jan2000')

Additionally you have quite a few UDF's being used in your return columns.

Change your statement to 'select 1 from ...' to eliminate the possibility that it is one of them...

You cant do that. A cte's scope is a single statement.

Maybe a view would better suit you ?

Why not avoid the issue altogether , and do the work in one statement ?

UPDATE AccountCounter

SET AccountNumber = AccountNumber+1

Output inserted.AccountNumber

WHERE objid = @currentObjID

Hi ,

Triggers fire once per statement. Period. Thats what they do.

You will have to adapt your trigger to deal with multiple rows, or find a different way...