index scan and index seek - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

index scan and index seek

Author

Topic

patla4u
Starting Member

10 Posts

Posted - 2011-07-20 : 18:22:12

Hello All,
I have table ACCT with composite primary key as_of_date and Acct_id.
and I have foreign key tran_id1,trans2 and tran3.
Total no. of records are 50,000 and more than that.

Now I am using query
1. select * from acct
output :clustered index scan

2. select * from acct
where as_of_date= '2011-06-30'
Output: clustered index seek

3. select * from acct
where as_of_date= '2011-06-30' and acct_id = 100
Output : clustered index seek

4. select * from acct
where acct_id =100
Output:select-nested loop-index scan
-Key look up
and saying that missing index :create non clustered index on acct.acct_id

5. I already have non clustered index on tran1 ,tran2 and tran3.but
select * from acct
where tran1=225
Output: select- nested loop-index seek(0%)
-key look up(100%)

and saying that missing index : create non clustered index on tran1 includes(as_of_date,acct_id,tran1_tran2,tran3)
(means it's saying that create non clustered index for all the columns)

Question:

(b.)why NO. 4 saying that create non clustered index on acct_id? I already have composite primary key on as_of_date and acct_id (see result No.2 and No.3)

(c.) Why No.5 saying that create non clustered index on tran1 includes(as_of_date,acct_id,tran1_tran2,tran3)but i already have non clustered index on tran1.
Please let me know...

Thanks
L

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-20 : 18:30:18

b. An index leading off with as_of_date is not helpful for a query that is filtering on acct_id. The column needs to be first in the index for it be used there, but then you've got queries that need as_of_date first, so therefore create another index for acct_id.
c. I am not clear if you do actually have an index on tran1. You indicated you have an FK, but that doesn't mean you have an index. Please show us the DDL for your table and indexes. Adding include columns will mean that it's covering your index, which is optimal.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-20 : 18:31:05

I don't see an "a." question.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

mikebird
Aged Yak Warrior

529 Posts

Posted - 2012-07-25 : 03:34:40

can you tell me the syntax for 'filtering on ...' and as_of_date
and what's the query to specify a seek or scan?

mikebird
Aged Yak Warrior

529 Posts

Posted - 2012-07-25 : 03:38:54

can you tell me the syntax for 'filtering on ...' and as_of_date
and what's the query to specify a seek or scan?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 10:28:45

quote:
Originally posted by mikebird

can you tell me the syntax for 'filtering on ...' and as_of_date
and what's the query to specify a seek or scan?

filtering on means effectively addinga WHERE condition

seek or scan depends on columns used in where and select list, their selectivity etc so you cant necessarily write a query to get seek or scan as such. the optimiser will determine whether it can seek predicates from index by considering lot of factors

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources