Why is my primary key slowing my requests?

Question

I am using SQL Server 2008, and in one of my tables I implemented a (clustered) primary key in my identifier. Here is the table description:

Identifier: - IdentifierId int NOT NULL PK - Alias ​​nvarchar (200) - DataType int NOT NULL

I made two indexes: one for Alias ​​and one for DataType. However, I noticed something strange. When executing the following query:

SELECT * FROM IDENTIFIER WHERE DataType = 1 

A query actually works slower with indexes and a primary key than without them; It takes about 10 seconds longer! Indexes are not fragmented - I checked - and I also use this

 GO CHECKPOINT; GO DBCC DROPCLEANBUFFERS; GO DBCC FREEPROCCACHE; GO 

before the request itself.

This table is quite large, with several million entries on it. Indexes and PCs play a vital role in most queries, but in this case I cannot understand why the query works slower with them. Any ideas?

Thank you in advance

EDIT: The execution plan shows that only a clustered index is used, and the DataType variable currently reaches 150.

Answer 1

Create a composite nonclustered coverage index on a DataType with the Alias column enabled and delete the individual indexes on the Alias and DataType :

 CREATE NONCLUSTERED INDEX NC_DataType_I_Alias ON tblIdentifier(DataType) INCLUDE (Alias) 

Answer 2

If you select *, the system should still get each column. Therefore, the optimizer often determines that it is faster to use a clustered index scan (remember that a clustered index is not really an index - it's just data ordered in the specified order) than using a search on a different index in combination with a bookmark search based on the primary key for extraction of additional lines.

Thus, the key to performance is to have a non-clustered index (this is a bad name, indeed, since non-clustered indexes often far outperform cluster indexes) and INCLUDE extra columns in the index, so it becomes covering . In the case of pre-SQL Server 2005, you just need to add columns to the end of the index.

So, basically, the primary key is good, but it doesn’t have to determine your choice of clustering, and you usually need to rely on non-clustered indexes (with corresponding INCLUDE ed columns) for performance on most selective operations, with a clustered index designed for the least selective and most often sorted case.