Primary keys and clustered and Single-column indexes and multiple-column indexes

1. Primary key and clustered index

First, clarify a concept that the primary key is not equal to the clustered index. (This is not nonsense, if the same thing, Microsoft will not be called two different names)

A table can have only one clustered index, and the data is physically stored in the order of the clustered index.

Primary keys are grouped into clustered primary keys and nonclustered primary keys. The default is the clustered primary key. The following code is the automatically generated create TABLE code for SQL Server, and note that the key in the phrase '

CLUSTERED ', which represents the primary key of the cluster.

/****** object:table [dbo]. [User] Script date:03/28/2014 15:14:53 ******/

SET ANSI_NULLS on

Go

SET QUOTED_IDENTIFIER ON

Go

CREATE TABLE [dbo]. [User] (

[ID] [int] IDENTITY (1,1) not NULL,

[SchoolID] [nvarchar] () not NULL,

[StudentID] [nvarchar] () not NULL,

[TimeLine] [nvarchar] () not NULL,

[Name] [nvarchar] (m) NULL,

[Phone] [nvarchar] (m) NULL,

[Gender] [nvarchar] (m) NULL,

[Age] [smallint] Null

[Idcard] [nvarchar] (m) NULL,

[Nation] [nvarchar] (m) NULL,

[Party] [nvarchar] (m) NULL,

[Birthday] [nvarchar] (m) NULL,

[Married] [nvarchar] (m) NULL,

[School] [nvarchar] (m) NULL,

[Degree] [nvarchar] (m) NULL,

[Province] [nvarchar] (m) NULL,

[City] [nvarchar] (m) NULL,

[Area] [nvarchar] (m) NULL,

[Address] [nvarchar] (m) NULL,

[College] [nvarchar] (m) NULL,

[Major] [nvarchar] (m) NULL,

[Class] [nvarchar] (m) NULL,

[Extra] [Text] Null

PRIMARY KEY CLUSTERED

(

[Id] ASC

With (Pad_index = off, Statistics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = ON) O N [PRIMARY]

) on [PRIMARY] textimage_on [PRIMARY]

Go

2. Multiple-column index and multiple single-column indexes

Consider two different ways to establish an index:

Case 1: Add a multiple-column index in this order for c1,c2,c3 three columns;

Case 2: Establish three single-column indexes for C1,C2,C3;

Question 1: When C1 search, which index is efficient?

Answer: CASE2

Question 2: When C2 search, which index is efficient?

A: Case2, and the CASE1 index is invalid

Question 3: Which efficiency is faster when searching by C1,C2?

A: I don't know.

Question four: According to C1,C2,C3 search which kind of efficiency is fast?

Answer: CASE1

Question five: Which efficiency is faster when searching by C2,C3,C1?

A: Case2, the CASE1 index is not used because there is no sequential search for multiple-column indexes.

3. Overwrite Query

The so-called overwrite query is simply the query that all query columns are covered by the index used.

4. Negative effects of too many indexes in a single table

When multiple (single-column) indexes exist for a table, the delete, update, insert operation takes a significant amount of time to delete the index and rebuild the index.

By merging multiple (Single-column) indexes into one (multiple-column) index, the test results in Delete, update, and the time taken to insert the operation is greatly shortened. However, this may have an effect on the query performance of the previously Single-column indexed fields. Advantages, trade-offs.