SQL statement-create an index

Syntax:
Create [index type] Index name
On Table Name (column name)
With fillfactor = fill factor value 0 ~ 100
Go

/* Instance */
Use Database Name
Go
If exists (select * From sysindexes where name = 'ix _ test_tname ') -- check whether the ix_test_tname index already exists
Drop index test. ix_test_tname -- delete if it exists

-- Create an index
Create nonclustered index ix_test_tname -- create a non-clustered Index
On test (tname) -- creates an index for the tname field of the test table
With fillfactor = 30 -- fill factor is 30%
Go

Select * from test (Index = ix_test_tname) Where tname = 'A' -- specify to query by 'ix _ test_tname 'Index

Summary:
1. What is index: the index in the database is a logical pointer list of a set of values in one or more columns in a table and corresponding data pages pointing to the values physically identified in the table.
2. Category:
Unique index: two rows cannot have the same index value (a unique constraint is created, and the system automatically creates a unique index)
Primary Key Index: each value in the primary key must be unique (a primary key index is automatically created when a primary key is created)
Clustered index: the physical order of each row in the table is the same as the logic (INDEX) Order of the key value. The table can only contain one clustered index, and the primary key column is a clustered index by default.
Nonclustered: The physical order of each row in the table does not match the logic (INDEX) Order of the key value. The table can have 249 non-clustered indexes.
3. index creation criteria: Columns used for frequent search; columns used for sorting data
Note: if there are only a few rows in the table or the column contains only a few different values, it is not recommended to create an index, because SQL Server takes longer to search data by index in small tables than to search by row.

 

 

In fact, you can think of indexes as a special directory. Microsoft's SQL Server provides two types of indexes: clustered index (also called clustered index and clustered index) and non-clustered index (nonclustered index, also known as non-clustering index and non-cluster index ). The following is an example of the difference between clustered indexes and non-clustered indexes:

In fact, the body of our Chinese dictionary is a clustered index. For example, if we want to check the word "an", we will naturally open the first few pages of the dictionary, because the Pinyin of "an" is "", the Dictionary of Chinese characters sorted by pinyin starts with the English letter "a" and ends with "Z". Then the word "an" is naturally placed at the front of the dictionary. If you still cannot find the word in all the parts starting with "A", it means that you do not have this word in your dictionary. Similarly, if you query the word "Zhang, then you will turn your dictionary to the last part, because the Pinyin of "Zhang" is "Zhang ". That is to say, the body of the dictionary itself is a directory, and you do not need to query other directories to find the content you need.

We refer to this text content as a directory arranged according to certain rules as "clustered Index ".

If you know a word, you can quickly find it automatically. However, you may also encounter a word you do not know and do not know its pronunciation. At this time, you cannot find the word you want to query according to the method just now, you need to find the word you are looking for based on the "radicals", and then directly go to a page based on the page number after the word to find the word you are looking. However, the words you find in combination with the "radical directory" and "word checking table" are not really the sorting method of the text. For example, you can query the word "Zhang, we can see that the page number of the "Zhang" in the word checking table after the department head is 672, and the "Zhang" in the word checking table is "Chi", but the page number is 63, under "Zhang" is the word "", and the page is 390 pages. Obviously, these words are not really in the upper and lower sides of the word "Zhang, the continuous "Chi, Zhang, and "words you see are actually their sorting in the non-clustered index, which is the ing of words in the dictionary body in the non-clustered index. We can find the words you need in this way, but it requires two steps: first find the results in the directory, and then flip to the page number you need.

We refer to this directory as a directory, and the text as a non-clustered index ".

Through the above example, we can understand what is "clustered index" and "non-clustered index ".