SQL Server Indexing Technology

Classification description of the index

Clustered index: Based on the sort and storage location of records within a data table. Because the physical ordering of data can only be one way, only one field in a table can be set as a clustered index.

Nonclustered indexes: The index is established on the index page, where the records are located from the index when queried.

Unique index: When the field is set to a unique index, the same field of the different records is unique. When a primary key is created in the datasheet, the database automatically creates a unique index for that primary key.

Composite Index: Multiple fields are combined as indexes, called composite indexes. This index is used only for the first field of a composite index or for the entire composite index field as a conditional query.

Inclusive Column index: When you create an index, the maximum number of fields cannot exceed three, and all field sizes cannot exceed the sum of five bytes, and the Inclusive column index is the result of solving the problem.

View index: If you create an index for a view, the view is materialized and the result set is permanently stored in the view.

Full-Text Indexing: Full-text indexing is created and maintained by the full-text engine service. Used primarily for searching strings in large amounts of text, which is much more efficient than a like statement in T- SQL .

XML Index : An index created on an XML field is an XML index.

Syntax for creating indexes

create [unique] [clustered | nonclustered]index <index name> on  <table or view name> (<column name> [asc| desc][,... N]) include  (<column name> [,... n]) [    with     [pad_index = {on | off}]    [[,] fillfactor =  <fillfactor>]    [[,] ignore_dur_key = {on | off}]     [[,] drop_existing = {on | off}]    [[,]  statistics_norecompute = {on | off}]    [[,] sort_in_tempdb  = {on | off}]    [[,] online = {on | off}]     [[,] allow_row_locks = {on | off}]    [[, ] allow_page_locks = {on | off}]    [[,] maxdop = <maxinum degree of parallelism>][on  {<filegroup> | <partition scheme name> | default}]

Parameter description:

Fill factor: The fill rate of the fill data for each leaf node when the index page is created. If the data in the table changes very little, set the fill factor a little bit larger, or vice versa.

Need to be added ...

Create various index examples: to be replenished ...

viewing and modifying indexes

To view the index using sp_helpindex :

Syntax:sp_helpindex [@objname] ' name '

Example:exec sp_helpindex ' Student '

Use:sys.indexes to view all parameter information for the index:

Example:select * from sys.indexes

Sys.indexs View field Description :

Field name	Field description
object_id	The number of the object to which the index belongs
Name	Index name
index_id	Index ID
Type	Index type:0 for heap,1 for clustered index,2 for nonclustered index,3 for XML Index
Type_desc	Index type description
Is_unique	0: Not unique index,1: Unique index
data_space_id	The number of the indexed filegroup or partition scheme
Ignore_dup_key	Whether to ignore duplicate key values:1 Yes,0 No
Is_primary_key	is part of a PRIMARY KEY constraint:1 Yes,0 No
Is_unique_constraint	is part of a Unique constraint:1 Yes,0 No
Fill_factor	Fill factor
ispadded	Whether to populate the index
Is_disabled	Whether to disable indexing
Is_hypothetica	Whether to assume the index
Allow_row_locks	Whether to use row locks
Allow_page_lock	Whether to use page locks

To modify an index by using the ALTER INDEX statement

ALTER INDEX {<name of index> | all}on<table or view name>{rebuild[[with ([pad_index = {on | OFF}]| [[,] FILLFACTOR = <fillfactor> | [[,] sort_in_tempdb = {on | OFF}] | [[,] Ignore_dup_key = {on | OFF}] | [[,] Statistics_norecompute = {on | OFF}] | [[,] ONLINE = {on| OFF}] | [[,] allow_row_locks = {on | OFF}] | [[,] allow_page_locks = {on | OFF}] | [[,] MAXDOP = <max degree of parallelism>)]| [PARTITION = <partition number>[WITH (< partition rebuild Index option> 　　　　 [,... N])] []] | DISABLE | REORGANIZE [PARTITION = <partition number>] [with (lob_compaction = {on | OFF})] | SET ([allow_row_locks = {on | OFF}] | [[,] allow_page_locks = {on | OFF}] | [[,] Ignore_dup_key = {on | OFF}] | [[,] Statistics_norecompute = {on | OFF}])}[;]

Rebuilding and rebuilding indexes

TheSQL Server Database engine maintains indexes automatically whenever an INSERT, update, or delete operation is performed on the underlying data. Over time, these modifications may cause information in the index to be scattered in the database (containing fragmentation). Fragmentation occurs when the logical ordering (based on the key value) in the page contained by the index does not match the physical sort in the data file. A very numerous index of fragments can degrade query performance and cause the application to respond slowly.

To reorganize an index:

Reorganize an index to defragment the leaf level of a table or view's clustered and nonclustered indexes by physically reordering the leaf pages so that they match the logical order (left to right) of the leaf nodes. Order the page to improve the performance of index scans. The index is re-organized within the existing page that is assigned to it, and the new page is not assigned. If the index spans multiple files, a file is re-organized one at a time and the pages are not migrated between files.

Re-organization also compresses the index pages. If there is still disk space available, all empty pages generated during this compression process will be deleted. Compression is based on the fill factor value in the Sys.indexes catalog view.

The reorganize process uses minimal system resources. Also, the re-organization is performed automatically online. The process does not hold a long-term blocking lock, so it does not prevent queries or updates from running.

To rebuild the index:

Rebuilding the index deletes the index and creates a new index. This process removes fragmentation, reclaims disk space by setting compressed pages with the specified or existing fill factor, and re-sorts the indexed rows in successive pages (assigning new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.

The selection of the re-grouping and center of gravity Build index:

Index fragmentation is not too long (index fragmentation is less than 30%) and indexes can be re-organized. If the index fragmentation is very much (greater than 30%), rebuilding the index yields better results.

Viewing index fragmentation

To View index fragmentation using the Sys.dm_db_index_physical_stats function:

Sys.dm_db_index_physical_stats (
{database_id | NULL}
, {object_id | NULL}
, {index_id | NULL | 0}
, {partition_number | NULL}
, {mode | NULL | DEFAULT}
)

example, view All index fragmentation information for the SC table:

DECLARE @databaseid int

DECLARE @objectid int

SET @databaseid = db_id (N ' DB ')

SET @objectid = object_id (N ' SC ')

SELECT * from Sys.dm_db_index_physical_stats (@databaseid, @objectid, Null,null,null)

Renaming, deleting, disabling indexes

To rename an index:

sp_rename index_name_old, ' index_name_new '

To delete an index:

Drop INDEX Table_name.index_name

To disable indexing:

Alter index idxname on S disable-- disables the specified index

Alter index all on S disable-- disables all indexes ( including primary keys )

Indexed views

Considerations for creating Indexes:

L A table can have only one clustered index, but there may be multiple nonclustered indexes. A nonclustered index cannot exceed 249 .

An index can contain up to a maximum of three fields, and the sum of all field lengths cannot exceed The number of bytes, if more than Three fields or more than the byte, you might consider using the included column index.

When there are very few records in a table, you do not need to create an index in addition to the index created by the system itself for the primary key field and the Unique field.

Under what circumstances should I avoid using indexes?

Although indexes are designed to improve the performance of the database, there are times when they should be avoidable. The following guidelines should be reconsidered when working with indexes:

l Indexes on small tables should not be used.

L There are frequent, high-volume updates or insert operations on the table.

The index should not use a column that contains a large number of NULL values.

L frequently-manipulated columns should not be indexed.

Summarize

Add: Not all indexes need to be created manually, as long as the primary key or unique constraintis set when a table is created in SQL Server .SQL Server the index is automatically created and the primary key index is a clustered index with the same index name as the primary key name.

SQL Server Indexing Technology