Shulou(Shulou.com)05/31 Report--

This article shows you how to create an index in SQLSERVER. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

What is an index?

Take the catalogue page (index) of the Chinese dictionary for example: just as the Chinese characters in the Chinese dictionary are stored by page, the 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 search, Chinese characters (words) dictionaries generally have catalogs (indexes) sorted by pinyin, strokes, partial radicals, etc., and we can choose to find the desired words (words) quickly by pinyin or stroke search. Similarly, SQL Server allows users to create indexes in a table and specify that they are pre-sorted by a column, thus greatly improving query speed.

The data in SQL Server is also stored by 4KB.

Index: is the 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; an index page is similar to a catalog page sorted by pinyin or strokes in a Chinese word dictionary.

The function of index: through the use of index, the retrieval speed of database can be greatly improved and the performance of database can be 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 is unique and cannot be empty.

Clustered index (Clustered): the physical order of rows in a table is the same as the logical (index) order of key values, and there can be only one table.

Nonclustered index (Non-clustered): a nonclustered index specifies the logical order of the tables. The data is stored in one location, the index is stored in another, and the index contains a pointer to the data storage location. There can be multiple index types, less than 249 index types: again, using the Chinese dictionary for example, I hope you can understand the concepts of clustered index and nonclustered index.

Unique index: a unique index does not allow two rows to have the same index value. If there are duplicate key values in existing data, most databases do not allow the newly created unique index to be saved 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 number (stuID) column in the stuInfo table, the ID numbers of all students cannot be duplicated. Tip: if a unique constraint is created, a unique index will be created automatically. Although unique indexes help you find information, it is recommended that you use primary key constraints or unique constraints 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. It also allows quick access to data when primary key indexes are used in queries.

Clustered index (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, Chinese characters (words) arrange the page numbers of each page in the dictionary according to pinyin sorting. Phonetic alphabet a _ d. The logical order of the index is x _ 4e _ y _ r _ z, and the page number 1 ~ 2 ~ 2 ~ 3. It's the physical order. Dictionaries sorted by Pinyin by default have the same index order and logical order. In other words, the page number of the word (word) that is later in pinyin order is also larger. For example, the page number of the word (word) corresponding to Pinyin "ha" is lower than that of the word (word) corresponding to Pinyin "ba".

If a nonclustered index (Non-clustered) 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 page number corresponding to the word (word) drawn by "1" may be larger than the page number corresponding to the word (word) drawn by "3". Tip: in SQL Server, only one clustered index and multiple nonclustered indexes can be created for a table. Set a column primary key that defaults to the clustered index

The syntax for how to create an index using the T-SQL statement: the copy code is 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 or NONCLUSTERED represents a clustered index or nonclustered index, and optional Q FILLFACTOR represents a fill factor, specifying a value between 0 and 100 that indicates the percentage of space filled by the index page

Create an index in the writtenExam column of the stuMarks table: copy the code as follows: 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: fill factor is 30% Mustang / 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 60 AND 90

Although we can specify which index SQL Server will query for data, we generally do not need to specify it manually. SQL Server will automatically optimize the query based on the index we created.

Advantages and disadvantages of indexes-faster access-enhance row uniqueness-indexed tables require more storage space in the database-commands that manipulate data take longer to process because they need to update the index

For guidelines for creating an index, select the indexed columns according to the following criteria. -this column is used for frequent searches.-this column is used to sort data. Please do not use the following column to create an index:-the column contains only a few different values. -the table contains only a few rows. It may not be cost-effective to index small tables because SQL Server takes longer to search for data in the index than it takes to search for data row by row in the table

The above is how to create an index in SQLSERVER. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.