Is SQL Server's clustered index really the best ?,

Is SQL Server's clustered index really the best ?,

-- Simulate the environment first. The following describes how to USE [Temp] GO-drop table [TestTab] truncate table [TestTab] create table [dbo]. [TestTab] ([UserAcount] [varchar] (50) not null, [UserName] [varchar] (50) not null, [crdatetime] [datetime] not null, [value] [numeric] (18, 4) NULL, [Info] [varchar] (50) NULL, CONSTRAINT [PK_TestTab] primary key clustered ([UserAcount] ASC, [UserName] ASC) ON [PRIMARY] GO-drop view [VTestTab] create view [dbo]. [VTestTab] ASSELECT [UserAcount], [UserName], [crdatetime], [value], [Info] FROM [dbo]. [TestTab] WHERE [UserAcount] = CURRENT_USERGO -- INSERT test data: 2538 rows insert into [TestTab] ([UserAcount], [UserName], [crdatetime], [value], [Info]) SELECT CURRENT_USER, name, MAX (crdate), FLOOR (RAND (ABS (CHECKSUM (NEWID () * 1000), NULLFROM master. sys. sysobjectsWHERE LEN (name)> 1 and len (name) <50 group by name

Actual environment:
The data inserted above is the data of a user (CURRENT_USER), and there are more users in the table.
Because it is managed by user (Database sharding, and so on), the column name [UserAcount] is first placed in the first column as the clustered index.
Therefore, all users access the view [VTestTab] and only their data is displayed.

The following query is available:

SELECT * FROM [VTestTab]WHERE [crdatetime] = '2011-06-17 03:18:08.647'

When you look at the execution plan, the query is indexed, because the view changes to the query of the table, as shown below:

SELECT * FROM [TestTab]WHERE [UserAcount] = CURRENT_USERAND [crdatetime] = '2011-06-17 03:18:08.647'

At first, I thought that this clustered index could no longer be optimized. however, according to the principle of clustered index, all data in the table [UserAcount] = CURRENT_USER will be consistent, that is, the clustered index will query all the data of the current user, that is, it will be searched by the primary key column [UserAcount, the time is not accurate to '2017-06-17 03:18:08. 647 'line

So now let's test how many rows are queried by the clustered index!

-- Use serialization to view the lock status before the transaction ends. Set transaction isolation level serializablebegin transelect * FROM [VTestTab] WHERE [crdatetime] = '2017-06-17 03:18:08. 647 'select resource_type, resource_description, request_mode, request_status, request_type, request_lifetime from sys. dm_tran_locks where resource_database_id = DB_ID () and request_session_id = @ SPID COMMIT TRAN

As you can see, the entire hash key is searched. PAGE: IS and KEY range lock (KEY: RangeS-S ). Query the users that match the index key [UserAcount! But only one row is returned.

If the data in the table meets the lock upgrade requirements (more than 5000 or 6000 rows in the table), the table will be upgraded to a shared lock!

Since it is not the best, consider another index positioning!

The current clustered index key column: ([UserAcount], [UserName])

Which of the four index creation methods is better !~

--DROP INDEX IX_TestTab ON [TestTab]CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([UserAcount],[crdatetime])CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime])CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime],[UserAcount])CREATE NONCLUSTERED INDEX IX_TestTab ON [TestTab]([crdatetime])INCLUDE([value],[Info])

-- First: create nonclustered index IX_TestTab ON [TestTab] ([UserAcount], [crdatetime])

In this case, clustered indexes are used and new indexes are not used. Because the first key column of the new index is [UserAcount], which is the same as the clustered index, as long as there is [UserAcount], the clustered index is used for queries! This index is also added as a whitelist.

-- Type 2: create nonclustered index IX_TestTab ON [TestTab] ([crdatetime])

This query uses index search and directly queries by column [crdatetime], but other columns not included in the index use key search. You have to search for non-index columns on the index subpage. And there are a lot fewer locks !!

-- 3: create nonclustered index IX_TestTab ON [TestTab] ([crdatetime], [UserAcount])

The execution plan of this query is consistent with the preceding one. Because the cache plan is parameterized, the statements executed by the two are the same, and the indexes used do not affect the execution plan. And the locked resources are almost the same. Another [UserAcount] column of this index is actually redundant, because non-clustered indexes contain key columns of the clustered index. Therefore, the second column [UserAcount] of this index can be removed.

-- Type 4: create nonclustered index IX_TestTab ON [TestTab] ([crdatetime]) INCLUDE ([value], [Info])

This locked resource is more direct !~ Use a non-clustered index to search for data rows !~

Therefore, for clustered indexes, try to use a unique column as the clustered index, or do not repeat the data of the most key column as much as possible to locate rows as quickly as possible. If there is no unique column, as in the above example, the clustered index and another more effective column serve as the composite index clustered index !~