One thing to note with the times is that they vary with other server activity. I ran the following on my server 5 times and got the following results: (exactly...

The reason I am doing it as a string built up is that alot of our procs have optional parameters and as far as I know you have to define...

Don't use hints unless you've tried everything else, and even then think twice. The optimiser is often better at picking a query plan than you are.

If 10 users each lock...

Several years ago, I 'proved' to my boss that the asp frontend that I'd written for a stock options system wasn't vulnerable to sql injection.... and in the process I...

Not possible to tell. SQL uses its memory for all databases as required. memory is divided into areas, but not per database. Things like buffer cache, lock cache, procedure cache, etc.

I...

No, but this should work a lot better

CAST(COALESCE (table.value, '') AS CHAR(6))

In your one, if value had been null you'd have got an empty string. SPACE will also work, as will...

If you use the parameters collection of the ado command object then you don't have to worry about wrapping strings in quotes. been a while since I did asp, but...

cmd.Command...

If you have excessive locking, then you likely either have poorly written queries, poor indexing (or badly fragmented indexes), or both.

I've fixed numerous procedures that have a tendency to deadlock either...

I just need to mention that TOP 100 PERCENT is completely unnecessary and is a bad habit to get into. Only use top if you want a portion of the records.

Other...

What do you mean by 'performance'? Took longer? More IO? More CPU? Which of those statistics are you using?

The statistics that you posted are the client statistics, not the server....

What database did you use for that? I'd like to see what the change in the plan was for the 3rd query.

I tried the following in Northwind. All 3 came...

See the insert into... delete from... suggestions on page 1 of this thread. They should work ust fine and won't require a complete replacement of the table.You can put those...

Makes absolutely no difference on an inner join. On a left or right join, filters the joined table before it's joined in. Doing this can be difficult to understand, at...

Not always. See below

CREATE

TABLE #Temp(

ID int NOT NULL,

Yeah, that's better than mine. Why didn't I think of that...

However

BEGIN TRANSACTION

SET IDENTITY_INSERT Services ON...