If you can spot several things you would change, then please let us know. It's not about having an argument, it's about learning, and if you have a good method,...

Having modified that script a bit, and run it on my server - the time was 23.078 secs. Improved, certainly, but not better.

SET NOCOUNT ON

declare @query varchar(100), @id int

DECLARE @muchWorseProblem...

As you can see in the image below, while the actual allocation/deallocation of the cursor doesn't have much of an impact, the inefficient way the query is executed is what...

Yeah - i was just editing my post to...

Chirag (9/23/2008)

---

The pages from the source database get written to the snapshot's sparse file the first time the page gets modified in the source database. Is that right?

If that's so,...

I would say the conclusion is a bit of an understatement - with 5 snapshots the graphs show performance degradation of 500% at best, and 5000% at worst...

Put another way,...

CREATE TABLE [temporaryTable]...

LOL

Matt Miller (5/30/2008)

---

Don't forget that settting a primary key is also the only way you have to put an index (clustered or not) on a table var. I'm not...

JohnG (5/30/2008)

---

Well, somebody decided to test that theory to prove it or not and found that it can improve performance. See http://msdn.microsoft.com/en-us/library/aa175774(SQL.80).aspx

And before you discount the article, you may...

And another typical post thinking you're amazing and that we're all idiots.

Of course it's going to be useful for enforcing duplicate checking, if you're inserting duplicates in there in the...

JohnG (5/30/2008)

---

Various comments:

4. Related to the above and "why would you create a PK on a table variable?"

Often enough. In the case of my delimited values pivot function, for...

Yeah, but it's pretty rare that the data...

Ok - bit of myth busting

1) Table Variables outperform Temporary Tables

This is not necessarily true. You can't create an index on a table variable, and if you are dealing with...

I agree totally. And, to be honest, I think the separation of user/schema makes it even more complicated in sql server 2005. So you could have users a, b &...

I have to say, this 'solution' is actually worse than having a cursor. It would have the same performance implications (because the reason cursors are 'bad' is because row based...