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)
 Indexing related issues
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/02/2013 :  05:12:35  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by sgondesi

one more doubt..

Non clustered Index with clustered index is effective or non clustered index with heap is effective?


see this white paper which compares both scenarios

http://technet.microsoft.com/en-us/library/cc917672.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Thanks for the response.
I have already gone through that link earlier. But i Will go through once again.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/03/2013 :  08:46:48  Show Profile  Reply with Quote
I have referred the link.
Difference between having a
1. clustered index on heap with no other clustered indexes and
2. non clustered index on heap.

But i need to know the difference between having a
1. non clustered index on clustered table and
2. non clustered index on heap.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 12/03/2013 :  11:36:21  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi


But i need to know the difference between having a
1. non clustered index on clustered table and
2. non clustered index on heap.




I think it is the same. i.e. whether you have an index on a clustered table or you have that same index on a heap table, the index should behave the same. And give the same performance if your where clause specifies that column.

But I look forward to anyone correcting me.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/04/2013 :  03:40:03  Show Profile  Reply with Quote
quote:
Originally posted by denis_the_thief

quote:
Originally posted by sgondesi


But i need to know the difference between having a
1. non clustered index on clustered table and
2. non clustered index on heap.




I think it is the same. i.e. whether you have an index on a clustered table or you have that same index on a heap table, the index should behave the same. And give the same performance if your where clause specifies that column.

But I look forward to anyone correcting me.



one small doubt..
what are the contents of leaf level of non- clustered index on a heap table?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/04/2013 :  05:27:23  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

quote:
Originally posted by denis_the_thief

quote:
Originally posted by sgondesi


But i need to know the difference between having a
1. non clustered index on clustered table and
2. non clustered index on heap.




I think it is the same. i.e. whether you have an index on a clustered table or you have that same index on a heap table, the index should behave the same. And give the same performance if your where clause specifies that column.

But I look forward to anyone correcting me.



one small doubt..
what are the contents of leaf level of non- clustered index on a heap table?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


From MSDN documentation


•If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/04/2013 :  05:42:09  Show Profile  Reply with Quote
quote:

From MSDN documentation


•If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Hello Sir,
The leaf level of clustered index has original data pages.
so are you telling that the leaf level of a non-clustered index consists of row locators or rowid's for the rows present in the data pages?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

Edited by - sgondesi on 12/04/2013 06:36:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/04/2013 :  06:19:47  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

quote:

From MSDN documentation


•If the table is a heap, which means it does not have a clustered index, the row locator is a pointer to the row. The pointer is built from the file identifier (ID), page number, and number of the row on the page. The whole pointer is known as a Row ID (RID).


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs



Hello Sir,
The leaf level of clustered index has original data pages.
so are you telling that the leaf level of a clustered index consists of row locators or rowid's for the rows present in the data pages?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


Nope you asked about nonclustered index and not clustered
The above statement applies to nonclustered index ie leaf nodes of non clustesred index consist of a row locator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/04/2013 :  06:36:12  Show Profile  Reply with Quote
quote:

Nope you asked about nonclustered index and not clustered
The above statement applies to nonclustered index ie leaf nodes of non clustesred index consist of a row locator

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




Sorry i miss typed in the post.
I am telling about non-clustered index only.
thank you.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/04/2013 :  06:41:35  Show Profile  Reply with Quote
So, when the there is a non clustered index over a clustered table(table with clustered index),
the non clustered index pages just have the pointers to the clustered index pages right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/04/2013 :  08:03:11  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

So, when the there is a non clustered index over a clustered table(table with clustered index),
the non clustered index pages just have the pointers to the clustered index pages right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


yes you're correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/04/2013 :  09:15:30  Show Profile  Reply with Quote
When we have to use rowid's for data retrieval(in case of non-clustered index on a heap table), using those(rowid's) we access data from data pages only right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/04/2013 :  09:44:15  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

When we have to use rowid's for data retrieval(in case of non-clustered index on a heap table), using those(rowid's) we access data from data pages only right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


