I gotta say, those two statements seem contradictory 🙂 or you're on a terribly...

Either one.

Adding a rowversion column to every table would indeed be huge overhead for no real gain, since it likely would never be used. Eight bytes per row would...

True. I didn't explicitly mention it, but I meant using standard/approved methods, such as generating the legit commands:

CREATE LOGIN

EXEC sp_addlinkedserver

etc..

With the old master db there, you could get (almost)...

You should be able to restore the master db to a different db name, if you want to pull data from the old master db directly in the SQL 2008...

I didn't object to doing it on ONE table.

I merely pointed out that for SQL to try to do that on all tables, as a default, as it were, would...

Yes, cluster the table by date and/or employee; choose the column(s) and the order based on your other query needs.

Typically the clustered index...

Yes, that can be done.

It's easier with linked servers but possible even without.

Do you have a Linked Server defined between the servers? Or do you prefer not to have...

For an actual SQL job, SQL itself records that info.

Look in msdb.dbo.sysjobhistory.

To get the job name, join...

No. Maintaining that on all tables would be a huge sacrifice of performance for no real gain.

You're still fine. SQL will add an integer counter itself so it can tell the rows apart. It's some slight overhead but it's not a real issue.

If you...

That's your problem.

You must *cluster* by recorddate. Add a non-clus index on id, if you need it.

Lowell's change is vital...

How is "devicehistory" clustered?

If it's not clustered, or clustered by something irrelevant like an identity column, you could cluster it by recorddate and vastly speed up your queries.

Also, you should...

I expected that, just wanted to be fair about it 🙂 .

The optimizer still needs improvement.

Did you adjust the original query so you're doing an apples-to-apples comparison?

SELECT *

FROM #myData md

CROSS JOIN #patterns p

WHERE

md.descr LIKE p.pat

Is that really necessary?

Won't SQL treat computations on a declared variable as a constant, so they are still...