Primary Key and clustered index and Single Column index and multi-column Index

1. Primary keys and clustered indexes first clarify the concept that primary keys are not equal to clustered indexes. (Isn't that nonsense? If it's the same thing, Microsoft won't call it two different names.) A table can only have one clustered index, data is physically stored in the order of clustered indexes. Primary keys are classified into clustered primary keys and non-clustered primary keys. By default, the cluster master is used.

1. Primary keys and clustered indexes first clarify the concept that primary keys are not equal to clustered indexes. (Isn't that nonsense? If it's the same thing, Microsoft won't call it two different names.) A table can only have one clustered index, data is physically stored in the order of clustered indexes. Primary keys are classified into clustered primary keys and non-clustered primary keys. By default, the cluster master is used.

1. Primary Key and clustered Index

First, clarify the concept that the primary key is not equal to the clustered index. (Isn't that nonsense? If it's the same thing, Microsoft won't call two different names)

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

Primary keys are classified into clustered primary keys and non-clustered primary keys. The default value is the primary key of the aggregation. The following code is the create table code automatically generated by SqlServer. Pay attention to'

CLUSTERED 'indicates the primary key of the aggregation.

/***** 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] (50) not null,

[StudentId] [nvarchar] (50) not null,

[TimeLine] [nvarchar] (50) not null,

[Name] [nvarchar] (50) NULL,

[Phone] [nvarchar] (50) NULL,

[Gender] [nvarchar] (50) NULL,

[Age] [smallint] NULL,

[IdCard] [nvarchar] (50) NULL,

[Nation] [nvarchar] (50) NULL,

[Party] [nvarchar] (50) NULL,

[Birthday] [nvarchar] (50) NULL,

[Married] [nvarchar] (50) NULL,

[School] [nvarchar] (50) NULL,

[Degree] [nvarchar] (50) NULL,

[Province] [nvarchar] (50) NULL,

[City] [nvarchar] (50) NULL,

[Area] [nvarchar] (50) NULL,

[Address] [nvarchar] (50) NULL,

[College] [nvarchar] (50) NULL,

[Major] [nvarchar] (50) NULL,

[Class] [nvarchar] (50) 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) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

2. Multi-column indexes and multiple single-column Indexes

Consider two different indexing methods:

Case 1: add an index for multiple columns c1, c2, and c3 in this order;

Case 2: create three single-column indexes for c1, c2, and c3 respectively;

Question 1: Which index is efficient when you search by c1?

Answer: case2

Question 2: Which index is efficient when searching by C2?

A: case2, and the index of case1 is invalid.

Question 3: What is the efficiency of searching by C1 and C2?

A: I don't know.

Question 4: What is the efficiency of searching by C1, C2, and C3?

Answer: case1

Question 5: Which of the following is the efficiency of search by C2, C3, and C1?

A: case2, because the index is not searched in the order of multiple columns, the index of case1 is not used.

3. Overwrite Query

Overwrite query is simply a query where all query columns are covered by the indexes used.

4. Negative Effects of too many indexes on a single table

When a table has multiple (Single Column) indexes, the Delete, update, and insert operations will take a lot of time to Delete the index and recreate the index.

After combining multiple (Single Column) indexes into one (multiple columns) index, the test results show that the time required for Delete, update, and insert operations is greatly shortened. However, this may affect the query performance of the previous single-column index fields. One of the benefits, trade-offs.