Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Reasons not to have a clustered index in SQL Server 2005

I've inherited some database creation scripts for a SQL SERVER 2005 database.

One thing I've noticed is that all primary keys are created as NON CLUSTERED indexes as opposed to clustered.

I know that you can only have one clustered index per table and that you may want to have it on a non primary key column for query performance of searches etc. However there are no other CLUSTERED indexes on the tables in questions.

So my question is are there any technical reasons not to have clustered indexes on a primary key column apart from the above.

like image 593
AJM Avatar asked Oct 27 '10 14:10

AJM


People also ask

What is the disadvantage of clustered index?

Disadvantages of Clustered IndexExtra work for SQL for inserts, updates, and deletes. A clustered index takes longer time to update records when the fields in the clustered index are changed. The leaf nodes mostly contain data pages in the clustered index.

Should you always have a clustered index?

Yes, every table should have a clustered index. The clustered index sets the physical order of data in a table.

When should we use non-clustered index?

If you want to select only the index value that is used to create and index, non-clustered indexes are faster. For example, if you have created an index on the “name” column and you want to select only the name, non-clustered indexes will quickly return the name.

When should indexes be avoided in SQL?

Indexes should not be used on small tables. Tables that have frequent, large batch updates or insert operations. Indexes should not be used on columns that contain a high number of NULL values. Columns that are frequently manipulated should not be indexed.


3 Answers

On any "normal" data or lookup table: no, I don't see any reason whatsoever.

On stuff like bulk import tables, or temporary tables - it depends.

To some people surprisingly, it appears that having a good clustered index actually can speed up operations like INSERT or UPDATE. See Kimberly Tripps excellent The Clustered Index Debate continues.... blog post in which she explains in great detail why this is the case.

In this light: I don't see any valid reason not to have a good clustered index (narrow, stable, unique, ever-increasing = INT IDENTITY as the most obvious choice) on any SQL Server table.

To get some deep insights into how and why to choose clustering keys, read all of Kimberly Tripp's excellent blog posts on the topic:

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Clustering-Key.aspx

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Clustered-Index.aspx

Excellent stuff from the "Queen of Indexing" ! :-)

like image 199
marc_s Avatar answered Oct 12 '22 02:10

marc_s


Clustered Tables vs Heap Tables

(Good article on subject at www.mssqltips.com)

HEAP Table (Without clustered index)

  • Data is not stored in any particular order

  • Specific data can not be retrieved quickly, unless there are also non-clustered indexes

  • Data pages are not linked, so sequential access needs to refer back to the index allocation map (IAM) pages

  • Since there is no clustered index, additional time is not needed to maintain the index

  • Since there is no clustered index, there is not the need for additional space to store the clustered index tree

  • These tables have a index_id value of 0 in the sys.indexes catalog view

Clustered Table

  • Data is stored in order based on the clustered index key

  • Data can be retrieved quickly based on the clustered index key, if the query uses the indexed columns

  • Data pages are linked for faster sequential access Additional time is needed to maintain clustered index based on INSERTS, UPDATES and DELETES

  • Additional space is needed to store clustered index tree These tables have a index_id value of 1 in the sys.indexes catalog view

like image 6
Branimir Avatar answered Oct 12 '22 02:10

Branimir


Please read my answer under "No direct access to data row in clustered table - why?", first. Specifically item [2] Caveat.

The people who created the "database" are cretins. They had:

  • a bunch of unnormalised spreadhseets, not normalised relational tables
  • the PKs are all IDENTITY columns (the spreadsheets are linked to each other; they have to be navigated one-by-one-by-one); there is no relational access or relational power across the database
  • they had PRIMARY KEY, which produce UNIQUE CLUSTERED
  • they found that that prevented concurrency
  • they removed the CI and made them all NCIs
  • they were too lazy to finish the reversal; to nominate an alternate (current NCI) to become the new CI, for each table
  • the IDENTITY column remains the Primary Key (it isn't really, but it is in this hamfisted implementation)

For such collections of spreadsheets masquerading as databases, it is becoming more and more common to avoid CIs altogether, and just have NCIs plus the Heap. Obviously they get none of the power or benefits of the CI, but hell, they get none of the power or benefit of Relational databases, so who cares that they get none of the power of CIs (which were designed for Relational databases, which theirs is not). The way they look at it, they have to "refactor" the darn thing every so often anyway, so why bother. Relational databases do not need "refactoring".

If you need to discuss this response further, please post the CREATE TABLE/INDEX DDL; otherwise it is a time-wasting academic argument.

like image 1
PerformanceDBA Avatar answered Oct 12 '22 03:10

PerformanceDBA