SQL Server index-clustered index, nonclustered index, nonclustered unique index < eighth >

Clustered index, nonclustered index, nonclustered unique index

We all know that establishing an appropriate index can improve query speed and optimize queries. First, whether a clustered or nonclustered index is a B-tree structure .

• The clustered index matches the primary key by default, and when you set the primary key, SQL Server creates the clustered index in the primary key column by default. However, you can manually change to create a clustered index on either column, and then define a primary key on another field or multiple fields. The primary key is then created as a unique nonclustered index (a unique index). You can do this by specifying the nonclustered keyword.

  CREATE TABLE mytablekeyexample{Column1 int IDENTITY  KEY nonclustered, Column2 int}

• The clustered index actually loads the data record rows of SQL Server, and the leaf level of the clustered index is the data row, so the leaf level reaching the clustered index reaches the data.
• When you declare a PRIMARY KEY or UNIQUE constraint for a table, SQL Server automatically creates a unique index corresponding to it.
• The column of the clustered index is best to be self-increasing, because, according to the theory of the section-page, if the clustered index column is self-increment, then when the data is added, the view is placed at the end of the index, and there is no intermediate insert, so that the page split is not caused. And if the index column is not self-increasing, when adding data, it is also possible to find the location of the record in order, and insert it, which can often cause page splits if it is inserted more frequently.
• The best data types for clustered indexes, smallint, int, bigint, datetme.
• It is best to avoid combining clustered indexes.

Index is another important, user-defined data structure that is stored in the database, in addition to tables. When searching for data based on the value of the index code, the index provides quick access to the data. In fact, there is no index database that can successfully retrieve results from a table scan based on a SELECT statement, but as the table gets larger, the effect of using an "appropriate" index becomes more pronounced. However, if the index is used without careful consideration of its implementation, the index may degrade the performance of the database. The clustered index is automatically created when the primary key is created, unless the current table already contains a clustered index or the nonclustered keyword is specified when the primary key is created.

　　Clustered index, nonclustered index, nonclustered unique index:

SQL Server provides two kinds of indexes: clustered and nonclustered indexes. The function of aggregation is to change the physical order of a column (or columns) to match the logical order.

　　The difference between a clustered index (CLUSTERED) and a nonclustered index (nonclustered):

In fact, the body of our Chinese dictionary itself is a clustered index (in alphabetical order) For example, we want to check the word "Ann", it will be very natural to open the first few pages of the dictionary, because "ann" Pinyin is "an", and alphabetical order of Chinese character Dictionary is the English letter "A" begins with "Z" At the end, the word "Ann" naturally ranked in the front part of the dictionary. If you have turned over all the parts that begin with "a" and still cannot find the word, then it means that you do not have the word in your dictionary, and if you look up the word "Zhang", you will also turn your dictionary into the last part, because the pinyin of "Zhang" is "Zhang". That is, the body part of the dictionary is itself a directory, and you do not need to look up other directories to find what you need to find. We refer to this body of content itself as a directory of certain rules, called a "clustered index."   This is why a single table can have only one clustered index. Because a table can only be sorted in one way.   The leaf-level node where the clustered index is data. You can understand that there are a lot of trains, which are indexed by the locomotive, followed by data.
If you know a word, you can quickly find it from the auto. But you may also encounter the words you do not know, do not understand its pronunciation, at this time, you can not follow the method to find the word you want to check, and need to go to the "radicals" to find the word you are looking for, and then according to the page number after the word directly to a page to find the word you are looking for. But the sort of words you find in combination with the "radicals" and "gept" is not really the sort method of the body, for example, you check the word "Zhang", we can see in the Gept table after the Radicals "Zhang" page number is 672 pages, gept table "Zhang" above is "Chi" word, but the page number is 63 pages, "Zhang" below is "crossbow "Word, page 390. Obviously, these words are not really in the "Zhang" the word of the upper and lower side, now you see the continuous "Chi, Zhang, crossbow" three words is actually their order in the nonclustered index, is the dictionary body of words in the non-clustered index mapping. We can find the words you need in this way, but it takes two procedures to find the results in the catalog and then turn to the page numbers you need. We put this kind of directory purely as a directory, the body is purely the sort of body is called "nonclustered index".

　　In fact, if there is a clustered index on the table, a reference to the clustered index is stored in the nonclustered index, and then the data is fetched by leveraging the clustered index. so this is why it is better to use a nonclustered index when returning a small amount of data, and a nonclustered index is not as fast as a direct full table scan when returning large amounts of data. If there is no clustered index on the table, the row number is referenced.

Nonclustered indexes require additional space to be stored, clustered by indexed columns, and a leaf node in the B-tree contains pointers to the table where the nonclustered index resides. A nonclustered index is also a B-tree structure, and another separate B-tree.

　　Unique index:

If the primary key is not set to a clustered index when it is created, it must be a unique nonclustered index. In fact, the unique index, the name Incredibles, is that it requires the value on that column to be unique.

Clustered index columns can be duplicated, and nonclustered index columns can also be duplicated. That's why there's only one thing. The syntax for declaring a unique index is simple, but with a unique keyword.

CREATE UNIQUE nonclustered INDEX [ak_product_name] on production.product ([Name]);

Unique indexes have many limitations and attributes. Learn more about the unique index below.

When you declare a PRIMARY KEY or UNIQUE constraint for a table, SQL Server automatically creates a unique index corresponding to it. When you define a unique constraint, SQL Server automatically creates a unique index with the same name, and you must first delete the constraint to delete the index. However, deleting a constraint also causes the index associated with it to be deleted, that is, the unique index cannot be deleted, and the only way to remove the index is to remove the unique constraint.

　　A unique index relies on a unique constraint, and deleting a unique index must remove a unique constraint. In addition, SQL Server establishes a unique index by default when it establishes a unique constraint.

The sum up is that the unique index always exists with the unique constraint.

Because defining a primary key or defining a constraint causes the index to be created, you must give the necessary index information when the constraint is defined, so the ALTER TABLE statement above contains the "CLUSTERED" keyword.

ALTER TABLE production.product ADD CONSTRAINT pk_product_productid PRIMARY KEY CLUSTERED (ProductID);

If the column constrained by a unique index or constraint already contains duplicate values in the current table, creating the index will fail. When the unique index is created successfully, all INSERT and UPDATE statements that violate this constraint will fail.

There is no significant difference between a unique constraint and a unique index. There is no difference in how data is validated by creating independent unique indexes and using unique constraints. The query optimizer also does not differentiate whether unique indexes are created by constraints or created manually. However, with the goal of data integrity, it is better to create constraints, which make the target of the corresponding index at a glance.

Filtering a unique index allows you to use this when we need to allow multiple null values and not allow duplicates:

CREATE UNIQUE nonclustered index xx on Productdemo (< index column >)--Specify index column where < indexed column >!=null)--Filter condition

For a unique index created with the syntax above, when inserted, duplicates are detected only if the unique index column is not NULL. In other words, the index column of the above table allows multiple null values.