Take the directory page (index) of Chinese Dictionary as an example: just as Chinese characters in Chinese Dictionary are stored by pages, data records in SQL Server are also stored by pages, and the capacity of each page is generally 4 K. In order to speed up the search, Chinese word dictionaries generally have directories (indexes) sorted by pinyin, strokes and radicals. We can choose to search by pinyin or strokes to quickly find the words (words) we need.
Similarly, SQL Server allows users to create indexes in tables and specify pre-sorting by columns, which greatly improves the query speed.
The data in SQL Server is also stored by page (4KB).
Index: It is an internal method for SQL Server to organize data. It provides a method for SQL Server to arrange query data.
Index page: the data page in the database where the index is stored; Index pages are similar to directory pages sorted by pinyin or strokes in Chinese dictionaries.
Function of index: By using index, the retrieval speed of database can be greatly improved and the performance of database can be improved.
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 will automatically create a primary key index, which is a special type of unique index. Primary key index requires that each value in the primary key is unique and cannot be empty.
Clustered index: The physical order of rows in a table is the same as the logical (index) order of key values, and each table can only have one.
Nonclustered index: A nonclustered index specifies the logical order of tables. The data is stored in one location and the index is stored in another location. The index contains pointers to data storage locations. There can be more than 249.
Index type: Take the Chinese dictionary as an example again. I hope you can understand the concepts of clustered index and nonclustered index.
Unique index:
Unique indexes do not allow two rows to have the same index value.
If there are duplicate key values in the existing data, most databases are not allowed to save the newly created unique index with the table. When the new data will duplicate the key values in the table, the database also refuses to accept the data. For example, if a unique index is created on the student ID column in the stuInfo table, the ID numbers of all students cannot be duplicated.
Tip: A unique constraint has been created and a unique index will be created automatically. Although a unique index helps to find information, it is recommended to use primary key constraints or unique constraints for best performance.
Primary key index:
Defining a primary key for a table in a database diagram will automatically create a primary key index, which is a special type of unique index. A primary key index requires that each value in the primary key be unique. It also allows quick access to data when using a primary key index in a query.
Clustering index
In a clustered index, the physical order of rows in a table is the same as the logical (index) order of key values. A table can only contain one clustered index. For example, by default, the Chinese word dictionary arranges the page numbers of each page in the dictionary according to pinyin sorting. Pinyin letters A, B, C, D … X, Y, Z are the logical order of the index, while page numbers 1, 2, 3 … are the physical order. By default, the dictionary sorted by pinyin has the same index order and logical order. That is to say, words (words) with lower pinyin order have larger page numbers. For example, the page number of the word (word) corresponding to the pinyin "ha" is behind the page number of the word (word) corresponding to the pinyin "ba".
Nonclustered index
If it is not a clustered index, the physical order of rows in the table does not match the logical order of key values. The data access speed of clustered index is faster than that of nonclustered index. For example, an index sorted by strokes is a nonclustered index, and the page number of a word (word) drawn with "1" may be larger (smaller) than that of a word (word) drawn with "3".
Tip: In SQL Server, a table can only create 1 clustered indexes and multiple nonclustered indexes. Set a column as the primary key, which is a clustered index by default.
How to create an index
Syntax for creating an index using a T-SQL statement:
Create [unique] [clustered | nonclustered]
Index index name
On the table name (column name ...)
[fill factor =x]
Q UNIQUE represents a unique index, which is optional.
Q clustered and nonclustered means clustered index or nonclustered index, which is optional.
The Q FILLFACTOR indicates the fill factor and specifies a value between 0 and 100, which indicates the percentage of space filled by the index page.
Create an index in the writtenExam column of the stuMarks table:
Using stuDB
go to
If it exists (select the name from sysindexes
Where name = 'IX_writtenExam')
Delete the index tag. IX_writtenExam
/*-Create a nonclustered index for the written column: the fill factor is 30%-*/
Create nonclustered index IX_writtenExam
On stuMarks(writtenExam)
Fill factor = 30
go to
/*-Specifies to query by index ix _ writenexam-*/
SELECT * FROM stu marks(INDEX = IX _ written exam)
Write an example between 60 and 90.
Although we can specify the index on which SQL Server queries data, we usually don't need to specify it manually. SQL Server will automatically optimize the query according to the index we created.
Advantages and disadvantages of index
superiority
–Speed up access.
–Strengthen the uniqueness of rows.
disadvantaged
Index 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
Please select the columns to index according to the following criteria.
–This column is used for frequent searches.
–This column is used to sort the data.
Do not use to create an index with the following columns:
–Columns contain only a few different values.
–The table contains only a few rows. Creating an index for a small table may not be cost-effective, because it takes SQL Server longer to search for data in the index than to search for data row by row in the table.