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)
 Creating an index
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 10/16/2013 :  13:41:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 10/16/2013 :  13:46:09  Show Profile  Reply with Quote
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
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 10/16/2013 :  13:55:30  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/16/2013 :  14:00:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/16/2013 :  14:01:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/

Edited by - tkizer on 10/16/2013 14:02:12
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 10/16/2013 :  14:21:02  Show Profile  Reply with Quote
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
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35940 Posts

Posted - 10/16/2013 :  14:29:48  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sgondesi
Posting Yak Master

India
193 Posts

Posted - 10/25/2013 :  03:22:23  Show Profile  Reply with Quote
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.

Edited by - sgondesi on 10/25/2013 03:52:56
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/25/2013 :  05:25:51  Show Profile  Reply with Quote
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..
Go to Top of Page

sgondesi
Posting Yak Master

India
193 Posts

Posted - 10/25/2013 :  05:38:20  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 10/25/2013 :  11:25:23  Show Profile  Reply with Quote
"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
Go to Top of Page

sgondesi
Posting Yak Master

India
193 Posts

Posted - 10/25/2013 :  14:51:28  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5932 Posts

Posted - 10/25/2013 :  15:03:02  Show Profile  Reply with Quote
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
Go to Top of Page

sgondesi
Posting Yak Master

India
193 Posts

Posted - 10/27/2013 :  00:36:30  Show Profile  Reply with Quote
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.
Go to Top of Page

sgondesi
Posting Yak Master

India
193 Posts

Posted - 10/27/2013 :  02:19:19  Show Profile  Reply with Quote
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.
Go to Top of Page

sgondesi
Posting Yak Master

India
193 Posts

Posted - 10/28/2013 :  08:38:50  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
581 Posts

Posted - 10/28/2013 :  10:29:59  Show Profile  Reply with Quote
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.
Go to Top of Page

sgondesi
Posting Yak Master

India
193 Posts

Posted - 10/28/2013 :  11:18:48  Show Profile  Reply with Quote
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.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
581 Posts

Posted - 10/28/2013 :  11:39:54  Show Profile  Reply with Quote
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.
Go to Top of Page

sgondesi
Posting Yak Master

India
193 Posts

Posted - 10/28/2013 :  13:22:07  Show Profile  Reply with Quote
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.

Edited by - sgondesi on 10/28/2013 14:31:42
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
581 Posts

Posted - 10/28/2013 :  15:08:31  Show Profile  Reply with Quote
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.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next 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.19 seconds. Powered By: Snitz Forums 2000