index scan

Question

index scan

sqlfriend

SSC Guru

Points: 52769
More actions
April 18, 2013 at 12:25 pm

#274177

I have a table that has a composite primary key studentID + Schoolyear, both of the two columns are int,
But looking at the execution plan just by using a select * on the table, I see it is using index scan, why can it use index seek?
Thanks,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

sqlfriends (4/18/2013)
I have a table that has a composite primary key studentID + Schoolyear, both of the two columns are int,
But looking at the execution plan just by using a select * on the table, I see it is using index scan, why can it use index seek?
Thanks,

One, hard to know since all you have provided is SELECT *. If you show us the complete query we could probably give you an answer.

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 2

As far as I know select * ALWAYS does a scan. You'd have to add WHERE conditions against the indexed columns to get a seek.

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

See the notes in my code. My understanding, too, is that you will always get a scan (index or table) unless you provide a filter. E.g. Select * FROM x will never do a seek. See my code and notes:

-- based on what we know you have something like this:

IF OBJECT_ID('tempdb..#postDDLNextTime') IS NOT NULL

DROP TABLE #postDDLNextTime;

CREATE TABLE #postDDLNextTime (student_id int NOT NULL, school_year int NOT NULL, constraint pk_x primary key(student_id, school_year));

INSERT INTO #postDDLNextTime

SELECT 1,2000 UNION ALL SELECT 2,2000 UNION ALL SELECT 3,2001;

GO

-- This will cause an index scan

SELECT *

FROM #postDDLNextTime;

-- This will cause an index seek

SELECT * FROM #postDDLNextTime

WHERE student_id=1;

DROP TABLE #postDDLNextTime;

GO

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

sqlfriend SSC Guru Points: 52769 More actions · Answer 4

here are the table definition in attachment, and select. with a filter

No matter what I use the execution plan uses index scan.

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 5

Your PK and consequently your index is on StudentID + SchoolYear.

Your where statement only queries half the key so you're still going to get a scan. If you want to seek on the PK your conditional would have to be

where studentID = ## and schoolyear = ####

You could add a non-clustered index on Schoolyear to get a seek.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

sqlfriends (4/18/2013)
here are the table definition in attachment, and select. with a filter
No matter what I use the execution plan uses index scan.

SELECT

[StudentID] ,

[SchoolYear] ,

[SeqNbr] ,

[SchoolID] ,

[RangeCd] ,

[ProgramID] ,

[AssgnStatusCd] ,

[AssgnMethodCd] ,

[WaitingListAppDt] ,

[LotteryNbr] ,

[OriginalRank] ,

[OverrideRank]

FROM

[Assignment].[dbo].[schWaitingListAssignment]

WHERE

schoolyear = 2013

This is always going to do a scan.

You may get seek if you flip the columns of your PK so that schoolyear is first.

This also depends on how you query the table most of the time if you want to do that.

Creating a nonclustered index on schoolyear may not help unless you made it a covering index.

sqlfriend SSC Guru Points: 52769 More actions · Answer 7

Thanks, tried to add filter with where studentID=.. and schoolyear=.. this time it uses the index seek.

The reason I asked this is because we have a view that is in slow performance, and the underlying table of the view is using this table.and the view's execution plan is index scan for that table.

And the filter is not either studentId or schoolyear, it is WHERE AssgnStatusCd = 'A',

does that mean if I add a non-clustered index to the table for the column AssgnStatusCd , it will become index seek?

Thanks

Lowell SSC Guru Points: 323504 More actions · Answer 8

sqlfriends (4/18/2013)
Thanks, tried to add filter with where studentID=.. and schoolyear=.. this time it uses the index seek.
The reason I asked this is because we have a view that is in slow performance, and the underlying table of the view is using this table.and the view's execution plan is index scan for that table.
And the filter is not either studentId or schoolyear, it is WHERE AssgnStatusCd = 'A',
does that mean if I add a non-clustered index to the table for the column AssgnStatusCd , it will become index scan?
Thanks

in that example, probably not.

AssgnStatusCd = 'A' is probably not a good filter for a unique criteiria;

for exmaple, if 50% of the records are 'A', the index is not very selective, and it would probably use the clustered index of the table instead of an index that happen to be on that column.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlfriend SSC Guru Points: 52769 More actions · Answer 9

Thanks, it seems I can only leave it as it is as a index scan for the view, and optimize other parts.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

http://www.sqlservercentral.com/articles/Indexing/68439/

http://www.sqlservercentral.com/articles/Indexing/68563/

http://www.sqlservercentral.com/articles/Indexing/68636/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass