I have several tables with more than 1000 pages and fragmentation above 30% and commonly around 90%.
Edit: Thanks for the comments.
It boils down to this - if a table is not being used in a relational context, what are the benefits of putting a clustered index on the table?
I know it can save space, since it can now be defragged. Anything else?
Developers claim it is rarely queried and wouldn't benefit from an index.
Since all tables should have a primary key then yes, all tables should have an index.
Even if you use the table rarely, only once a year, a well designed clustered index and possibly a few non-clustered indices as well will help you speed up your queries, allowing you to spend less time waiting for your queries and reports to finish and giving you more spare time - how's that for an argument? :-)
Marc
PS: Oh yeah, and to (loosely) cite Joe Celko: "if it doesn't have a primary key, it's not a table" :-)
Being a web developer myself I personally do not mind if a DBA chooses to index a table. it will not affect my application.
Have at it Haus....
"Here are the statistics on how much smaller the tables are when I took hotcopies of them and added indexes. Compelling, aren't they?"
NO...But!
Unique indexes help keep your data clean if you desire uniqueness on a particular field. Let the data base do your editing for you in this case.
Other types of indecies generally improve retrieval and reporting performance. This is where the business requirements will drive the need for more complex indecies.
If your application is OLTP leaning, then simple unique key indexes should suffice, as others will slow your response time down. If your application is primarily reporting and retrieval, then bring on the indexes and retrieve to your heart's content.
I'd highly recommend reading this article on (1) SQL indexes although it is a bit detailed.
The short hand summary is that just because you add indexes doesn't mean Query Analyzer will use them. You need to use indexes intelligently. Note that this is different from the old "clustered vs non-clustered indexes" discussion.
Given your description it sounds like your tables are (2) "heap tables" anyway, so you probably don't want to use clustered indexes (do you have a lot of inserts and deletes regularly occuring?)
Having said that, in my experience it's not that hard to know when and where to use them, and in 80% of the cases a decent working knowledge of your application(s) and business logic will lead you to the right columns to index (and the right degree of uniqueness).
[ (1) http://www.sql-server-performance.com/articles/per/index_not_equal_p1.aspx ]
[ (2) http://www.mssqltips.com/tip.asp?tip=1254 ]