Clustered indexes

kriki

Some links to clustered indexes.

http://www.sqlmag.com/article/articleid/92886/sql_server_blog_92886.html
http://www.sqlskills.com/blogs/kimberly/2007/02/02/TheClusteredIndexDebateContinues.aspx
http://www.sqlskills.com/blogs/kimberly/2005/06/06/EverincreasingClusteringKeyTheClusteredIndexDebateagain.aspx
http://www.sql-server-performance.com/indexes_not_equal.asp

I invite all who have other links to info about clustered index to add it to this topic. So all info about them stay together.

Joe_Mathis

SQL Server 2005 Books Online
Clustered Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms190639.aspx

I don't know if you need a subscription to view but it's a good description.

Joe

kriki

jondoba wrote:

I don't know if you need a subscription to view but it's a good description.

Just checked. You don't need a subscription.

colin_leversuch-roberts

Kimberley is a supporter of sequential keys on all tables ( surrogate keys ) this is a contentious area , ask Joe Celko. There are plenty of people who don't support this view - as always however "it just depends" and you should investigate what's best for you rather than following a posted article or blog blindly.
I've seen some real issues caused by poor choice of clustered indexes.

Joe_Mathis

I don't think we have much of a choice on whether or not we get a clustered index... :shock:

According to the SQL Server Resource Kit (from SQl Tools CD)
(pg 9)

How Indexes Work in Navision
• All the indexes in Navision are unique. Note that in the earlier example, the index is Customer No., Posting Date, Currency Code, Entry No. to enforce uniqueness.
• A primary index in Navision translates to a unique clustered index on SQL Server and a secondary index in Navision translates to unique non-clustered index in SQL Server.
• Navision Database Server supports SIFT effortlessly. However, in the SQL Server Option, when a SIFT field is defined on any index an extra table is created on SQL Server. This table is maintained by triggers that have been placed on the source data table. Based on the earlier example, Navision creates a new SIFT table on SQL Server for the source data table. The source data table is called CRONUS International Ltd_$Cust_ Ledger Entry and the SIFT table is called CRONUS International Ltd_$21$0...

I don't really care if this is good or bad because I cannot change it, Navision will have to do that. I also agree that the primary key is unique and should be a good key to cluster if there has to be one.

What got me scratching my brain :-k was the following (pg 14)

SQLIndex Key Property
Navision contains a key property, called SQLIndex that allows you to create indexes on SQL Server that are different from the keys defined in Navision.
This property is designed to address selectivity issues by allowing you to optimize the way that Navision uses SQL Server indexes without having to rewrite your application or redesign your keys in Navision.
...

Now the question is... If, in order to get a higher selectivity on my index I want to change my SQLIndex for Sales Header table (36) Primary Key from "Document Type","No." to "No.","Document Type" ,then why didn't they do it? I see that they did it for some keys in 5.0...

Anyways....
The reason for the post was to provide link to SQL Server Resource Kit.
Which has information mainly on SQL that pertains to Navision.
http://www.mibuso.com/dlinfo.asp?FileID=561
Is this the latest version?

Joe

kriki

jondoba wrote:

I don't think we have much of a choice on whether or not we get a clustered index... :shock:

Actually, you have. In the keys, you have also a field to designate the clustered index (since 4.00SP1 I think). You can remove the toggle from the field.

colin_leversuch-roberts

