| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-11-26 : 09:01:19
|
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
Master Smack Fu Yak Hacker
3575 Posts |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-11-27 : 02:15:47
|
| 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 |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-27 : 04:39:14
|
you didn't get low disk space on any of your servers, huh ec?  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-27 : 04:58:13
|
| 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. |
|
|
|
Will H
Yak Posting Veteran
56 Posts |
Posted - 2007-12-05 : 15:54:14
|
| 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. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-12-05 : 17:12:07
|
| > 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 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
kathik
Starting Member
3 Posts |
Posted - 2007-12-07 : 20:00:12
|
| 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,KathiKathi |
|
|
|
joew
Starting Member
1 Post |
Posted - 2007-12-18 : 10:05:51
|
| Great intro to indexes, Kathi! Thanks! |
|
|
|
pelegk2
Aged Yak Warrior
723 Posts |
Posted - 2008-01-30 : 06:12:09
|
| is there an intro to indexes but for the very begginersthat only start with SQL and work correctly from beggning?thnaks in advancepelegIsrael -the best place to live in aftr heaven 9but no one wan't to go there so fast -:) |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-01-30 : 07:25:41
|
| http://weblogs.sqlteam.com/mladenp/archive/2007/09/18/Back-To-Basics-What-is-a-Clustered-and-a-Non-Clustered.aspx_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
pelegk2
Aged Yak Warrior
723 Posts |
Posted - 2008-01-30 : 07:50:25
|
| 10X:)Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:) |
|
|
|
SergeiSF
Starting Member
1 Post |
Posted - 2009-06-10 : 16:59:10
|
| 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 |
|
|
|
_Gazza_
Starting Member
1 Post |
Posted - 2011-07-07 : 11:11:01
|
| Hi KathiI 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 thanksGarry |
|
|
|
Ibid
Starting Member
4 Posts |
Posted - 2012-01-26 : 10:53:35
|
| 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 |
|
|
|
|
Article: SQL Server Indexes: The Basics