I am trying to speed up a table and as I was experimenting I ran into this (what I think is) odd occurrence. I created a clustered index and a nonclustered index that should be the same thing. However, as I have run queries against the table I have found that SQL Server always wants to use the nonclustered index instead of the matching clustered index. On top of that, when needed SQL Server will properly do an index seek on the nonclustered index, but will always perform a scan on the clustered index.
Why does SQL Server prefer the nonclustered index?
And how can I rewrite this so I still have the performance increase but only the clustered index?
I have the following table structure:
CREATE TABLE [dbo].[Variables](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Header] [varchar](255) NULL,
[FullVariables] [varchar](max) NULL
)
Clustered index:
ALTER TABLE [dbo].[Variables] ADD CONSTRAINT [PK_Variables] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
Nonclustered index:
CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20190307-091011] ON [dbo].[Variables]
(
[ID] ASC
)
INCLUDE ( [Header],
[FullVariables])
My current knowledge leads me to believe that in this case both of those indexes should contain the data laid out in the same fashion with [ID] being the key column and then [Header] and [FullVariables] as extra data contained on the index instead of being pointers. If you have some source of knowledge that you could link I am more than eager to read more.
I should specify that I don't always want a seek and I understand that a scan is better in some cases (otherwise why would be have it). The table contains about 60GB of data due to row size (several million) multiplied by the varchar(MAX)
(which contains strings that are 16000+ characters long). Before inserting into the table, a scan is done to ensure no duplicates are insterted (matching on Header
for elimination and on FullVariables
). Then the table is joined in several views on the ID field where the seeks are desired.