| Author |
 Topic  |
|
|
patla4u
Starting Member
10 Posts |
 Posted - 07/20/2011 : 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
USA
35007 Posts |
Posted - 07/20/2011 : 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
USA
35007 Posts |
|
|
mikebird
Aged Yak Warrior
United Kingdom
518 Posts |
Posted - 07/25/2012 : 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
United Kingdom
518 Posts |
Posted - 07/25/2012 : 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
India
47023 Posts |
Posted - 07/25/2012 : 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/
|
|
|
| |
Topic |
|
index scan and index seek
