July 9, 2015 at 6:10 pm
I have the following table:
CREATE TABLE [dbo].[tblAlternateReferences](
[SourceSystemId] [int] NOT NULL,
[PolicyNumber] [nvarchar](20) NOT NULL,
[MigratedSystemId] [int] NOT NULL,
[ReferenceNumber] [nvarchar](20) NOT NULL,
[AlternateReferenceId] [int] NOT NULL,
[CreatedTS] [datetime] NOT NULL,
[CreatedBy] [nvarchar](50) NOT NULL,
[ModifiedTS] [datetime] NULL,
[ModifiedBy] [nvarchar](50) NULL,
[isDeleted] [bit] NULL,
[RV] [timestamp] NOT NULL,
CONSTRAINT [PK_tblAlternateReferences] PRIMARY KEY CLUSTERED
(
[SourceSystemId] ASC,
[PolicyNumber] ASC,
[MigratedSystemId] ASC,
[ReferenceNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
If I run the following query it does an index scan:
SELECT PolicyNumber FROM tblAlternateReferences WHERE ReferenceNumber = '1410492'
I'm just wondering why this would be a scan when the ReferenceNumber field is part of the PK?
I can obviously add a non-clustered index to get a seek but I was just curious.
July 9, 2015 at 7:23 pm
It has to scan because ReferenceNumber is not the primary sort of the key (it's the fourth sort, or "quaternary" if I want to be strange :-)).
The order of the key columns determines how the index is sorted. The primary sort is on the first column, and then for rows with the same value for that column, they are sorted according to the second column, and so on.
It's very much the same way a phone book is usually sorted. Phone books are usually sorted by last name, and then secondarily sorted by first name.
While first name is part of the key for the phone book index, it doesn't help if your only search criterion is on first name.
If I asked you to find all the people in a phone book with the last name 'Wilkins', it would be an easy task, because that's the primary sort. If I asked you to find all the people in the phone book with the first name 'Jacob', you'd have no choice but to read through the entire thing, because you're not accessing the data using the primary sort first.
Hopefully that helps clear it up!
July 9, 2015 at 7:46 pm
Thanks, Jacob!
I assumed it was something along those lines but thank you for clarifying.
July 10, 2015 at 9:57 am
if you run: SELECT PolicyNumber FROM tblAlternateReferences WHERE [SourceSystemId] = '123'
it will do a index seek. I think it is sorted by 'SourceSystemId'.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply