Creating an index - 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

SQL Server 2008 Forums

SQL Server Administration (2008)

Creating an index

Author

Topic

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-16 : 13:41:23

Hello,
If I create an index on columns A and B, then another query needs just column A will the index be used?

How about I have an index on column A and a second index on column B, would I need an index on columns A and B together?

Thanks
DJJ

djj

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-16 : 13:46:09

index on (A,B) can be used even when the criteria or correlation is just for [A].
And indexes on (A) and (B) can both be used when both the columns are referred to as criteria or correlation.

For the first (A,B) the index will not be used if only column [B] is referred to.

Be One with the Optimizer
TG

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-16 : 13:55:30

Thank you.
I have a query that was taking a while so I did an execution plan. Said I need an index on (A, B) but I already had an index on both separately.

By-the-way, I actually knew the bit about not B for A,B. Just not the other.

Thanks again,
djj

djj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-16 : 14:00:45

Your index on just A was likely being used, but A,B was a better index so it was recommending that. It might also have recommended include columns so that it avoided the bookmark lookup on the clustered index.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-16 : 14:01:58

If you were to add this A,B index, your A index is now considered a "duplicate" (provided there is no difference in include columns, include columns muddy the water a bit in regards to "dupes").

"Duplicate" indexes generally should be dropped due to the impact of DML operations.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-10-16 : 14:21:02

Thanks! I was thinking about creating a new index as suggested by the query plan (with include). After reading your (first) explanation I was also thinking that I could delete the single, so thanks for confirming that.

djj

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-10-16 : 14:29:48

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-25 : 03:22:23

i am getting the below mentioned error when i tried to create an index nemed "I_TAB1" on table "TAB1" on column names "T1" and "T2".
what's wrong in this.

CREATE INDEX I_TAB1 ON TAB1(T1,T2)

And in this table T1 is of type (int) and T2 of (char(8000))

Msg 1944, Level 16, State 1, Line 1
Index 'I_TAB1' was not created. This index has a key length of at least 8004 bytes. The maximum permissible key length is 900 bytes.

Does this mean that i cannot create an index on a column or a set of columns whose length is more than 900 bytes?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems ,Inc.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-25 : 05:25:51

quote:
Originally posted by sgondesi

i am getting the below mentioned error when i tried to create an index nemed "I_TAB1" on table "TAB1" on column names "T1" and "T2".
what's wrong in this.

CREATE INDEX I_TAB1 ON TAB1(T1,T2)

And in this table T1 is of type (int) and T2 of (char(8000))

Msg 1944, Level 16, State 1, Line 1
Index 'I_TAB1' was not created. This index has a key length of at least 8004 bytes. The maximum permissible key length is 900 bytes.

Does this mean that i cannot create an index on a column or a set of columns whose length is more than 900 bytes?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems ,Inc.

correct. That is exactly what it means..

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-25 : 05:38:20

quote:
Originally posted by James K

quote:
Originally posted by sgondesi

i am getting the below mentioned error when i tried to create an index nemed "I_TAB1" on table "TAB1" on column names "T1" and "T2".
what's wrong in this.

CREATE INDEX I_TAB1 ON TAB1(T1,T2)

And in this table T1 is of type (int) and T2 of (char(8000))

Msg 1944, Level 16, State 1, Line 1
Index 'I_TAB1' was not created. This index has a key length of at least 8004 bytes. The maximum permissible key length is 900 bytes.

Does this mean that i cannot create an index on a column or a set of columns whose length is more than 900 bytes?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems ,Inc.
correct. That is exactly what it means..

Thanks for that confirmation.
what is the difference between "creating an index on group of columns" and "creating an index on one column and including other columns with keyword include"

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-25 : 11:25:23

"included" columns are not actually part of the key. Their data is simply stored with the index. So it won't help for optimizing correlations or filtering but it may help in retrieval of the data when those included column(s) are required. Of course the additional space needed to include those columns may have a negative impact on performance.

Be One with the Optimizer
TG

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-25 : 14:51:28

quote:
Originally posted by TG

"included" columns are not actually part of the key. Their data is simply stored with the index. So it won't help for optimizing correlations or filtering but it may help in retrieval of the data when those included column(s) are required. Of course the additional space needed to include those columns may have a negative impact on performance.

Be One with the Optimizer
TG

Yes.
these are termed as covered indexes right?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-10-25 : 15:03:02

same concept, yes. But a when an index "covers" the required columns that is only applicable to a given statement. I guess it is semantics but an index itself can't be covered. A covered index only applies to the context of a specific statement.

Be One with the Optimizer
TG

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-27 : 00:36:30

quote:
Originally posted by TG

same concept, yes. But a when an index "covers" the required columns that is only applicable to a given statement. I guess it is semantics but an index itself can't be covered. A covered index only applies to the context of a specific statement.

Be One with the Optimizer
TG

Im unable to understand..
Can you send me any link so that i can develop my knowledge in indexes..

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-27 : 02:19:19

quote:
Originally posted by TG

same concept, yes. But a when an index "covers" the required columns that is only applicable to a given statement. I guess it is semantics but an index itself can't be covered. A covered index only applies to the context of a specific statement.

Be One with the Optimizer
TG

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-28 : 08:38:50

Please anyone respond to this post.

Difference between index scan and table scan.

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-10-28 : 10:29:59

quote:
Originally posted by sgondesi

quote:
Originally posted by TG

same concept, yes. But a when an index "covers" the required columns that is only applicable to a given statement. I guess it is semantics but an index itself can't be covered. A covered index only applies to the context of a specific statement.

Be One with the Optimizer
TG

Im unable to understand..
Can you send me any link so that i can develop my knowledge in indexes..

Here's a link on the include:

http://www.practicalsqldba.com/2013/03/sql-server-part-8-explaining-covering.html

Say you have a query:

select
  a, b
from
  table1
where
  a = ...

Then you created the index on table1: a include b

When SQL server processes the query, it will go through this index finding a match based on column a. There, it will find a pointer to the the entire record plus right there will be the value for column b. Since the value for column b is right there, in the index, sql server does not need to go and retrieve the record. This a covering index for this particular query.

If you change your query to:

select
  a, b
from
  table1
where
  b = ...

This index will not help you since it is not based on column b.

If you change your query to:

select
  a, b, c
from
  table1
where
  a = ...

This index will still be helpful but it will not be covering this particular query. Since SQL Server still needs to fetch the entire record in order to return the value for column c.

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-28 : 11:18:48

First of All Thank you for your support.

please dont get irritated if my questions look silly, i am fresher(2013 passed out BTECH-IT) and started learning SQL Server since one month.

normally when we create a non clustered index on a column of a table without including any columns with it, a tree structure will be created right. In that tree, the leaf level pages will be
1. pointers to the rows in the original table in case of non clustered index and
2. original rows of the table

So my actual doubt is,
even when we dont include other columns in creation of index, entire row of the table will be pointed with the pointer right(in case of a non clustered index)?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-10-28 : 11:39:54

quote:

So my actual doubt is,
even when we dont include other columns in creation of index, entire row of the table will be pointed with the pointer right(in case of a non clustered index)?

That is correct. When we are dealing with one record and that record is in memory, the time involved for SQL Server to fetch that record when there is a pointer to it is insignificant.

But when many records are involved and they are on disk, that is where these bookmark lookups will take a lot of time. Especially when a table has many columns and you only need one or few of the columns.

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-10-28 : 13:22:07

quote:
Originally posted by denis_the_thief

quote:

So my actual doubt is,
even when we dont include other columns in creation of index, entire row of the table will be pointed with the pointer right(in case of a non clustered index)?

That is correct. When we are dealing with one record and that record is in memory, the time involved for SQL Server to fetch that record when there is a pointer to it is insignificant.

But when many records are involved and they are on disk, that is where these bookmark lookups will take a lot of time. Especially when a table has many columns and you only need one or few of the columns.

ya. I got you.

do u mean bookmark lookups means hard disk seeks?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-10-28 : 15:08:31

quote:

ya. I got you.

do u mean bookmark lookups means hard disk seeks?

--
Thanks and Regards
Srikar Reddy Gondesi,
Trainee SQL Server Database Administrator
Miracle Software systems, Inc.

Not quite. A bookmark lookup is the process of finding the actual data in the SQL table, based on an entry found in a non-clustered index.

I just meant it takes longer for SQL Server to retrieve the record from disk as compared to memory.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources