is the SQL Server clustered index really the best?

--Simulate the environment first, followed by: Use [Temp]go--drop table [testtab]truncate table [Testtab]create table [dbo]. [Testtab] ([Useracount][varchar] (+) not Null,[username][varchar] (NO) null,[crdatetime][datetime] not null,[value][numeric ] (4) Null,[info][varchar] (+) Null,constraint [Pk_testtab] PRIMARY KEY CLUSTERED ([Useracount] asc,[username] ASC) O N [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 lines 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) <50group by name

actual environment:
The data inserted above is a user (current_user) of data, and there are more users in the table.
The column name [Useracount] is placed first in the first column as a clustered index because it is managed by user division (partition, etc.)
Therefore, the user is accessing the view [Vtesttab], looking for only the user's own data.

The following query is available:

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

Look at the execution plan, the query is indexed. Because the view will be turned into a query on the table, as follows:

SELECT * FROM [testtab]where [useracount] = Current_userand [Crdatetime] = ' 2011-06-17 03:18:08.647 '

Starting to think that this use of the clustered index has been unable to optimize. But think, by the principle of a clustered index, using a clustered index lookup, the data in the table [Useracount] = Current_User will all conform, that is, the clustered index will query the current user's data all over again, that is, by the primary key column [ Useracount] Find it again, and did not pinpoint to point in time ' 2011-06-17 03:18:08.647 ' this line

So, test now to see exactly how many rows the clustered index is querying!

--View the lock condition before the end of the transaction using serialization view. SET TRANSACTION Isolation Level serializablebegin transelect * FROM [vtesttab]where [crdatetime] = ' 2011-06-17 03:18:08.64 7 ' 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 [email protected] @SPID  COMMIT Tran

As you can see, the entire hash key has been searched. There is a page lock (Page:is), and the Keys Range Lock (key:ranges-s). The user matching the index key [Useracount] to the query came out! But eventually only one row is returned.

If the data in the table meets the lock escalation requirements (more than five thousand or six thousand table rows), the table will be upgraded to a shared lock!

Since it's not the best, consider another index location!

The current clustered index key is listed as: ([Useracount],[username])

Now consider 4 kinds of index creation method, in which kind of 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 type: CREATE nonclustered INDEX ix_testtab on [Testtab] ([Useracount],[crdatetime])

This still uses the clustered index, and does not use the new index, because the new index first key is listed as [Useracount], as with the clustered index, so as long as there is [Useracount], the query uses the clustered index! This index plus go is also barker.

--second type: CREATE nonclustered INDEX ix_testtab on [Testtab] ([Crdatetime])

This query uses the index lookup, directly by column [Crdatetime] query, but other columns not included in the index, using the key lookup. You also have to look for non-indexed columns in the index sub-page. And the lock is a lot less!!

--The third type: CREATE nonclustered INDEX ix_testtab on [Testtab] ([Crdatetime],[useracount])

The execution plan for this query is consistent with the above, because the cache plan is parameterized, and the indexes used do not affect the execution plan, as are the execution statements. and locked resources are almost the same. Another column of this index [useracount] is actually redundant because the nonclustered index contains the key columns of the clustered index. So the second column of the index [Useracount] can be removed.

--fourth type: CREATE nonclustered INDEX ix_testtab on [Testtab] ([Crdatetime]) INCLUDE ([Value],[info])

This locked resource is more straightforward! ~ Full use of nonclustered index lookup, query directly to the data row! ~

So for clustered indexes, you should try to use a unique column as a clustered index, or the data of the most key column as much as possible, so that you can navigate to the row as fast as you want. If there is no unique column, as in the above example, the clustered index and another more valid column as a combined index clustered index! ~

is the SQL Server clustered index really the best?