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
 Site Related Forums
 Article Discussion
 Article: SQL Server Indexes: The Basics
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 11/26/2007 :  09:01:19  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote

Indexes directly affect the performance of database applications. This article uses analogies to describe how indexes work. The estimated execution plan feature of the Query Window is utilized to compare the performance of two queries in a batch.



Read SQL Server Indexes: The Basics

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 11/27/2007 :  02:01:29  Show Profile  Reply with Quote
I was going to comment on this article, but I get totally different execution plans then what you show in most of your examples. What build of SQL 2005 did you use? Is the adventureworks db you used somehow different than what can be downloaded from here http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004


-ec

Edited by - eyechart on 11/27/2007 02:17:13
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 11/27/2007 :  02:15:47  Show Profile  Reply with Quote
The indexes are definitely different on the new download available from microsoft. there is not a non-clustered index on ModifiedDate on the SalesOrderDetail table as indicated in the article. So that mystery is solved.

Comments on article:

1. 'key lookup' is used in SQL 2K5 SP2 and newer to indicate a clustered index bookmark lookup instead of 'clustered index seek'.
2. You detailed covered indexes, which I would consider a somewhat advanced topic, but did not mention composite indexes at all. the last couple of examples you show can be tuned with appropriate composite indexes.



-ec

Edited by - eyechart on 11/27/2007 02:54:08
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 11/27/2007 :  04:39:14  Show Profile  Visit spirit1's Homepage  Reply with Quote
you didn't get low disk space on any of your servers, huh ec?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3603 Posts

Posted - 11/27/2007 :  04:58:13  Show Profile  Reply with Quote
I would also suggest (with this being on 2005), that you mention INCLUDE. This (I think) is one of the best features of indexes in SQL 2005. They really help on larger databases as you can use fields in your indexes and they do not count towards the cost. They are also a way of getting a covering index very cheaply.
Go to Top of Page

Will H
Yak Posting Veteran

56 Posts

Posted - 12/05/2007 :  15:54:14  Show Profile  Reply with Quote
Thanks for the great tutorial - The more I read this site the more I realize what I don't know.

Just A few questions, I'm not really sure on these things...

When you add a foreign key constraint, is a non-clustered index automatically created??

Is it correct to say that Nonclustered indexes can only include columns in the same table??


---------------------------------------------------------
SSRS Kills Kittens.

Edited by - Will H on 12/05/2007 16:08:57
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 12/05/2007 :  17:12:07  Show Profile  Visit spirit1's Homepage  Reply with Quote
> When you add a foreign key constraint, is a non-clustered index automatically created??
No.

> Is it correct to say that Nonclustered indexes can only include columns in the same table??
Yes.

indexes in general are table scoped.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

kathik
Starting Member

USA
3 Posts

Posted - 12/07/2007 :  20:00:12  Show Profile  Reply with Quote
Thanks for your comments. The goal of the article was to give an introductory overview of how indexes work.

Just a few comments about the comments:

The version of AdventureWorks was installed along with the SQL Server instance. I'll download the latest version for future articles.

I did briefly mention the new Include option. Take a look at the paragraph on covering indexes.

I also need to install SP2 on my laptop. I didn't realize they had the new 'key lookup' to indicate a bookmark lookup. I am really glad that they changed this!

There are some examples with multi-column indexes also called composite indexes.

The point of the last two queries was to show how SQL Server can use two indexes in one query. If you have a composite index on two columns and have a where clause with "OR", a scan will result. If you have two separate indexes, the two indexes can be used in tandem with "OR".

Thanks,
Kathi

Kathi
Go to Top of Page

joew
Starting Member

1 Posts

Posted - 12/18/2007 :  10:05:51  Show Profile  Reply with Quote
Great intro to indexes, Kathi! Thanks!
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 01/30/2008 :  06:12:09  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
is there an intro to indexes but for the very begginers
that only start with SQL and work correctly from beggning?

thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 01/30/2008 :  07:25:41  Show Profile  Visit spirit1's Homepage  Reply with Quote
http://weblogs.sqlteam.com/mladenp/archive/2007/09/18/Back-To-Basics-What-is-a-Clustered-and-a-Non-Clustered.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out
Go to Top of Page

pelegk2
Aged Yak Warrior

Israel
723 Posts

Posted - 01/30/2008 :  07:50:25  Show Profile  Visit pelegk2's Homepage  Send pelegk2 an ICQ Message  Reply with Quote
10X:)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)
Go to Top of Page

SergeiSF
Starting Member

1 Posts

Posted - 06/10/2009 :  16:59:10  Show Profile  Reply with Quote
Thanks a lot for the article - it's well written, clear and extremely helpful.

Blog on .Net, C# and MS SQL: http://sergeihomeblog.blogspot.com
Go to Top of Page

_Gazza_
Starting Member

1 Posts

Posted - 07/07/2011 :  11:11:01  Show Profile  Reply with Quote
Hi Kathi

I have a fairly complicated SP that calls nested functions and eventually references a view. One of the tables in the view has a non-clustered index scan happening on it. The way the view is being used there are no predicates against this table (no columns from it in the where clause). Im trying to get SQL to do an index seek on this table by adding a new index based on the columns used to join to this table, but I just cant get SQL to do it. Am I taking the correct approach?

I know it is hard to give advice without actaully looking at my query but I guess I was more looking for theoretical advice on indexes for joins. ie. most posts out there seem to be about creating appropriate indexes when you have the columns in a where clause but i want to understand how SQL uses indexes on a table when it is just being used in a join.

Many thanks
Garry
Go to Top of Page

Ibid
Starting Member

4 Posts

Posted - 01/26/2012 :  10:53:35  Show Profile  Reply with Quote
Thank you for the informative article - indexes are indeed a very powerful tool to optimize data access. However, you should be aware of some potential risks that come with it. You can read more about it here: unspammed
Go to Top of Page
  Previous Topic Topic Next Topic  
 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.12 seconds. Powered By: Snitz Forums 2000