SQL Server Index

The basic unit of data storage in SQL Server is the page. The disk space allocated by the data file (. mdf or. ndf) in the database can be logically divided into pages (numbered from 0 to n consecutively). Disk I/O operations are performed at the page level. That is, the smallest unit of data that SQL Server reads or writes to data every time is the data page.

Purpose of the Index

Our data query and processing speed has become a measure of the success of the application system standards, and the use of indexes to speed up data processing speed is often the most commonly used optimization method.

What is an index

The index in the database is similar to that of a book, and using a table of contents in a book allows you to quickly find the information you want without having to read it. In a database, a database program uses an index to locate data in a table without having to scan the entire table. The catalog in the book is a word and a list of the page numbers where the words are, and the indexes in the database are the values in the table and a list of where each value is stored.

The pros and cons of indexes

most of the overhead of query execution is I/O, and one of the primary goals of using indexes to fetch high performance is to avoid full table scans, because full table scans need to read every data page of a table from disk, and if an index points to a data value, then the query needs to read the disk a few times. therefore, reasonable use of the index can speed up the data query. However, indexes do not always improve the performance of the system, and indexed tables require more storage space in the database, as well as the time required to delete and remove data, and to maintain the index for longer processing time. So we should use the index reasonably, update the sub-optimal index in time.

Classification of indexes

There are multiple index types in SQL Server.

By storage structure: " clustered Index (also known as Cluster index, cluster index)", "Nonclustered index (nonclustered index, non-clustered index)"

Differentiated by Data uniqueness: " unique index ", " non-unique index "

The number of key columns is distinguished: " single column Index ", " Multi-column index ".

Clustered index

　　A clustered index is a sort of re-organization of actual data on disk to be sorted by a specified column or columns of values. like the Chinese dictionary we used, is a clustered index, such as to look up "Zhang", we naturally turn to the back of the dictionary Baishi page. Then follow the alphabetical order to find out. Here, Microsoft's balanced binary tree algorithm, that is, the first to turn the book to about One-second of the position, if you want to find the page number is smaller than the number of pages, the book forward to One-fourth, otherwise, the book back to Three-fourths of the place, and so on, the pages continue to be divided into smaller parts, until the correct page number.

　　because a clustered index is a sort of data, it is impossible to have multiple rows, so a table can only build one clustered index. Scientific statistics establishing such an index requires at least an additional space equivalent to that of table 120% to hold a copy of the table and an index intermediate page, but his performance is almost always faster than other indexes.

Because the data is physically arranged on the data page in the clustered index, the duplicate values are also sorted together, thus containing a range check (bentween,<,><=,>=) or a query using group by or order by, once the row for the first key value is found, The back will all be linked together, without further searching, avoiding a wide range of scans that can greatly improve query speed.

Nonclustered indexes

　　SQL Server creates an index that is nonclustered by default, does not reorganize the data in the table, but instead stores the indexed column values on each row and points to the page where the data resides. He looks like a Chinese dictionary based on the ' radicals ' to find the word, even if the data is not sorted, but he has a directory more like a directory, the efficiency of data extraction is also a room for improvement, without the need for a full table scan.

A table can have multiple nonclustered indexes, and each nonclustered index provides a different sort order based on the different indexed columns.

Fill factor

An attribute of the index that defines the amount of free space on each page of the index. The FILLFACTOR (fill factor) adapts to the subsequent expansion of table data and reduces the likelihood of page splits. The fill factor is a percent value from 0 to 100, and when set to 100 means that the data page fills up. This setting is only used when no changes are made to the data, such as in a read-only table. The smaller the value, the greater the amount of free space on the data page, which reduces the need for page splitting during the index growth process, but requires more hard disk space to be consumed. Improper fill factor designation reduces the read performance of the database, which is inversely proportional to the fill factor setting value.

CREATE INDEX syntax

CREATE [UNIQUE] [clustered| Nonclustered]index index_name on {table | view} (column [ASC | DESC] [,... n]) [With[pad_index][[,]fillfactor=fillfactor][[,]ignore_dup_key][[,]drop_existing][[,]statistics_ norecompute][[,]sort_in_tempdb]][on filegroup]

The CREATE INDEX command creates indexes for each parameter description as follows:

Unique: Used to specify that a unique index is created for a table or view, that is, two rows with the same index value are not allowed.

CLUSTERED: Used to specify that the index created is a clustered index.

Nonclustered: Used to specify that the index created is a nonclustered index.

Index_name: Used to specify the name of the index being created.

Table: Specifies the name of the table on which the index is created.

View: Specifies the name of the view that created the index.

asc| DESC: Used to specify an ascending or descending sort direction for a specific index column.

Column: Used to specify the columns to be indexed.

PAD_INDEX: Used to specify the space that remains open on each page (node) in the intermediate level of the index.

FILLFACTOR = FILLFACTOR: Used to specify that the data for each index page is a percentage of the index page size when the index is created, and the value of FILLFACTOR is 1 to 100.

Ignore_dup_key: Used to control the response of SQL Server when inserting duplicate data into a column that is contained in a unique clustered index.

Drop_existing: Used to specify that a named pre-existing clustered index or nonclustered index should be dropped and recreated.

Statistics_norecompute: Index statistics used to specify expiration are not automatically recalculated.

SORT_IN_TEMPDB: Used to specify that intermediate sorting results are stored in the TEMPDB database when the index is created.

ON filegroup: Used to specify the filegroup to hold the index.

Example

--Table Bigdata Create a nonclustered index named Idx_mobiel, index field mobielcreate index Idx_mobielon bigdata (Mobiel)--table Bigdata create a named Idx_ Unique clustered index of ID, index field id--requires that duplicate values be ignored when inserting data in batches, no recalculation of statistics, a fill factor of 40create unique clustered index Idx_idon bigdata (ID) with PAD_INDEX, Fillfactor=40,ignore_dup_key,statistics_norecompute

Manage Indexes

EXEC sp_helpindex bigdata   --view index definition exec sp_rename ' bigdata.idx_mobiel ', ' Idx_big_mobiel '  --index name from ' Idx_mobiel ' Change to ' idx_big_mobiel ' drop index Bigdata.idx_big_mobiel  --delete Idx_big_mobiel index in bigdata table DBCC SHOWCONTIG (BIGDATA,IDX _mobiel)--Check the fragmentation information for index Idx_mobiel in the Bigdata table DBCC INDEXDEFRAG (Test,bigdata,idx_mobiel)  --Organize the index of Bigdata tables in the Test database idx_ Fragment on Mobiel UPDATE STATISTICS bigdata  --Update statistics for all indexes in Bigdata table

How the index is designed

For a table, it depends on the WHERE clause and the join expression to index the index.

The principles of indexing generally include the following:

• The system typically automatically establishes a clustered index for the primary key field .
• Consider establishing a clustered index with a large number of duplicate values and frequently scoped queries and sorting, grouped columns, or frequently accessed columns .
• In a table that often inserts operations , you should use FILLFACTOR (fill factor) to reduce page splits, while increasing the concurrency to reduce the occurrence of deadlocks. If the table is read-only, the fill factor can be set to 100.
• when selecting an index key, use columns of the small data type as a key so that each index page can hold as many index keys and pointers as possible , in such a way that a query must traverse the index page to a minimum, and, as much as possible, use an integer as the key value. Because integers are accessed most quickly.

SQL Server Index