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 INDEX 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.
CREATE 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 INDEX 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.
CREATE 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.
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.
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