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)
 Table Organization

Author  Topic 

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 06:28:05
Hai everybody.

In general, when a table is created without creating any indexes on it, the table will be organized logically as a heap right?

I would like to know Which is the best data structure in which a table can be organized.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-20 : 08:10:36
yep..you're correct. unless you create a clustered index it will be stored as a heap.

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 08:34:49
quote:
Originally posted by sgondesi

Hai everybody.

I would like to know Which is the best data structure in which a table can be organized.


Almost always it is better to have a clustered index. And, if possible a unique clustered index.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 09:04:48
after creation of a clustered index on a table, Will the entire table is organized into a B-tree?

--
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

Prav4u
Starting Member

15 Posts

Posted - 2013-11-20 : 09:08:22
i always keep identity field as my clustered index just to make sure every new record goes at the the end of the page without causing page splits, also it is unique narrow ever increasing few attributes recomanded for clustered index

Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 09:17:07
quote:
Originally posted by Prav4u

i always keep identity field as my clustered index just to make sure every new record goes at the the end of the page without causing page splits, also it is unique narrow ever increasing few attributes recomanded for clustered index

Praveen D'sa
MCITP - Database Administrator 2008
http://sqlerrors.wordpress.com




I have only understood that "you will always create clustered index on an identity column only which may reduce the page splits".

I did not get the rest. Did you post this message for my post?

--
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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 09:17:18
quote:
Originally posted by sgondesi

after creation of a clustered index on a table, Will the entire table is organized into a B-tree?

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

Yes. That is the very definition of a clustered index - i.e., the data is stored in the index. And, it is stored in a B-tree in SQL Server. http://technet.microsoft.com/en-us/library/ms190457.aspx
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 09:20:22
thanks for the reply.
i will go thorough the site and i get back to 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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 09:22:01
An ever-increasing clustering key has the advantage that any new rows that are added will always be added at the "end". That will avoid page splits if you are mostly inserting into the table. Compare that with a clustering key that is a GUID. With a GUID, when you add a new row, SQL Server may be required to insert it into some page where that GUID happens to fall in the ordering scheme. If that page does not have enough space to hold the new data, then a new page will need to be inserted (page split). An ever-increasing key avoids this.

Of course, if you are frequenly deleting from the table as well, that doesn't hold up.

Another thing to consider is that it is advantageous to have a narrow clustering key. Very interesting reading here on the clustering key debate: http://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/

Kimberly Tripp has a video as well on this very topic - couldn't find the link now when I searched for it.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 09:49:26
Please clarify my doubt.
How the page splits can be kept low with 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

596 Posts

Posted - 2013-11-20 : 11:00:48
quote:

How the page splits can be kept low with the clustered index?


It is kept low with a Clustered index based on the identity column.

It's like a phone book, it's ordered by name so if you want to add a new person, you would have to rip your phonebook in 2 and add a page in between. But if a phonebook was ordered by who was added chronologically, you would just need to add a page at the end when you add a person.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 11:22:50
"But if a phonebook was ordered by who was added chronologically, you would just need to add a page at the end when you add a person."

In the above sentence, you meant that chronologically means the order in which they occur. So, the order will be according to the order in which that particular row is added to the table.
Do you think that i got your point?

--
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

596 Posts

Posted - 2013-11-20 : 11:27:26
quote:

Do you think that i got your point?





Chronologically being, the order in which the phone numbers where added to the phone directory. Or in Database sense, yes, the order in which that particular row is added to the table.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 11:50:57
thanks for the reply.

why do call clustered index as ever-increasing?

even a non-clustered index which is created on a table will be modified or increased when a new record is added to that particular table 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

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 12:00:29
Thank you. Thanks a lot.

And, if the page gets filled (when 8060 Bytes of a page is filled), then
the newly entered row should anyways be placed in the new page right?

Why there will not be any page splits?

--
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

596 Posts

Posted - 2013-11-20 : 12:57:05
quote:
Originally posted by sgondesi



why do call clustered index as ever-increasing?





No, the recomendation is for an ever-increasing clustered index. A Clustered Index on an Identity column in this case.

Not all clustered indexes are ever-increasing.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-20 : 13:00:13
Thanks for the reply.

Please tell me what do you mean by ever increasing?

--
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

596 Posts

Posted - 2013-11-20 : 13:47:23
quote:


Please tell me what do you mean by ever increasing?




[url]http://answers.yahoo.com/question/index?qid=20090730175914AAuvpUk[/url]

"Always increasing" not sure why we say "ever"
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-11-20 : 14:01:22
"Ever" as in the opposite of "Never"?

You could also say "monotonically increasing". Which means that a new value that is added is guaranteed to be greater than all the existing values.
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-11-20 : 14:12:16
quote:
Originally posted by sgondesi

Thank you. Thanks a lot.

And, if the page gets filled (when 8060 Bytes of a page is filled), then
the newly entered row should anyways be placed in the new page right?

Why there will not be any page splits?





That is not a page split, that is adding a page. Then the next question would be is why do page splits affect performance?

Basically, when splitting the page, SQL Server will have to move several records (but not all) from the original page to the new page. Perhaps may also lead to more fragmentation.

Here is an article:
[url]http://careerride.com/SQL-Server-what-is-page-splits.aspx[/url]
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-21 : 03:10:14
Thanks for the post.

i got your point what you meant to say in the last post.(understood what you have said and also had knowledge on fragmentation)

My actual doubt is,
if there is no sufficient space in the page to store another row, how the page split can be avoided in this scenario?

All our assumptions hold, like having a clustered index on identity column.

--
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
    Next Page

- Advertisement -