We cant use RowIDs directly. they're internally maintained for pointing to data pages from non clustered index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/04/2013 :  09:57:00  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

quote:
Originally posted by sgondesi

When we have to use rowid's for data retrieval(in case of non-clustered index on a heap table), using those(rowid's) we access data from data pages only right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.


We cant use RowIDs directly. they're internally maintained for pointing to data pages from non clustered index.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs




That is fine.
next issue,

Since we are talking about a non clustered index on a heap table, the data pages in which the table's data is present are organized into a particular order or not?
Since there is a non clustered index on it, those data pages should be organized in some useful order right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 12/04/2013 :  10:09:00  Show Profile  Reply with Quote
quote:


That is fine.
next issue,

Since we are talking about a non clustered index on a heap table, the data pages in which the table's data is present are organized into a particular order or not?
Since there is a non clustered index on it, those data pages should be organized in some useful order right?





Since it is a heap, no, the table's data is not organized in a particular order.

It would be like a phone book, where everything is out of order. But at the back of the phonebook would be a section in alphabetical order, you could look up the person's name and it would say go to page xxx.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/04/2013 :  11:45:41  Show Profile  Reply with Quote
quote:

Since it is a heap, no, the table's data is not organized in a particular order.

It would be like a phone book, where everything is out of order. But at the back of the phonebook would be a section in alphabetical order, you could look up the person's name and it would say go to page xxx.



Sir, we are talking about non clustered index on heap table right.
So the data pages of the heap table will be arranged into a btree right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 12/04/2013 :  11:50:50  Show Profile  Reply with Quote
quote:


Sir, we are talking about non clustered index on heap table right.
So the data pages of the heap table will be arranged into a btree right?





"On" in this case does not mean it is physically on the table. The index and the table would be 2 separate physically entities.

The Index would be arraganed into a btree with the leaf nodes pointing to the records in the heap. The Heap itself would be unordered.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/04/2013 :  12:22:09  Show Profile  Reply with Quote
quote:

"On" in this case does not mean it is physically on the table. The index and the table would be 2 separate physically entities.

The Index would be arraganed into a btree with the leaf nodes pointing to the records in the heap. The Heap itself would be unordered.



ok another doubt..
If a table has a clustered index on it, the data pages of the table will be present in the leaf level of the btree right?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 12/04/2013 :  13:08:21  Show Profile  Reply with Quote
quote:


ok another doubt..
If a table has a clustered index on it, the data pages of the table will be present in the leaf level of the btree right?





Yes, Clustered index leaf-level pages contain the data in the table.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 12/05/2013 :  04:23:59  Show Profile  Reply with Quote
Tell me whether my following assumptions are correct or not.

1. If there is a non clustered index on a heap table then, to retrieve the records we need to first go through the non clustered index then -> we get a row locator or rowid then -> we need to look into the heap for actual record .

2. If there is a non clustered index on a clustered table(table which has a clustered index) then, to retrieve the records we need to go through the non clustered index then -> we get a clustered index pointer then -> we get the record from the data pages in the leaf level on the clustered index.


--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
593 Posts

Posted - 12/05/2013 :  09:53:15  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

Tell me whether my following assumptions are correct or not.

1. If there is a non clustered index on a heap table then, to retrieve the records we need to first go through the non clustered index then -> we get a row locator or rowid then -> we need to look into the heap for actual record .

2. If there is a non clustered index on a clustered table(table which has a clustered index) then, to retrieve the records we need to go through the non clustered index then -> we get a clustered index pointer then -> we get the record from the data pages in the leaf level on the clustered index.




That is pretty much correct depending on what you mean by 'need'. In #1, if SQL Server determines this to be efficient, then yes. But SQL Server may opt to do a table scan if that index is not helpful, in which case it would go directly to the heap.

Same for #2. Also if the index is covering SQL Server doesn't need to do the 2nd part. The 2nd part is what you are describing as the bookmark lookup.
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | 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.14 seconds. Powered By: Snitz Forums 2000