Subject of Orig Post:

The Data are logs from Many different machines (1000s). The table was actually installed as a heap. The Table is 98% of the DB size....

But.. If some reports don't filter on Date they wouldn't benefit from the new index design.

I bet there is no query which benefits from the current index design.

Well, the inserts...

Why is an Autoincremental bigint ID a bad idea for a clustered index?

Telephone numbers are allocated to devices/SIM cards incrementally, in sequence. It's one of examples of auto-incremental ID's we...

With this kind of queries you need a clustered...

Look inside the code and find out what it uses as grounds for its recommendations.

Then you'll be able to figure out what's different in your environments.

P.S. Professional racers never use...

Compression to save disk space is of dubious value unless it saves IO.

With technologies such as Fusion-IO and SSDs where you are talking about literally thousands of times...

May I ask you where do you have the data coming from...

You need to declare those variables in sp_executesql as OUTPUT parameters

Filtered index?

I prefer to use SQL_VARIANT instead of NVARCHAR(MAX) for generic columns.

It keeps numbers as numbers, datetimes as datetimes, avoiding potentially dangerous conversions to character strings and back.

N'CAST('...

The best way to rebuild indexes is not to do it.

You need to work on your index strategy. It's named "strategy" for a reason. Correct approach to indexing structure pays...

"superior ability in SSIS" is strictly opposite to very efficient and elegant designs", as well as "very strong SQL programming".

SSIS is pretty ineffective, bulky and hard to manage tool for...

queries on ID are typically for a single record, rarely for a small group of ID's mentioned in a list.

nobody is ever interested in records with ID's within a range,...

Bad choice of clustered index.

It must be not on identity column but on [Date].

You hid the name of the remote machine you’ve connected to.

I bet it’s not “26003D9”

That machine must have DLS enabled, and the server hosting the SQL Server instance must have...