5 ways SQL Server creates an index

Quoted https://www.cnblogs.com/JiangLe/p/4007091.html

Pre-Preparation:

CREATE TABLE Employee (
ID int NOT null primary key,
Name nvarchar (4),
credit_card_id varbinary (max)); ---Be careful with this data type.
Go

Description: The index on this table will be deleted before the next index is created.

--------------------------------------------------------------------------------------------------------------- ----------------------------------------------

Operation 1,

Create a clustered index

Method 1,

ALTER TABLE TABLE_NAME ADD constraint Cons_name Priamry key (ColumnName asc| Desc,[...]) With (drop_existing = on);

ALTER TABLE Employee
Add constraint Pk_for_employee primary key clustered (ID);
Go

This is a special method because the index is automatically added when the primary key is defined, but whether the clustered index or nonclustered index is the one we can control.

The index in the table can be viewed by sp_helpindex

Execute sp_helpindex @objname = ' Employee ';

Go

Attention

This index is not deleted, do not believe! You have to delete it.

Drop index employee.pk__employee__3214ec277d95e615;

Go

Method 2,

Create clustered index ix_name on table_name (columnName asc| desc[,......]) With (drop_existing = on); method

Create clustered index ix_clu_for_employee_id on employee (ID);

Go

To view the created index

Operation 2,

To create a composite index

Create INDEX Ix_com_employee_idname on Employee (Id,name) with (drop_existing = on);

This is to create a composite index, but the foot of the road is very long, we look at the next composite Index example:

Create INDEX Ix_com_employee_idcreditcardid on Employee (id,credit_card_id);

See this sentence, you first ask yourself if it is wrong!

Can find it wrong, varbinary is not indexed.

Operation 3,

Create an overlay index

CREATE INDEX index_name on table_name (columnName asc| desc[,......]) Include (Column_name_list) with (drop_existing = on);

Create INDEX Ix_cov_employee_id_name on Employee (ID) include (Name);
Go

First, overwrite the index it's just a special form of a nonclustered index, and the nonclustered index below does not contain an overwrite index, which is, of course, only applicable to this passage.

The purpose is to illustrate the differences in each.

First of all:

1. A nonclustered index does not contain data, and it finds only references to data rows in the file (in the case of a heap) or a reference to a clustered index, SQL Server

To use this reference to find the corresponding data row.

2. The second lookup is raised because the nonclustered index does not have data.

3, overwriting the index is to add data to the nonclustered index, so that there is no need to find a second time. This is a way to change the performance of a space. Nonclustered indexes are also.

Just do it without it so out of bounds.

Operation 4,

Create a unique index

Create unique index index_name on table_name (column asc| desc[,.....]) With (drop_existing = on);

As I said earlier, before I create an index on a table, I delete all the indexes on the table, and here's why I want to say it again! Because I'm afraid you forgot. Secondly, this example is used to it.

The current table is an empty table, and I add two rows of data to it.

Insert into Employee (id,name) VALUES (1, ' AAA '), (1, ' BBB ');

So let's add a unique index to the table, which is defined on the ID column

Create unique index ix_uni_employee_id on Employee (ID);
Go--it can be thought that because the ID is duplicated, it can't be created.

Conclusion 1, if there is a duplicate value on the column, it can not be defined on this column, a unique index.

Below we empty the table: Truncate TABLE Employee;

The next thing to do is to create a unique index and then insert duplicate values.

Create unique index ix_uni_employee_id on Employee (ID);
Go

Insert into Employee (id,name) VALUES (1, ' AAA '), (1, ' BBB ');

Go

Conclusion 2.

Duplicate values cannot be inserted on corresponding columns after a unique index is defined.

Operation 5,

Filter indexes

CREATE INDEX index_name on table_name (columname) where boolexpression;

Create INDEX ix_employee_id on Employee (ID) where id>100 and id< 200;
Go

Only the hotspot data is indexed, and if a large number of queries are interested only in data with IDs from 100 to 200, you can do so.

1, you can reduce the size of the index

2. Improve query performance for data base.

Summarize:

BTree indexes have aggregation and non-aggregation points.

It is better to view top-to-clustered index performance than nonclustered index performance.

Nonclustered index sub-overlay index, unique index, composite index (of course, clustered index also has compound, compound two words, just explain index, reference Multi-column), General nonclustered index

It is better to view the performance of the overlay index in the nonclustered index than the performance of other nonclustered indexes, which is similar in performance to the clustered index, but

It is also not a ' silver bullet ' and it will use more disk space.

Finally, say this.

With (drop_existing = On|off), plus this means that if the index is still dropped on the table then the create a new one. Especially on the clustered index.

Using this will not cause a nonclustered index to be rebuilt.

With (online = on|off) The user can also access the data in the table when the index is created.

With (Pad_index = On|off fillfactor = 80); FILLFACTOR is used to set the fill percentage, Pad_index is only used to connect fillfactor but it's not hard to

There is no language in this.

With (Allow_row_locks = On|off |  Allow_page_locks = on |off); Whether to allow page locks or row locks

With (data_compression = row | page); This will compress the index size

5 ways SQL Server creates an index