>> this pretty much means that tables that participate in queries should have a clustered index.

Man, are you ever dense! It absolutely does not mean that.

Tables that participate in queries should have indexes. Tables whose records are typically retrieved in continuous blocks or ranges may benefit from a clustered index because of the likelyhood is that the blocks will be contiguously stored on the same page or on closely proximate pages, which speeds up retrieval.

The statement "All tables should have a clustered index" is completely false and absolutely reprehensible advice. Snap out of it, John, wake up.

I do not *think* that you don't know what you are talking about, I absolutely know that, far too frequently, you don't know what you are talking about.

As for the 13 second update time on MoreOnRemoteViews, as soon as the off-topic hot-air on Stored Procedures is refactored and stored in a StoredProcedures topic you'll find the performance more to your liking.

**--** Steve






><<
>On performance-sensitive tables that are heavily loaded with inserts and updates, the heuristic does not apply.
><<
>
>
>In a table with a high amount of reads compared to inserts and updates, a clustered index should be used. As I said below, this pretty much means that tables that participate in queries should have a clustered index. If the table is a pure Tx table, that ultimately updates a different table that will be queried, you certainly could make the case that removing a clustered index in the table with high inserts and updates would reduce overhead. Again, I, perhaps in an indirect way, qualified this below. However, having said that, the additional overhead may be immaterial. And, if the high tx table is also going to be used for reads, the query optimizer favors a clustered index. Therefore, for improved query speed, the overhead in updates and inserts is worth it.
>
>You seem to think I don't know what I am talking about...< bg >. I have delivered several C/S apps in the past 24 months. How many have you delivered?
>
>Since we are on the topic of performance, how about improving those 13 second update times? If you had acceptable performance there, I might actually take your opinion with a grain of salt...< bg >...
>
>
>
>
>
>
>
>>I cite the overhead on the database server to maintain a table in physical order (which is what a clustered index is) on tables that are heavily transactioned. On performance-sensitive tables that are heavily loaded with inserts and updates, the heuristic does not apply.
>>
>>Across the OLTP --> OLAP spectrum, what you say is true at the OLAP end, and progressively gets weaker as we get to the OLTP end, especially at higher volumes.
>>
>>Thus "All tables should have a clustered index" is wrong. It's an engineering absurdity.
>>
>>You seem to think I don't know what I'm talking about. All I'm doing is challenging the one-dimensional heuristics you are inventing or parroting without really thinking about (or properly qualifying) the extent of the problem space to which the statement applies.
>>
>>**--** Steve
>>
>>>Steve..
>>>
>>>I cite page 247 of Inside SQL Server 7. I did'nt want you to think I pulled this out of thin air...
>>>
>>>With that in mind, the only tables that would not need a clustered index would be ones that would not participate in queries - I suppose. At some level, every table participates in a query - to one degree or another...
>>>
>>>The advice is that if your table only has 1 index, it should be clustered.
>>>
>>>I am sure you will find something that disputes this. Just know that I have at least, some authority on my side of the argument.
>>>
>>>A lot of times Steve, you go onto to disagree, but don't cite authority for your opinion. If it is your opinon, fine, but state that.
>>>
>>>
>>>
>>>
>>>>>> Every table in SQL Server should have a clustered index
>>>>
>>>>Er, no <s>.
>>>>
>>>>I specifically don't want to go there. Just know that I know this to be an incorrect general heuristic.
>>>>
>>>>**--** Steve