Is there any possible reason to have a non-clustered index on a table without a clustered index at all?

Question

Post reply

Is there any possible reason to have a non-clustered index on a table without a clustered index at all?

Vivien Xing

SSChampion

Points: 12297
More actions
September 17, 2008 at 8:56 pm

#131366

I have an existing old database; none of the tables has a clustered index, but each table has one non-clustered index instead. It is not a big database (about 10 GB). I could not ask why as the database design technical people are gone.
Does anyone have the similar case to share?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Rajesh kasturi SSCarpal Tunnel Points: 4007 More actions · Answer 1

Please let me know your database is OLTP or OLAP.

Rajesh Kasturi

dmoldovan SSCertifiable Points: 6334 More actions · Answer 2

It is a good practice to have a clustered key for each table. Kimberly Tripp offers all the explanations you need on her blog on sqlskills.com.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

If you do not delete data from the table, and the order in which the data is inserted cannot be anticipated, a heap like this will allow data to be added without fragmenting.

Usually, it is best to have an appropriate clustered index.

Vivien Xing SSChampion Points: 12297 More actions · Answer 4

Additional info, it is a reporting database. The fragmentation is very bad.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

is anybody going to ask if the indexes are unique or non-unique? 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Vivien Xing SSChampion Points: 12297 More actions · Answer 6

Vivien Xing

SSChampion

Points: 12297

September 18, 2008 at 8:55 am

#873661

non-clustered and unique.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

Vivien Xing (9/18/2008)
Additional info, it is a reporting database. The fragmentation is very bad.

Clustered indexes are most beneficial in range queries, ie. those including clauses (such as GROUP BY, ORDER BY, BETWEEN etc.) and in queries retrieving a large number of records.

This being a reporting database, clustered indexes on the appropriate columns would be a must.

No clustered indexes and high fragmentation: are your reports running at all? 😉

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Vivien Xing SSChampion Points: 12297 More actions · Answer 8

Good question. It is a reporting database. If no need to have a clustered index for whatever reason, why have a non-clustered index then? I remember it is a bad practice to have a non-clustered index on a heap table, if I remember right.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 9

There are reasons you might want to put a non-clustered index on a table that doesn't have a clustered index, for example if the primary key is a uniqueidentifier and there isn't another good candidate key to use, but you still want to improve performance on a reasonablly selective column used in WHERE clause of queries.

Since clustered indexes determine how the rows will be physically stored though, it may be beneficial to analyze how the data is being queried, such as mostly recent records with very little historical queries, or certain criteria that all queries against a particular table will always have. It's one advantage of having a clustered index on an identity field is that it will keep records toghether that are from the same relative timeframe, so that could lead to fewer pages having to be read from the disk into memory if most of the queries are non-historical.

Vivien Xing SSChampion Points: 12297 More actions · Answer 10

Thank you all for your response.

Cluster That Index! has detailed info about the "Problems with not having a clustered index". I can not find any article better than this one regarding this topic.

http://www.sqlservercentral.com/articles/Performance+Tuning/clusterthatindex/952/

Even for uniqueidentifier column, "This is where a clustered index on a uniqueidentifier column can help ..."

http://www.sql-server-performance.com/articles/per/clustered_indexes_p1.aspx

I cant let you do that Dave Right there with Babe Points: 783 More actions · Answer 11

If the db is incrementally near real time loaded, the reports are not ad-hoc or are slowly changing and the reporting performance is important; there is an argument for non-clustered covering composite indexes ordered by the most selective column predicates in the reports.

If it is batch/backup loaded or the reports are ad-hoc or mostly returning data in order of insert, unless there is a specific performance need, composite covering indexes tailored to the report predicates is an expensive man effort that may be better spent on new hardware.

FWIW