| 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 RegardsSrikar 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
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. |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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 indexPraveen D'saMCITP - Database Administrator 2008http://sqlerrors.wordpress.com |
|
|
|
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 indexPraveen D'saMCITP - Database Administrator 2008http://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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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 RegardsSrikar 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 |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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. |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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. |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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. |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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), thenthe newly entered row should anyways be placed in the new page right?Why there will not be any page splits?-- Thanks and RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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. |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
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" |
|
|
|
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. |
|
|
|
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), thenthe 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] |
|
|
|
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 RegardsSrikar Reddy Gondesi,BTECH-IT 2013 Passed Out,Trainee for SQL Server Administration,Miracle Software systems, Inc. |
|
|
|
Next Page
|
Table Organization