SQL Server Index

Source: Internet
Author: User

Tags: des recognition query mechanism storage space statistics requires SEL dictionary

What is an index?

An index is a data structure on a disk that is based on a table or view. Using an index can make data acquisition faster and higher in universities, and also affect some other performance, such as inserting or updating.

There are two main types of indexes: clustered and nonclustered indexes.

Dictionary directory is an index, according to pinyin query the desired word is a clustered index (physical continuous, page number and table one by one corresponds), the radicals are a nonclustered index (logical continuous, page number and directory discontinuity).

Clustered index storage records are physically contiguous, while nonclustered indexes are logically contiguous, and physical storage is not contiguous.

A clustered index can have only one table, and a non-clustered index may have more than one table. The pros and cons of indexes

The index is used to avoid full table scans because full table scans read every data page of a table from disk, and if an index points to a data value, only a few disks are required to read.

Indexed tables take up more space in the database, and it takes longer to add, delete, and change data commands. Storage mechanism of indexes

The catalog in the book is a word and a list of page numbers in which the index in the database is the value in the table and the list where the values are stored.

A clustered index is a new physical space in the database that holds the values he arranges, and when new data is inserted, he rearranges the entire physical storage space.

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

When a new data table is created, the system allocates a contiguous space of 8k in the disk. When a 8k run out of time, the database pointer will automatically allocate a 8k of space, each 8k of space is called a data page, and assigned from 0-7 page number, each file's No. 0 page records the boot information called the page header, every 8 data pages by 64k form the expansion 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 8k in size, and storing more than 8k of data should use the text type. In fact, the text type of the field value can not be directly entered and saved, it is to store a pointer to a number of 8k data page composed of the expansion area, the real data is actually placed in these data pages. When to set the index

    1. Defines the data column for the primary key (SQL Server defaults to a clustered index for the primary key).
    2. Data columns that have foreign keys defined
    3. For data columns that are queried frequently
    4. For data columns that need to be queried frequently within a specified range
    5. Data columns that are often present in the WHERE clause
    6. Often appear in the fields following the keyword ORDER BY, group by, and distinct.
Under what circumstances do not set the index
    1. The columns that are seldom involved in a query are those that have more duplicate values.
    2. columns for text, image, bit data types
    3. Frequently-accessed columns
    4. Frequently updated table of operations, the index generally not more than 3, not more than 5. Although the speed of access is increased, it affects the update operation.
Clustered index 1, using SSMS to create a clustered index

Expand the table to create the index, right-click Index, select New index, clustered index, new indexing point, select Columns, select column, and choose Ascending or Descending, enter name, OK.

By default, a clustered index is automatically created at the same time that the primary key is generated. 2. Create a clustered index using T-SQL

 create  clustered  Span style= "Color:rgb (0, 0, 255); >index  index_name /*   Clustered index name  */ on  " Span style= "Color:rgb (0, 0, 0); > table_name (ID  desc  )  with  (Drop_existing =  /*   */ 

Each table or view can contain only one clustered index, because the clustered index changes the way data is stored and arranged. Whether clustered or nonclustered, the information is stored in a balance tree or B-tree, and B-trees identify and group similar data, precisely because the retrieval in the B-tree is based on key values, so the index can increase the speed of data access. The B-Tree will have a combination of similar keys, so the database engine can find the target record by searching only a few pages. Nonclustered indexes

You can have more than one nonclustered index on each table, you can create an index on a column, or you can create an index on multiple columns that are already part of an existing index.

The SSMs creation method is the same as the T-SQL creation method:

Create nonclustered Index /* Clustered index name */  on defualt (    desc)
Add Index Options

FILLFACTOR: Used when creating an index, the data for each index page is a percentage of the index size, default 100. When the table needs to be modified frequently, it is recommended to set to 70-80, which is recommended when updating infrequently. 90.

Pad_index: Used to keep open space on each page in the Index intermediate level. The value cannot be set, and his value inherits from FillFactor.

Small example

Create TableCeshi--New Table(IDint Identity(1,1)Primary Key, namevarchar( -), Codevarchar( -),    [Date] datetime)--inserting 10w test dataDeclare @n intSet @n = 1 while @n <100000beginInsert  intoCeshi (Name,code,[Date]) Values('name'+cast(@n  as varchar( -)),'Code'+cast(@n  as varchar( -)) , getUTCDate ())Set @n=@n+1End--View index StatusSet StatisticsIo on --View disk IOSet StatisticsTime on --Viewing SQL statements parsing compilation and execution timesSelect *  fromCeshi--View index Statusexecsp_helpindex CeshiSelect *  fromCeshiwhereName= 'name1'--ctrl+l View Execution Plan clustered index scan cost 100%, consider optimization for index lookup, build nonclustered index on name--To build a nonclustered indexCreate IndexName_index onCeshi (name)--look at the index again. New Nonclustered indexexecsp_helpindex Ceshi--in the run above statementSelect *  fromCeshiwhereName= 'name1'--It was obvious that the speed was getting faster, ctrl+l found that clustered and nonclustered indexes accounted for 50%
Manage Indexes
execSp_helpindex Ceshi--View the indexes in the tableexecsp_rename'Ceshi.name_index','new_name' --renamingDrop IndexCeshi.new_name--Delete IndexDBCCShowcontig (Ceshi,new_name)--Check for fragmentationDBCCIndexdefrag (Webdb,ceshi,new_name)--Organize the FragmentsUpdate StatisticsCeshi--UPDATE STATISTICS for all indexes in a table

SQL Server Index

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

Tags Index: