What's the difference between clustered index and nonclustered index?
The clustered index is almost synonymous with the primary key. In fact you can only use one per table and have to be in the order of data insertion, this obviously excludes natural primary keys. At such an index the key is the position where the die is. In fact it is the table itself .
The primary key is obviously in order, thus enabling binary search that is very important for performance.
The non clustered index index are all other indexes where you will have any keys and a pointer to the data table. So there are always two polls, one in the non-cluster index and then knowing what his position will get the data actually in the table, which can even be a clustered index .
A non-clustered index has the keys in order as well and can do binary search in the same way.
On what occasions should I use one and the other?
So you do not have much secret in the choice. The primary key is almost always the clustered , and it does not have other indexes as clustered .
It is important to note that the use of clustered index is not mandatory. Even the primary key can use a normal key that will point to the data table.
It has a database (SQL Server) that allows the use of clustered natural key . SQL Server does not even require the primary key to be unique because it guarantees uniqueness at some extra cost. In SQL Server the only difference between the two is the guarantee of the presence of all columns in the clutter , it is a common binary tree. There is controversy as to whether this is a clustered index . My theory is that it was initially clustered , then found it best to make the table be ordered anyway, they changed the concept but not the terminology. So you have to be careful to use implementation details to define something.
As an optimization a non clustered index can count on some extra columns if that search usually only needs to access a few specific columns, eliminating the need to search the data table. This works in well thought out cases. Nothing prevents all indexes from having all columns and so any query can be done without going to the data table, but from the point of view of space and data update this is insane. >
Most commonly, indexes are stored in some form of binary tree structure (there are several), but this is not required. The clustered index can use a simplified tree or even not have a tree since it can only do append in the table and will never change the order. This is an implementation detail.
Can I say that a clustered index is performed a binary query, and a nonclustered index is used a query with Tree B?
The query is 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 for facilitating insertions in any order or in a distributed way across several points of the structure, enabling quickness in every type of operation.
Understand that creating the clustered index does not create any extra structure, it only establishes how to store the table.
Conclusion
For general use I think it is sufficient to know this. Details will only be useful to anyone who is going to implement a database or who needs to understand the internals of a DB for something very out of the ordinary use.
