Index overhead - 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 2000 Forums

SQL Server Development (2000)

Index overhead

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-03-13 : 10:07:04

I have a table with three colum as
id,date1,date2

Three index is created as
ClusterIndex1 = id
Nonclusterindex1 = date1,id
Nonclusterindex2 = date2,id

I have written 2 different query,in that id, date1 is in one where clause id , date2 is another where clause.

I suggest index creation should be

ClusterIndex1 = id
Nonclusterindex1 = date1
Nonclusterindex2 = date2

Unnecessary adding some columns in index creation will affect performance. If this is correct can u send any link for the proof.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-13 : 12:47:02

Is this for an exam or a job interview?

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-03-14 : 05:07:06

we are adding index to improve performance in one of our project. not exam or interview.
After creation i can see index space used by this two scenario is same.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-03-14 : 08:05:55

quote:
Originally posted by niranjankumark

Unnecessary adding some columns in index creation will affect performance. If this is correct can u send any link for the proof.

Why don't you test in a dev environment with the specific type of workload that your apps generate and see if there is a noticable overhead and, if so, whether it's acceptable or not.

--
Gail Shaw
SQL Server MVP

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-14 : 08:13:33

Agreed. There's no "proof" that an index will improve your queries unless you test them. I mentioned that in another of your posts:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=120859

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-03-14 : 08:30:54

quote:
Originally posted by niranjankumark
After creation i can see index space used by this two scenario is same.

It will be. All nonclustered indexes contain the clustering key as that location of the row. It's needed for lookups/

--
Gail Shaw
SQL Server MVP

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-03-15 : 10:12:16

i believe explanation given in link is different from this one .
you can see id has clustered index , but it is created once again with date columns.
i have created 3 index .If the number of indexes increases mean index space will
increase. but here in the same index (non clustered ) have added one more column (ID).
so the index space remains same only . So what would be the different between this two type
of creation.i tested with 10000 rows in bot scenarios. execution plan is same only.
logical and physical read also same. we dont have access to see the execution plan to see in
our project database. so i am experting valuable answer from you folks.Kindly provide.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-15 : 12:34:50

quote:
so i am experting valuable answer from you folks.Kindly provide

You have been given the same answer by 2 different people. I cannot figure out what you're looking for. I'd recommend reading SQL Server Books Online under "indexes", it describes the mechanics of indexing and will also answer your question.

niranjankumark
Posting Yak Master

164 Posts

Posted - 2009-03-16 : 02:17:11

Hi rob , some valuable points are given , but i think it is not valuable for my scenario.Even in books online this scenaio will not be available , it is like generic only .By real time only we will get this ...
I explain you once again clearly ...

I have a table with three colum as
id,date1,date2

Three index is created as
scenario - 1
============
ClusterIndex1 = id
Nonclusterindex1 = date1,id
Nonclusterindex2 = date2,id

I have written 2 different query,in that id, date1 is in one where clause id , date2 is another where clause.

I suggest index creation should be

scenario - 2
============
ClusterIndex1 = id
Nonclusterindex1 = date1
Nonclusterindex2 = date2

But DBA ( i cannot contact him directly ) is created like that( scenario - 1 ) . But if you see the execution plan, logical and physical read , index space are same. but the id goes on clustered index only not non clustered. So what would be the difference between this two.Dont say if it goes clustered mean go head with that.I want strong explantion to make arguement with my DBA. ex: index space is different, executioon plan is different, non cluster also have cluster key so adding one more time for the same ID is good only .. like this some explantion i need.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-03-16 : 12:03:33

As I said above, the ID field will be in the nonclustered indexes even if you don't add it in. The clustering key is always part of a nonclustered index as it is the row's 'address' and is used for lookups.

That information is in Books Online under the topic "Nonclustered index Structure". I quote:

quote:
Nonclustered indexes can be defined on a table or view with a clustered index or a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator.

quote:
If the table has a clustered index, or the index is on an indexed view, the row locator is the clustered index key for the row.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources