SqlServer Series Notes-Index
What is an index?
An internal table structure used by SQL Server that is based on the values of one or more columns in the table
Provide an access mechanism for quick access to tabulation
Full table scan: data retrieval (select) the least efficient is full table scan, that is, searching one by one
Advantages of indexing
Increase the speed of accessing data pages
Speed up the query of data
Type of index
Clustered index
Nonclustered index
.
If there is no catalogue, you have to look up the Chinese dictionary page by page, and if you have a catalog, you only need to look up the catalog.
To improve the speed of retrieval, you can add indexes to columns that are retrieved frequently, which is equivalent to creating a directory
.
How to create the index, right-click in the table designer and select "Index / key" → to add → to select the column contained in the index.
Using indexes can improve query efficiency, but indexes also take up space.
And when adding, updating and deleting data, you also need to update the index synchronously.
Therefore, it slows down the speed of Insert, Update, and Delete. Indexes are created only on fields that are frequently retrieved (Where).
(*) even if an index is created, it is still possible to scan a full table, such as like, functions, type conversions, and so on.
Create using CREATE INDEX
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index name ON table name (column name)
Delete an index using DROP INDEX
The following statement removes an index from the test01 and test02 tables in the sample database:
USE sample
DROP INDEX test01.index_1, test02.index_1