SQL Server CREATE INDEX (GO)

What is an index

Take the catalogue page of the Chinese Dictionary (index) analogy: Just as Chinese characters are stored on pages, SQL Server data records are also stored on a per page basis, and each page capacity is typically 4K . In order to speed up the search, Chinese word (word) code generally have by pinyin, strokes, radicals and other categories of the directory (index), we can choose by pinyin or strokes to find the way to quickly find the desired word (word).

Similarly, SQL Server allows the user to create an index in a table, specifying that a column is pre-ordered, which greatly improves the query speed.

? ????????? data in SQL Server is also stored by page ( 4KB )

? ????????? Index: is an internal method of SQL Server orchestration data. It provides a way for SQL Server to orchestrate query data .

? ????????? Index page: The data page in the database where the index is stored; The index page is similar to a catalog page sorted by phonetic or stroke in the Chinese word (word) dictionary.

? ????????? The role of indexes: By using indexes, the retrieval speed of the database can be greatly improved and the database performance improved.

??

Index type

? ????????? Unique index: 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 (Clustered): The physical order of the rows in the table is the same as thelogical (indexed) Order of the key values, and each table can have only one

? ????????? Nonclustered indexes (non-clustered): 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

??

Index type: Again, in the Chinese dictionary, I hope you can understand the two concepts of clustered and nonclustered indexes.

??

Unique index:

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

if duplicate key values exist in the existing data, most databases do not allow the newly created unique index to be saved with the table. The database also rejects this data when the new data duplicates the key values in the table. For example, if you create a unique index on the learner ID number (STUID) column in the stuinfo table , the ID number of all learners cannot be duplicated.

Tip: A unique constraint is created and a unique index is created automatically. Although a unique index can help you find information, it is recommended to use a PRIMARY KEY constraint or a unique constraint for best performance.

??

Primary KEY index:

Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a special type of unique index. The primary key index requires that each value in the primary key be unique. When a primary key index is used in a query, it also allows for fast access to the data.

??

Clustered Index ( Clustered Index )

In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index. For example, the Chinese word (word) code defaults to alphabetical order of each page number in the dictionary. Phonetic Alphabeta,b,C,d......x,y,Zis the logical order of the index, and the page number1,2,3 ...is the physical order. The dictionary, which is sorted by phonetic Alphabet by default, has the same index order and logical order. That is, the phonetic order of the word (word) corresponding to the page number is also larger. such as pinyin"Ha"the corresponding word(Word)The page number is more than pinyin"BA"the corresponding word(Word)The page number leans back.

??

Nonclustered Indexes (non-clustered)

if it is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered Indexeshave faster data access speeds than nonclustered indexes (nonclustered index). For example, an index sorted by strokes is a nonclustered index, andthe word"1" (word) corresponds to a page number that corresponds to the number of words (the word) that the " 3" is drawn on ( back).

Tips: SQL Server , a table can only create 1 a clustered index, multiple nonclustered indexes. Set a column's primary key, which defaults to a clustered index

??

How to create an index

Use T-SQL statement to create the index syntax:

CREATE [UNIQUE] [clustered| Nonclustered]

INDEX index_name

On table_name (column_name ...)

[With Fillfactor=x]

? ?????? Unique represents a single index, optional

?????? clustered , nonclustered

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

??

in the Stumarks of the table Writtenexam column to create an index:

Use Studb

GO

IF EXISTS (SELECT name from sysindexes

WHERE name = ' Ix_writtenexam ')

DROP INDEX Stumarks.ix_writtenexam

/*-- the written test column creates a nonclustered index: The fill factor is - % --*/

CREATE nonclustered INDEX Ix_writtenexam

On Stumarks (Writtenexam)

???? With fillfactor= 30

GO

/*----- Specify by index Ix_writtenexam Enquiry ----*/

SELECT * from Stumarks (index=ix_writtenexam)

WHERE Writtenexam between and 90

Although we can specify SQL Server by which index to query the data, but generally do not need us to manually specify. SQL Server automatically optimizes the query based on the index that we create .

??

Advantages and disadvantages of indexes

? ????????? Advantages

– ???????? Speed up access

– ???????? enhancing the uniqueness of rows

? ????????? 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 an indexed column according to the following criteria.

– ???????? This column is used for frequent searches

– ???????? This column is used to sort the data

? ????????? do not create an index using the following columns:

– ???????? only a few different values are included in the column.

– ???????? sql Server It takes more time to search for data in the index than it takes to search in a table in a row