SQL Server creates index implementation code

What is an index <strong> <span class="Apple-converted-space"></span>
The Table of Contents page (index) of the Chinese Dictionary: just as the Chinese characters are stored in a page, the data records in SQL Server are stored on the page, typically 4K per page. 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.  

The data in the
SQL Server is also stored by page (4KB <span class="Apple-converted-space"></span> )


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 that stores an index in a database; an index page is similar to a catalog page sorted by phonetic or stroke in a Chinese word dictionary.  

The function of
index: By using index, 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 an empty <span class="Apple-converted-space"></span>


Clustered index (Clustered): The physical order of the rows in the table is the same as the logical (indexed) Order of the key values, <span class="Apple-converted-space"></span> and 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:


unique indexes do not allow two rows to have the same index value.  


if duplicate key values exist in 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. Set a column as the primary key, which defaults to the clustered index <span class="Apple-converted-space"></span>





How to create an index
syntax for creating indexes using T-SQL statements <span class="Apple-converted-space"></span> :


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 or nonclustered index, optional


Q FillFactor represents a fill factor, specifying a value from 0 to 100 that indicates the percentage of space that the index page fills up





creates 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=


go


/*-----Specify Ix_writtenexam Query by index----/ <span class="Apple-converted-space"></span> *


SELECT * from Stumarks (index=ix_writtenexam)


WHERE writtenexam BETWEEN


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
Index


Advantages


– speed up access


– Strengthening the uniqueness of the line <span class="Apple-converted-space"></span>


disadvantage


– tables with indexes require more storage space in the database


– commands that manipulate data require longer processing time because they need to update the index





guidelines for creating indexes


Select the indexed columns according to the following criteria.  


– This column is used to frequently <span class="Apple-converted-space"></span> search


– This column is used to sort the data <span class="Apple-converted-space"></span>


do not use the following columns to create an index <span class="Apple-converted-space"></span> :

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