Shulou(Shulou.com)06/01 Report--

An index provides pointers to data stored in a specified column in a table, and then arranges the pointers according to the specified order, reaching the row containing the value according to the pointer.

What is an index?

An index in a database is similar to a catalog of data, and you can use the catalog to quickly find the information you need. In a database, an index is a collection of values for a column or columns in a table and a logical list of pointers to the data pages that physically identify those values. is SQL Server's internal way of organizing data, which allows you to quickly find data through indexes without scanning the entire table

Index page is the data page in the database that stores the index, the keyword page that stores the data row, and the address pointer of the data row

reference classification

In SQL Server, there are six common types of indexes:

Unique index: two rows are not allowed to have the same index value

Primary key index: Each value in the primary key is required to be unique

Clustered index: Data is stored in the same physical order as the index

Nonclustered index: Data is stored in a different physical order than the index

composite index: an index formed by combining multiple columns

Full-text indexing: A special type of tag-based functional indexing

Note: In SQL Server, only one clustered index can be created for a table, but multiple nonclustered indexes can be created. Set a column as a primary key, and the column defaults to a clustered index

create an index

There are two ways to create an index: using SSMS and T-SQL statements

(1) Creating an index using SSMS

You can expand the table and choose New Index, or you can right-click the table and choose Design, right-click the column and choose Index/Key to create an index

(2) Creating indexes using T-SQL statements

Create a clustered index Syntax:

create clustered index on table name (column name)

To create a nonclustered index Syntax:

create [nonclustered] index name on table name

creates a unique index

create unique [clustered| nonclustered] index name on table name (column name)

delete the index

drop index name on table name

Index selection principles

Indexing speeds data retrieval, but indexing every column is unnecessary. Because the index itself needs to be maintained and occupies certain resources, you can select the columns to establish the index according to the following criteria.

* Frequent searches, query selected columns

* Frequently sorted, grouped columns

* Columns frequently used for joins (primary key, foreign key)

Do not index with the following columns because indexing takes longer than searching rows in the table

* Columns with only a few values

* The table contains only a few rows

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.