The single biggest factor in overall query performance is getting the best clustered index on each table (hint: it's rarely an identity column). Typically you'd start with a higher...

I'm virtually certain the tables are clustered improperly. That should be fixed before you do the partitioning. It's even possible you won't need to partition if you put...

You can, and definitely should, avoid converting cStaging.cDate if you can.

--if "cDate" is a datetime column:

--instead of this:

--WHERE (CONVERT(DATE, cStaging.cDate) BETWEEN @startdate AND @endDate) AND (CONVERT(DATE,...

sqldriver (7/5/2014)

TheSQLGuru (7/1/2014)

Good call on the joining to a split-built derived table and how putting that into a temp table could make things faster. ...

By far the biggest payback is in correcting clustered indexes if the wrong key column(s) were chosen for it. And that alone often allows very large numbers of other...

I also make the doc tables "live". That is, the code that created/initialized/set up a new db should use the "documentation" table to determine whether the table is static...

TheSQLGuru (7/2/2014)

ScottPletcher (7/2/2014)

I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of...

You did review the stats SQL Server provides on index usage and missing indexes before you made your changes, right?

Edit: What indexes you think SQL will need/use and what indexes...

I think that's over-stated. Most optimizers will short-circuit when they safely can. Presumably later versions of the optimizer...

I wouldn't want to put them in a separate schema unless you really had to. Aside from hideous renaming issues (*), you might end up with what seems like...

If you want to keep/commit previous activities in the loop, you likely need to explicitly BEGIN and COMMIT a tran for each loop, something like this:

WHILE ...

BEGIN

...

Try clustering the table by datetime (rather than identity, I'm guessing) and specify...

Yes, it can be.

For overall performance, what is really critical is determining the best column(s) for the...

Given that it's a single string being split, I don't think there's much to gain by changing that code.

Don't care about pk/fk specifically. What indexes currently exist on these tables? Could you add a computed column(s) and create...