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 example

 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; 

ten_bang

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

ten_chimuc_cu

The name of the index to delete.

For example

 DROP INDEX danhba.danhba_chimuc; 

This example deletes the index named ba_chimuc.

Previous article: Check constraints in SQL Server

The following article: Grant / Revoke permissions in SQL Server

5 ★ | 2 Vote

May be interested

  • Index (Mong) in MongoDBIndex (Mong) in MongoDB
    index (index) supports the resolution of queries more efficiently. without an index, mongodb must scan through all a collection's document to select the documents that connect to the query command. this scan may be ineffective and requires mongodb to handle a large amount of data.
  • Adaptation index (AQ) and strategy to help management leaders changeAdaptation index (AQ) and strategy to help management leaders change
    aq stands for adaptability quotient, which is used to measure the adaptability and flexibility of leaders' strategies to change the environment.
  • Instructions for using Index function in ExcelInstructions for using Index function in Excel
    index is a function that returns an array in excel. when using the index function, you get the values ​​in a cell between the column and the row.
  • How to reset and re-run the search index on Windows 10How to reset and re-run the search index on Windows 10
    by default, windows will use the index when searching to provide faster search results. this article will show you how to reset and re-run the manual search index for accurate search results in windows 10.
  • How to Add a Folder to the Windows 7 File IndexHow to Add a Folder to the Windows 7 File Index
    the windows search index is a list of files and folders that are commonly searched. these include the folders in your user directory, and anything in your libraries. adding folders to the index will allow them to be quickly searched, which...
  • What is triglyceride? What is the normal triglyceride index?What is triglyceride?  What is the normal triglyceride index?
    let's find out what triglyceride is by tipsmake.com, how much is the normal triglyceride index so that there are appropriate measures to protect your health!
  • Top website see weather and UV indexTop website see weather and UV index
    these are web services that help you see the uv index and forecast the weather according to the exact time of day.
  • How to view UV index on iPhoneHow to view UV index on iPhone
    you want to know the uv index where you live or anywhere in the world. you can do it right away without installing 3rd party applications.
  • The difference between web server and app serverThe difference between web server and app server
    you have probably seen that the terms web server and app server are often used interchangeably as if they are related to the same thing and also facilitate the website to function properly. but in reality, they are not the same.
  • How to Create an Index in WordHow to Create an Index in Word
    this wikihow teaches you how to create an index page in microsoft word to look up important terms discussed in a document and the pages they appear on. open a microsoft word document. ms word lets you add an index to any document...