The ability to use "sql indexes" as against "navision indexes" is something I'm looking at and hopefully will start testing soon. The navision process of adding the PK to the secondary indexes to make them unique is actually a rather strange design as it actually means you cannot have a unique index, if you want to index postcode ( zip ) to make it unique you can't, as adding the PK as extra columns to the index actually means the postcode column cannot be unique, ever. However, that's another discussion - what concerns me is that where the PK is several columns wide it actually makes the secondary index very large, for instance I have a table of 10gb size with a single index on a datetime column, because navision adds the PK, which on this table is wide, the index is actually 8gb, for suporting less a gb of data.
The clustered index physically orders the data on disk, so a poor choice may cause table fragmentation which may increase locking and degrade performance - the fragmentation causes selection from parts of the table ( scans rather than seeks ) to use more page io ( as the fragmented table takes more space ) and thus locking may escalate more often, you can produce the same effect by introducing fill factors.
However a clustered index can improve queries by ordering the data, a clustered index will usually be used by the optimiser with lower selectivity, however the optimiser will default to the clustered index if it feels there is no appropriate secondary index ( size, pages, io,locks and selectivity impact this choice - hence navision adding PK columns to all secondary indexes may cause the optimiser to not use an index )
If you want to use partitioning then the choice of clustered index is important. It's a very complex subject area and usually relevent to your database and application.

kriki

colin leversuch-roberts wrote:

The navision process of adding the PK to the secondary indexes to make them unique is actually a rather strange design

It is possible to override this behaviour. In the index, there is a property SQLIndex in which you can define the fields SQL must use to create its index.

Joe_Mathis

Actually, you have. In the keys, you have also a field to designate the clustered index (since 4.00SP1 I think). You can remove the toggle from the field.

I tried it (of course on a test system) and nope... not the way it works.

You can view your active keys by opening a SQL query and using

sp_helpindex [TestCompany$Sales Header]

With the toggle on or off you will get the Primary Key as a "clustered, unique, primary key located on Data Filegroup 1".

If you toggle it on another key then that key will become your clustered key. (Side Note... Don't do this unless you REALLY REALLY know what you are doing or like me are in a test system that no one cares about, and not just a test company but a separate Database)

Also with the toggle off, if you add a SQLIndex it will not change (at least on the PK). If you change the SQLIndex for the PK to No., Document Type and toggle the Clustered Index on however, the keys will re-arrange into that order. Once they re-arrange if you toggle it off it will remain until you remove the SQlIndex.

I changed the SQLIndex for the PK to No., Document Type and saved it just to see what the difference would be...

Using

dbcc show_statistics ([TestCompany$Sales Header],[TestCompany$Sales Header$0])

In my system that changed it to:
All density Average Length Columns
0.0007524454 9.762227 No_
0.0007524454 13.76223 No_, Document Type

From
All density Average Length Columns
0.25 4 Document Type
0.0007524454 13.76223 Document Type, No_

Now this isn't a very big database... but I believe that the lower the density the better right? So going to a .00075 (1/1329 of the records) from .25 (1/4 of the records) is more selective, and faster.

This should be true for a read, an update, and a delete right? I don't think it would matter too much for an insert...

Just some food for thought.

Joe

colin_leversuch-roberts

just to clarify, as sometimes it's not clear:
Your PK may be clustered or non clustered
A clustered index may be unique or non-unique
The key importance of index selectivity selection is the selectivity of the first column in the index.
non clustered indexes have to be more selective ( typically >90% ) for the optimiser to use them.
clustered indexes can be considerably less selective and the optimiser will still use them.
Clustered index physically sorts your table data in the order of the key.
====
technically all indexes work best for inserts if you add new entries at the top of the index,e.g. like a heap.
Inserts which occur through the range of the index will cause fragmentation, you can attempt to offset this with fill factors but this may actually make things worse.
Deletes need indexes too - many people forget this. Deletes leave holes/space in indexes which you resolve by index rebuilding.
Updates are a whole different matter, table triggers alter how updates work on a table. Updates generally cause the biggest problem.

Joe_Mathis

colin leversuch-roberts wrote:

just to clarify, as sometimes it's not clear:
Your PK may be clustered or non clustered

According to Microsoft Business Solutions–Navision SQL Server Option Resource Kit pg 5...

A primary index in Navision translates to a unique clustered index on SQL Server and a secondary index in Navision translates to unique non-clustered index in SQL Server.

In a Non-Navision SQL database you are correct, you do not have to have a clustered index.
In Navision you must, as it creates it for you by default. Even if it's toggled off.