SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Indexing related issues
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/05/2013 :  12:33:47  Show Profile  Reply with Quote
quote:


That is pretty much correct depending on what you mean by 'need'. In #1, if SQL Server determines this to be efficient, then yes. But SQL Server may opt to do a table scan if that index is not helpful, in which case it would go directly to the heap.

Same for #2. Also if the index is covering SQL Server doesn't need to do the 2nd part. The 2nd part is what you are describing as the bookmark lookup.



Can you go detail about point which you mentioned saying

"But SQL Server may opt to do a table scan if that index is not helpful" ?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/09/2013 :  04:32:18  Show Profile  Reply with Quote
quote:

That is pretty much correct depending on what you mean by 'need'. In #1, if SQL Server determines this to be efficient, then yes. But SQL Server may opt to do a table scan if that index is not helpful, in which case it would go directly to the heap.

Same for #2. Also if the index is covering SQL Server doesn't need to do the 2nd part. The 2nd part is what you are describing as the bookmark lookup.


quote:

Can you go detail about point which you mentioned saying

"But SQL Server may opt to do a table scan if that index is not helpful" ?




Do you mean to say that,
"If the selectivity of rows is less, then index is useful and when selectivity is more, table scan is useful " ?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

Edited by - sgondesi on 12/09/2013 04:34:03
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 12/09/2013 :  11:46:06  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

quote:


That is pretty much correct depending on what you mean by 'need'. In #1, if SQL Server determines this to be efficient, then yes. But SQL Server may opt to do a table scan if that index is not helpful, in which case it would go directly to the heap.

Same for #2. Also if the index is covering SQL Server doesn't need to do the 2nd part. The 2nd part is what you are describing as the bookmark lookup.



Can you go detail about point which you mentioned saying

"But SQL Server may opt to do a table scan if that index is not helpful" ?




Hi.

I may have misunderstood you but you said that SQL Server would need to go through the non-clustered index first. It would depend on the query.

For example. You have a Phonebook table which is a heap (no clustered index). With a non-clustered index on column LastName. Your query is: select * from Phonebook where TelephoneNumber = 'xxx'.

In this case the non-clusted index wouldn't be helpful so I think SQL Server would go directly to the Heap.

Have you looked at Execution Plans yet?
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 12/09/2013 :  11:49:34  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

quote:

That is pretty much correct depending on what you mean by 'need'. In #1, if SQL Server determines this to be efficient, then yes. But SQL Server may opt to do a table scan if that index is not helpful, in which case it would go directly to the heap.

Same for #2. Also if the index is covering SQL Server doesn't need to do the 2nd part. The 2nd part is what you are describing as the bookmark lookup.


quote:

Can you go detail about point which you mentioned saying

"But SQL Server may opt to do a table scan if that index is not helpful" ?




Do you mean to say that,
"If the selectivity of rows is less, then index is useful and when selectivity is more, table scan is useful " ?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.



The other way around, here is an article: http://www.programmerinterview.com/index.php/database-sql/selectivity-in-sql-databases/
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/10/2013 :  05:10:13  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:
Originally posted by sgondesi

quote:


That is pretty much correct depending on what you mean by 'need'. In #1, if SQL Server determines this to be efficient, then yes. But SQL Server may opt to do a table scan if that index is not helpful, in which case it would go directly to the heap.

Same for #2. Also if the index is covering SQL Server doesn't need to do the 2nd part. The 2nd part is what you are describing as the bookmark lookup.



Can you go detail about point which you mentioned saying

"But SQL Server may opt to do a table scan if that index is not helpful" ?




Hi.

I may have misunderstood you but you said that SQL Server would need to go through the non-clustered index first. It would depend on the query.

For example. You have a Phonebook table which is a heap (no clustered index). With a non-clustered index on column LastName. Your query is: select * from Phonebook where TelephoneNumber = 'xxx'.

In this case the non-clusted index wouldn't be helpful so I think SQL Server would go directly to the Heap.

Have you looked at Execution Plans yet?



Yes obviously it should go directly to heap right.
Because the column on which index is there is not being used in the where clause.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/10/2013 :  05:55:28  Show Profile  Reply with Quote
quote:


Hi.

I may have misunderstood you but you said that SQL Server would need to go through the non-clustered index first. It would depend on the query.

For example. You have a Phonebook table which is a heap (no clustered index). With a non-clustered index on column LastName. Your query is: select * from Phonebook where TelephoneNumber = 'xxx'.

In this case the non-clusted index wouldn't be helpful so I think SQL Server would go directly to the Heap.

Have you looked at Execution Plans yet?



And i have seen the execution plans in the case which you have asked me to do so.
SQL Server is opting for a table scan when the indexed column is not mentioned in the where clause.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/10/2013 :  09:03:19  Show Profile  Reply with Quote
quote:


The other way around, here is an article: http://www.programmerinterview.com/index.php/database-sql/selectivity-in-sql-databases/




I have read the above page.
I already has that idea towards selectivity.


--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000