Shulou(Shulou.com)05/31 Report--

This article mainly explains how to use indexes in SQL Server. Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's take a small series to show you how to use the index in SQL Server!

What is an index?

An index is an on-disk data structure based on tables or views. Using indexes can make data retrieval faster and higher, but it can also affect other performance, such as insertion or update.

There are two main types of indexes:

1. Clustered and nonclustered indexes

The dictionary directory is an index, according to Pinyin query want the word is clustered index (physical continuity, page number and directory one-to-one correspondence), radical is a non-clustered index (logical continuity, page number and directory discontinuous).

A clustered index stores records that are physically contiguous, whereas a nonclustered index stores records that are logically contiguous and physical storage is not contiguous.

There can be only one clustered index in a table, while there can be multiple non-clustered indexes in a table.

2. Advantages and disadvantages of indexing

Indexes are used to avoid full table scans, because full table scans read every page of the table from disk, and if there is an index pointing to the data value, only a few disk reads are required.

Indexed tables take up more space in the database, and the same commands to add, delete, and modify data take longer.

3. Storage mechanism of index

A table of contents in a book is a list of words and their page numbers, and an index in a database is a list of values in a table and where each value is stored.

A clustered index is a new physical space in the database that is used to store the values it arranges. When new data is inserted, it rearranges the entire physical storage space.

A nonclustered index contains only the columns of the nonclustered index in the original table and a pointer to the actual physical table.

Basic structure of data tables

When a new data table is created, the system allocates a contiguous segment of 8k space on disk. When an 8k is used up, the database pointer will automatically allocate an 8k space, each 8k space is called a data page, and the page number from 0 to 7 is allocated. The 0th page of each file records the guide information called the page header. Every 8 data pages are composed of 64k to form an extension area. The combination of all data pages forms a heap.

SQL Server specifies that rows cannot span data pages, so the maximum number of records per row can only be 8k, which is why char and varchar are limited to less than 8k character types. Data stored over 8k should use text type. In fact, field values of text type cannot be entered and saved directly. It stores a pointer to an extension composed of several 8k data pages. The real data is actually placed in these data pages.

Second, the trade-off of setting the index 1. Under what circumstances to set the index

Defines the data column for the primary key (sql server gives the primary key a clustered index by default).

Define data columns with foreign keys

For frequently queried data columns

For data columns that require frequent queries within a specified range

Data columns that often appear in where clauses

Fields that often appear after the keywords order by, group by, and distinct.

2. Under what circumstances do not set the index

Columns that are rarely involved in a query, columns with more duplicate values.

columns of text, image, bit data types

frequently accessed column

For tables that are frequently updated, the index should generally not exceed 3, and at most 5. Although it improves access speed, it will affect update operation.

3. Cluster index 1. Create a clustered index using SSMS

Expand the table you want to index-> Right-click Index-> Select New Index-> Clustered Index-> New Index Point Add-> Select Column-> Select Ascending or Descending-> Enter Name-> OK.

By default, a clustered index is automatically created with primary key generation.

2. Create clustered index_name /* clustered index name */on table_name( id desc)with(drop_existing=on); /* delete if existing */

Each table or view can contain only one clustered index because clustered indexes change the way data is stored and arranged.

Both clustered and non-clustered indexes store information in balanced trees or B-trees, which recognize similar data and group them together. It is because retrieval in B-trees is based on key values that indexes can speed up data access. B-trees combine those with similar keys, so the database engine only needs to search a small number of pages to find the target record.

IV. Non-clustered indexes

There can be multiple nonclustered indexes on each table, and you can create an index on a single column or on multiple columns that are already part of an existing index.

1. SSMS is created as above. T-SQL is created as follows: create nonclustered index fei /* clustered index name */on defualt( hits desc)2. Add index option

fillfactor: used to create an index, each index page data percentage of the index size, default 100. When you need to frequently modify the table, it is recommended to set to 70-80, not often updated 90.

5. Example create table ceshi --Create a new table ( id int identity(1,1) primary key, name varchar(20), code varchar(20), [date] datetime)--Insert 10w test data declare @n intset @n = 1while @n

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.