SQL Server Index

The basics of SQL Server indexing are generally understood by understanding the relevant knowledge.

Index is to optimize the query efficiency, too many duplicate indexes will affect the data deletion and modification operations. So the use of the time should be practical to add.

Index types are: Unique indexes, primary key indexes, clustered indexes, and nonclustered indexes.

Unique index: A unique index does not allow two rows to have the same index value.

Primary KEY index: Defining a primary key for a table will automatically create a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key is unique and cannot be empty

Clustered index: The physical order of the rows in the table is the same as the logical (indexed) Order of the key values, with only one per table.

Nonclustered indexes: Nonclustered indexes Specify the logical order of tables. The data is stored in one place, the index is stored in a different location, and the index contains a pointer to the location of the data store. can have multiple, less than 249.

In SQL Server, a table can only create 1 clustered indexes, multiple nonclustered indexes. Sets a column primary key, which defaults to a clustered index.

How to create an index

Syntax for creating indexes using T-SQL statements:

CREATE [UNIQUE] [clustered| Nonclustered]

INDEX index_name

On table_name (column_name ...)

[With Fillfactor=x]

Unique represents a single index, optional

CLUSTERED, nonclustered represents a clustered or nonclustered index, optional

FILLFACTOR represents a fill factor, specifying a value between 0 and 100 that indicates the percentage of space that the index page fills up

Although we can specify which index SQL Server will query for data, we do not normally need to specify it manually. SQL Server automatically optimizes the query based on the index that we create.

Advantages and disadvantages of indexes

• Benefits

– Speed up access

– Enhance the uniqueness of the row

• Disadvantages

– Indexed tables require more storage space in the database

– Commands that manipulate data require longer processing time because they require updates to the index

Guidelines for creating indexes

• Select indexed columns According to the following criteria.

– This column is used for frequent searches

– This column is used to sort the data

• Do not use the following columns to create an index:

– Only a few different values are included in the column.

– The table contains only a few rows. Creating an index on a small table may not be a good deal because SQL Server spends more time searching for data in the index than it does in a row-by-line search in the table

Reference article: http://www.cnblogs.com/fish-li/archive/2011/06/06/2073626.html

http://blog.csdn.net/lenotang/article/details/3329501

SQL Server Index