What is the difference between clustered index and nonclustered index?

Question

Studying about found a example, and about clustered says:

Primary Keys by default use a grouped Dice (clustered), that is, when conducting a consultation select * from myTable where IDColumn = 1 , the bank will perform a binary search to find this element, instead of a line-by-line scan.

I would like a more detailed opinion on the subject, if possible with consultation plans or examples in practice.

• What is the difference between clustered index and nonclustered index?
• On what occasions should I wear one and the other?
• I can say that a grouped index is held a binary consultation, and an unassembled index is used a query with Tree B?

Note: My English is not very good, the question I lined up helped me a little, but not much. I found very interesting the consultation plans that the bank makes, until then unknown.

Answer 1

What is the difference between clustered index and nonclustered index?

The index clustered is almost a synonym of primary key. In fact can only use one per table and has to be in the order of entering the data, which obviously excludes natural primary keys. At an index so the key is the position where the data is. Actually he is the very table.

The primary key is obviously in order, thus enabling binary research that is very important for performance.

The index does not clustered are all other indexes where you will have a key any and a pointer to the data table. So there are always two searches, one in the index non-cluster and then knowing his position will search for the actual data in the table, which may even be an index clustered.

A no index clustered has the keys in order too and can do binary search in the same way.

On what occasions should I wear one and the other?

So it doesn’t have much of a secret in choice: the primary key is almost always the clustered, and there is no way other indexes are clustered.

It is important to note that it is not mandatory to use index clustered. Even the primary key can use a normal key that will point to the data table.

Has database (SQL Server) that allows use of natural key clustered. SQL Server does not even require the primary key to be unique because it ensures uniqueness at some extra cost. In SQL Server the only difference between the two is ensuring the presence of all columns in cluttered, it is a common binary tree. There are controversies if this is an index clustered. My theory is that initially it was clustered, then they thought it best to have the table sorted anyway, changed the concept but not the terminology. That’s why you have to be careful about using implementation details to define something.

How to optimize an index not clustered can count on some extra columns if that search usually only needs to access some specific columns, eliminating the need to search in the data table. This works in thoughtful cases. Nothing prevents all indexes having all columns and so any query can be done without having to go to the data table, but from the point of view of space and data update this is insane.

The most common is that the indices are stored in some form of binary tree structure (has several), but this is not required. The index clustered you can use a simplified tree or even not have a tree as you can only do append table and will never change the order. This is an implementation detail.

I can say that a grouped index is performed a binary query, and an unassembled index is used a query with Tree B?

The query being binary has nothing to do with the structure of the data, except for the fact that this structure must be classified.

A tree structure is useful to facilitate the insertions in any order or in a distributed way through several points of the structure allowing speed in all types of operation.

Understand that the creation of the index clustered does not create any extra structure, just establishes how to store the table.

Completion

For general use I think it is enough to know this. Details will only be useful to those who will implement a database or who need to understand the Internals from a DB to something far out of normal use.

Answer 2

Marconi, the example you cite specifically deals with SQL Server.

What is the difference between clustered index and nonclustered index?

The basic difference between index clustered (grouped) and nonclustered (unbundled) is that in the indices clustered, the structure of the index and the data are in the same file; hence the term clustered (grouped). They are two structures implemented in the same file. And, in the case of indexes nonclustered, these are not grouped with the data, that is, they are in separate files.

In SQL Server the indexes (both clustered how much nonclustered) are implemented using b tree+. Direct research (Seek) is made walking in the tree, until it reaches a leaf node. In the case of sequential reading (scan), occurs directly at sheet level as there is a doubly chained list at that level. If the index is of type clustered, the data is in the sheet node. If it is of type nonclustered, there’s a pointer (Row Locator) indicating where the data are, in the table.

In the indexes clustered lines are kept ordered logically. To understand the implementation, I suggest reading the item Table and Items Structures, pages 188 to 197 of the book Inside Microsoft SQL Server 2008: T-SQL Querying, by Itzik Ben-Gan.

---

On what occasions should I wear one and the other?

For this question there is no single answer, because it depends on the context and, mainly, it is necessary to understand the concepts of natural key, primary key, substitute key etc. This concept you find in the article Primary Key Primer for SQL Server, phil Factor.

According to the previously mentioned article, primary key and index clustered are different things. A primary key is a logical construct and an index clustered is an index with a special physical implementation. When defining index clustered for a key, you determine how the key is implemented.

And the author also points out that the choice of the index clustered can have a strong impact on performance. The candidate key that makes sense as a primary key may not have the characteristics that are required for an index clustered with good performance. A good index clustered is light and easy to make comparisons with it. A good primary key is not always so.

Although by default (default) in SQL Server the primary keys are implemented using index of type clustered, this is not required. You can implement primary key using index nonclustered. Or even have no index for the primary key, but only a statement of uniqueness (Unic).

The choice of index clustered or nonclustered depends on the context. The following tasks make up the recommended index creation strategy:

• Understand the characteristics of the database;
• Understand the characteristics of the most used queries;
• Understand the characteristics of columns used in queries;
• Determine which index options could increase performance in creation or maintenance of the index;
• Determine the best storage location for the index.

---

I can say that a grouped index is performed a binary query, and an unassembled index is used a query with Tree B?

From the initial answer, you already know there’s no relationship.

---

In accordance with SQL Server Index Creation Guide, an index is a disk structure associated with a table, which speeds the recovery of the rows. An index contains keys created from one or more table columns. These keys are stored in a structure (B tree) that enables SQL Server to locate the line or lines associated with the key values quickly and effectively.

In the document Clustered and ungrouped indices described is that grouped indexes classify and store the table data rows based on their key values.

---