If you do many lookups by ManagerID, you'd likely be able to avoid messing with all this by clustering the table differently:

CREATE TABLE Employees (

ID INT...

You should also be able to use a CROSS APPLY to a "SELECT TOP (1) ..." to get the most recent row for any given user.

I've been a SQL Server DBA for 13+ years and I...

SELECT

COALESCE(t1.Account, t2.Account) AS Account,

MAX(Date) AS Date

FROM (

SELECT Account, MAX(Date) AS Date

FROM dbo.table1

...

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

...