SQL Server Index

Source: Internet
Author: User
Tags create index one table

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 index index_name /*clustered index name*/
On table_name
     Id desc
With(drop_existing=on); /* delete */ if it exists

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 fei /* clustered index name */
On defualt
     Hits 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 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 int
Set @n = 1
While @n <100000
Insert into ceshi (name,code,[date])
Values (‘name‘+cast(@n as varchar(20)), ‘code’+cast(@n as varchar(20)), getutcdate())
Set @n=@n+1

--View index status
Set statistics io on -- view disk io
Set statistics time on -- view sql statement analysis compile and execute time
Select * from ceshi

--View index status
Exec sp_helpindex ceshi

Select * from ceshi where name = ‘name1’

--ctrl+l View execution plan Clustered index scan overhead 100%, consider optimization for index lookup, build non-clustered index on name

--Create a non-clustered index
Create index name_index on ceshi
--View the index again. Newly created non-clustered index
Exec sp_helpindex ceshi

-- Run the above statement
Select * from ceshi where name = ‘name1’
- Obviously found that the speed is getting faster, ctrl+l found that the clustered index and the non-clustered index each accounted for 50%

Manage Indexes

Exec sp_helpindex ceshi -- view the index in the table
Exec sp_rename ‘ceshi.name_index‘, ‘new_name’ -- renamed
Drop index ceshi.new_name -- delete index
Dbcc showcontig(ceshi,new_name) -- check for fragmentation
Dbcc indexdefrag(webDB,ceshi,new_name) -- defragmentation
Update statistics ceshi -- update statistics for all indexes in the table

SQL Server Index

Related Article

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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.