Article source: blog.csdn.net/lenotang/ar…
What is an index
Take the table of Contents (index) page of a Chinese dictionary for example: Just as Chinese characters in a Chinese dictionary are stored by page, data records in SQL Server are also stored by page, and the capacity of each page is generally 4K. In order to speed up the speed of the search, the Chinese character (word) dictionary generally has a catalogue (index) sorted by pinyin, strokes, radicals, etc., we can choose to search by pinyin or strokes, quickly find the word (word) needed.
Similarly, SQL Server allows users to create indexes in a table that specify a column to be pre-ordered, making queries much faster.
• data in SQL Server is also stored by page (4KB)
• Index: is the internal method by which SQL Server orchestrates data. It provides SQL Server with a way to orchestrate query data.
• Index page: data page in the database where the index is stored; The index page is similar to the table of contents sorted by pinyin or strokes in the Chinese dictionary.
• Functions of indexes: Indexes greatly speed up database retrieval and improve database performance.
The index type
• Unique index: A 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. A primary key index requires that each value in a primary key be unique and cannot be null
• Clustered indexes: The physical order of rows in a table is the same as the logical (index) order of key values, and there can only be one per table
• Non-clustered indexes: Non-clustered indexes specify the logical order of tables. Data is stored in one location, indexes are stored in another location, and indexes contain Pointers to where the data is stored. There can be more than one, less than 249
Index type: using the Chinese dictionary analogy again, I hope you can understand the two concepts of clustered index and non-clustered index.
Unique index:
Unique indexes do not allow two rows to have the same index value.
If duplicate key values exist in existing data, most databases will not allow the newly created unique index to be stored with the table. The database also rejects new data that would duplicate key values in the table. For example, if a unique index is created on the stuID column in the stuInfo table, the id numbers of all trainees cannot be repeated.
Tip: Once a unique constraint is created, a unique index is automatically created. Although unique indexes are helpful in finding information, primary key or unique constraints are recommended 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. Primary key indexes require that each value in a primary key be unique. It also allows fast access to data when using a primary key index in a query.
Clustered Index
In a clustered index, the physical order of the rows in the table is the same as the logical (index) order of the key values. A table can contain only one clustered index. For example, by default, the Chinese character (word) dictionary arranges the page number of each page in the dictionary according to pinyin. Pinyin letters A, B, C, D… X, y, z are the logical order of the indexes, and page numbers 1,2,3… That’s the physical order. Dictionaries that are sorted by pinyin by default have the same index order as the logical order. In other words, the characters (ci) in the lower order of pinyin have larger page numbers. For example, the page number of the word corresponding to the pinyin “ha” is later than the page number of the word corresponding to the pinyin “BA”.
Non-clustered indexes
If the index is not clustered, 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 speeds than nonclustered indexes. For example, an index sorted by stroke is a non-clustered index, and a word drawn “1” may have a larger page number (later) than a word drawn “3”.
Note: In SQL Server, only one clustered index can be created for a table, and multiple non-clustered indexes can be created. If you set a column as the primary key, that column defaults to a clustered index
How to create an index
Syntax for creating indexes using t-SQL statements:
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED]
INDEX index_name
ON table_name, column_name…).
[WITH FILLFACTOR=x]
Q UNIQUE indicates the UNIQUE index, which is optional
Q CLUSTERED or NONCLUSTERED CLUSTERED or CLUSTERED indexes are optional
Q FILLFACTOR represents the FILLFACTOR, specifying a value between 0 and 100 that indicates the percentage of space that index pages fill
Create an index in the writtenExam column of stuMarks
USE stuDB
GO
IF EXISTS (SELECT name FROM sysindexes
WHERE name = ‘IX_writtenExam’)
DROP INDEX stuMarks.IX_writtenExam
/*– create a non-clustered index for the written column: fill factor is 30% –*/
CREATE NONCLUSTERED INDEX IX_writtenExam
ON stuMarks(writtenExam)
WITH FILLFACTOR= 30
GO
/*—– specifies the index IX_writtenExam to query —-*/
SELECT * FROM stuMarks (INDEX=IX_writtenExam)
WHERE writtenExam BETWEEN 60 AND 90
Although we can specify which index SQL Server queries data by, we generally do not need to manually specify. SQL Server will automatically optimize the query based on the index we create.
Advantages and disadvantages of indexes
• advantages
– Speed up the access speed
— Strengthen the uniqueness of rows
• disadvantage
– Indexed tables require more storage space in the database
– Commands that manipulate data take longer to process because they need to update the index
Guidelines for creating indexes
• Select columns to be indexed according to the following criteria.
– This column is used for frequent searches
– This column is used to sort data
• Do not create indexes using the following columns:
– Contains only a few different values in the column.
– The table contains only a few rows. Creating an index for a small table may not be cost-effective because SQL Server takes longer to search the data in the index than it does row by row in the table