I just tried this too on a table with 1 million rows, and whilst I agree that it chooses the NC index in preference to the clustered index, the estimated...

I'm totally with those people who gave replies like "it depends", and, more importantly, "try and test".

You can't give a hard and fast recommendation for a standard indexing strategy,...

Well, I've had substantial speed improvements on RAID1 disks, so I see no reason why RAID5 shouldn't benefit as well, even with caching. Granted, caching will reduce the effects...

As an update to the previous posts, I happened to try this again in SQL2005, just to demonstrate the effect to a colleague.

It doesn't do it! SQL2005 happily...

Sounds plausible Martin. I'll set up a test to see if this is the case. All the same, the test I ran in SQLServer2005 (which appeared to...

sysperfinfo doesn't hold any CPU data, though. It only holds SQLServer counters, not system counters. There's no way that I know of for getting this information directly from...

Impossible to say really without knowing what the distribution of data is like in each of the columns. If more than about 10,000 rows in COL_666 match the value...

For printing, the non-graphical showplan is best. Before you run your query in QA, execute the following:

SET SHOWPLAN_TEXT ON

SET NOEXEC ON

Now run your query by pressing F5 or the...

If you start a Profiler trace, filtering on the dbid and objectid for the table in question, and capturing any T-SQL commands against it, you ought to catch the...

Something I ommitted to mention -

As part of the copy, I actually specify an additional identity column (bigint) on the new table. I then update the adjacent column...

The "index" _WA_Sys_COMPANY_6576FE24 is actually distribution statistics automatically generated for that column. Autostatistics just happen to show up like indexes, and in fact are stored in the sysindexes...

Yes, Dave - I'm not used to you being subtle 🙂 When did this start?

Already did that - specified @updateusage with the sp_spaceused command.

I think we may all have missed an obvious point here (or perhaps it's only just dawned on me?)

When calling the dynamic SQL using sp_executesql, we've just called a...

The important thing to understand about non-clustered indexes is that they are AWFUL at returning large numbers of rows. Jo quotes 10% as the cutoff point after which a...