SELECT *
FROM #Date
WHERE ISDATE(DateString) = 0 OR
  DateString NOT LIKE '__[/.-]__[/.-]____' OR
  SUBSTRING(DateString, 3, 1) <> SUBSTRING(DateString, 6, 1)

It's no longer needed for moving files around on the same instance.  Instead you can update the master catalog with the file locations, take the db offline, move the files,...

You don't need an ID in this table at all (at least on what's known so far); in fact, as so often is the case, it corrupts what would be...

It may depend on specifically what you're trying to do.

For example, if you just want to execute a stored proc in the context of that db, you can...

Based on what's been show so far, I believe the best clustering key for this table would be:
( ProductCode, ValidFrom, ValidTo )
rather than just ValidFrom and ValidTo, assuming...

Yeah, your "DBAs" are dead wrong, and don't seem to understand indexing at even a basic level, which is a tough situation for you (and all the dbs they control!).

It seems as if you'd be scanning both tables anyway, since you're reading all the master and details (?).

Maybe just?:

FROM Details d
INNER JOIN Master m...

If you know that all rows in table1 come before rows in table2, you can go ahead and assign the ranking as part of the load of the table. ...

The best way to do this is to cluster the table on ( id, iDate ); that is, the clustered index on the table should be on those columns.  And,...