Get the App
SLTechnology News&Howtos  ›  Database  › 

SqlServer Series Notes-Index

Shulou Source: shulou.com Published: 2022-06-01 15:25:33 11月17日 Update

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

Tags: Index data directory speed retrieval query efficiency type update selection worst advantage function multiple dictionary field that is database way time Apple Docker Huawei Linux macOS MariaDB Microsoft MySQL NVidia OPPO Reno MariaDB Linux OPPO Reno MySQL Xiaomi