What is an index
Take the table of Contents page of Chinese dictionary (index) metaphorically: Just as Chinese characters are stored as pages in a dictionary, data records in SQL Server are stored on a per-page basis, typically 4K in volume. In order to speed up the speed of the search, the Chinese word (word) code generally have by pinyin, strokes, radicals, such as the sort of table of contents (index), we can choose to use pinyin or strokes to find ways to quickly find the words needed.
Similarly, SQL Server allows users to create indexes in a table, specifying that a column is sorted in advance, thereby greatly increasing the query speed.
Data in the SQL Server is also stored by page (4KB)
• Index: is an internal method for SQL Server to orchestrate data. It provides a way for SQL Server to orchestrate query data.
• Index page: A data page in a database that stores an index, which is similar to a table of contents that is sorted by phonetic or stroke in the Chinese word dictionary.
• Indexing function: By using indexes, the retrieval speed of database can be greatly improved and 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 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 and cannot be empty
• Clustered index (Clustered): The physical order of the rows in the table is the same as the logical (indexed) Order of the key values, each table can have only one
• Nonclustered index (non-clustered): A nonclustered index Specifies the logical order of a table. The data is stored in one location, the index is stored in another location, and the index contains pointers to the location of the data store. can have multiple, less than 249
Index type: Again in Chinese dictionary analogy, I hope you can understand the two concepts of clustered index and nonclustered index.
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 new data will cause key values to be duplicated in the table. For example, if a unique index is created on the student ID number (STUID) column in the Stuinfo table, the ID number of all trainees cannot be duplicated.
Tip: A unique constraint is created, and a unique index is created automatically. Although a unique index helps find information, it is recommended that you use a PRIMARY KEY constraint or a unique constraint for optimal 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 you use a primary key index in a query, it also allows you to access data quickly.
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 by default, the numbering of each page in the dictionary is sorted by phonetic alphabet. Pinyin A,b,c,d......x,y,z is the logical order of the index, and the page number 1,2,3 ... is the physical order. The default dictionary, sorted by phonetic Alphabet, is indexed in the same order as the logical order. That is, the phonetic sequence of words (words) corresponding to the page number is also larger. If the phonetic "ha" corresponding to the word (Word) page number than phonetic "ba" corresponding to the word (Word) page number on the back.
Nonclustered index (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 indexes have faster data access than nonclustered indexes (nonclustered index). For example, an index sorted by stroke is a nonclustered index, and the "1" word (word) may have a larger page number than the "3" word (word).
Tip: In SQL Server, a table can create only 1 clustered indexes and multiple nonclustered indexes. Sets a column as the primary key, which defaults to the clustered index
How to create an index
Syntax for creating indexes using T-SQL statements:
Copy Code code as follows:
CREATE [UNIQUE] [clustered| Nonclustered]
INDEX index_name
On table_name (column_name ...)
[With Fillfactor=x]
Q Unique represents a unique index, optional
Q CLUSTERED, nonclustered indicates a clustered index or a nonclustered index, optional
Q FillFactor represents a fill factor, specifying a value between 0 and 100 that indicates the percentage of space that the index page fills up
to create an index on the Writtenexam column of the Stumarks table:
Copy Code code as follows:
Use Studb
Go
IF EXISTS (SELECT name from sysindexes
WHERE name = ' Ix_writtenexam ')
DROP INDEX Stumarks.ix_writtenexam
/*--written test column create nonclustered index: Fill factor is 30%--*/
CREATE nonclustered INDEX Ix_writtenexam
On Stumarks (Writtenexam)
With fillfactor= 30
Go
/*-----Specify Ix_writtenexam Query by index----* *
SELECT * from Stumarks (index=ix_writtenexam)
WHERE Writtenexam BETWEEN 90
Although we can specify which index SQL Server will query for data, we generally do not need to manually specify it. SQL Server will automatically optimize the query based on the index we created.
Advantages and disadvantages of indexing
• advantages
– Speed up access
– Enhance the uniqueness of rows
• Disadvantages
– Tables with indexes require more storage space in the database
– Commands that manipulate data require longer processing time because they need to be updated on the index
guidelines for creating indexes
• Select the indexed columns according to the following criteria.
– This column is used for frequent searches
– This column is used to sort data
• Do not use the following columns to create an index:
– The column contains only a few different values.
– The table contains only a few rows. Creating an index for a small table may not be a good deal because SQL Server spends more time searching for data in the index than it does in a table-by-row search
