MS SQL Server: Index in SQL Server

This article explains how to create, rename and delete indexes in SQL Server.

What is the Index - Index in SQL Server?

Indexing is the method used to retrieve records faster. The index points to each value that appears in the indexed column.

Create an index

Syntax

 CREATE [UNIQUE  ] [ CLUSTERED | NONCLUSTERED ] INDEX ten_chimuc  ] [ CLUSTERED | NONCLUSTERED ] INDEX ten_chimuc 
  ON ten_bang ( cot1 [ASC | DESC ], . cot_n [ ASC | DESC ] ) 
  [ INCLUDE ( cot1, . cot_n ) ] 
  [ WHERE dieu_kien ] 
  [ WITH ( PAD_INDEX = { ON | OFF } 
  | FILLFACTOR = fillfactor 
  | SORT_IN_TEMPDB = { ON | OFF } 
  | IGNORE_DUP_KEY = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | STATISTICS_INCREMENTAL = { ON | OFF } 
  | DROP_EXISTING = { ON | OFF } 
  | ONLINE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF } 
  | ALLOW_PAGE_LOCKS = { ON | OFF } 
  | MAXDOP = so_toida 
  | DATA_COMPRESSION = { NONE | PAGE | ROW } 
  [ ON PARTITIONS ( { so | khoang } ] 
  [ ON scheme_phandoan ( cot ) 
  | ON fnhomtaptin | ON nhomtaptin_macdinh ] 
  [ FILESTR EAM_ON {nhomtaptin | scheme_phandoan }; scheme_phandoan}; 

UNIQUE

Optionally, indicate the combination of values in the index column must be unique.

CLUSTERED

Optionally, indicate the logical order that determines the physical arrangement of the rows in the table (ie the order of records in the table sorted by field in the index).

NONCLUSTERED

Optionally, indicate the logical order that does not determine the physical arrangement of the rows in the table.

ten_chimuc

The name of the index you want to create

ten_bang

The name of the table or View that the index is created on.

cot1,. cot_n

The column from that creates the index.

ASC | DESC

Sort order for values in the column.

INCLUDE (cot1,. cot_n)

Optionally, the column is not the main column to add to the leafy level of the Nonclustered index.

WHERE dieu_kien

Optional, is a condition that determines which row to include in the index.

ON scheme_phanvung (cot)

Optionally, indicate the partition space that determines the file group (filegroup) where the partition is mapped.

ON nhomtaptin

Optionally, indicate whether the index will be created on the specified file group.

ON nhomtaptin_macdinh

Optional, indicate the default file group.

FILESTREAM_ON {nhomtaptin | khongigna_phanvung } khongigna_phanvung}

Optionally, indicate the location of FILESTREAM data for Clustered index.

Examples of indexes in SQL Server

 CREATE IND EX danhba_chimuc 
  ON danhba (h o); 

In this example, we create an index named danhba_chimuc on the list, including a field called cough. An index of more than 1 field can be created as shown in the example below.

 C REATE INDEX danhba_chimuc 
  ON da (cough, ten); 

This example creates the list of namesba_chimuc in the list of names, including two fields, cough and ten. Since no ASC or DESC is specified for each item, the index created with each field in the default order is incremental. If you want in descending order, you can do the following.

 CREATE INDEX list name 
  ON danhba (ho DESC, te n DESC); 

The above CREATE INDEX command creates only the username and _chimuc with the first and last names sorted in descending order.

Example of unique index

 CREATE UNIQUE IN DEX danhba_chimucduynhat 
  ON danhba (ho, ten); 

This example creates an index named danh_chimuc on the list of names that includes first and last names, and ensures that the combination of these two fields is unique. It is possible to create a unique index of Clustered so that the physical order of the rows in the table will be decided by the logical order of the index.

 CREAT E UNIQUE CLUSTERED INDEX danhba_chimucduynhat 
 ON danh three (ho, ten); 

The newly created index in this example has the name danhba_chimucduynhat consisting of two first and last name fields, and also changes the sort order of the rows in the table according to it.

Rename the index in SQL Server

Syntax

 sp_rename 'tenbang.ten_chi_muc_cu', 'ten_chimuc_moi', 'INDEX'; 

ten_bang

The name of the table to which the index is created.

ten_chimuc_cu

The name of the index you want to rename.

ten_chimuc_moi

New name for index.

For instance,

 sp_rename 'danhba.danhba_chimuc', 'danhba_chimuc_ten', 'INDEX'; 

This example renames the index in the namba table from danhba_chimuc to listba_chimuc_ten.

Delete the index in SQL Server

Syntax

 DROP INDEX tenbang.ten_chimuc;