Can I ask why everyone is putting their code inside a UDF? Can't the code just be inlined as necessary?

Have you done an index tuning session to find out if the tables of concern are optimally indexed? Have you analyzed your queries to ensure they are efficient? ...

With only 2 drives it is likely more important to be concerned about your tempdb and transaction log placements as opposed to your database file placement.

Surprised no one...

I had intended to write pretty much everything Grant did - thanks for saving me the time Grant!

I will add one thing:

We should be careful about creating temporary...

I am, and have been for a long time, a BIG proponent of Hyperbac (which is now owned by Red-Gate). This was way before they had the ability to...

what error is thrown? if the update completes but the change isn't there after, is there a trigger on the table?

I will add as a minor point that non-clustered index fragmentation (at least the extent kind) usually doesn't matter that much since the seeks tend to be random in nature...

Nadrek (9/8/2010)

---

Nice overview; I'd like to encourage the addition of looking at SQL Profiler CPU, Reads, Writes, and Duration columns on SQL:BatchCompleted events.

Not only do these show aggregates, if you...

1) If you create a clustered index on a HEAP table, you won't need to defrag it immediately since it will be as defragged as possible upon creation.

2)...

For almost all ETL scenarios where you wipe the entire table or affect a reasonable fraction of rows it is best to disable auto update/auto create, do the load, then...

loki1049 (9/2/2010)

---

Wayne, thanks for that example, and yes I am now using that splitter function to create a table to join to. That works well, and I am happy with...

Here is another link from Gail Shaw relevant to this thread: http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

People, if you use this type of construct either do it with one of the 3 mechanisms covered...

I'm with the guy/gal that recommended using the index usage stats DMV, at least for inserts. And if you have a column that is "always" updated you can get...

The difference is that you must dot a lot more i's and cross a lot more t's with clustering. It can really be a PITA. If you go...

I love the smell of no-indexed tables in the morning. Smells like . . . MONEY!! 😀 (wondering how many got that great American movie reference)