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

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/20/2013 :  06:28:05  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 11/20/2013 :  08:10:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 11/20/2013 :  08:34:49  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/20/2013 :  09:04:48  Show Profile  Reply with Quote
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

India
15 Posts

Posted - 11/20/2013 :  09:08:22  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/20/2013 :  09:17:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 11/20/2013 :  09:17:18  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/20/2013 :  09:20:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 11/20/2013 :  09:22:01  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/20/2013 :  09:49:26  Show Profile  Reply with Quote
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

Canada
591 Posts

Posted - 11/20/2013 :  11:00:48  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/20/2013 :  11:22:50  Show Profile  Reply with Quote
"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

Canada
591 Posts

Posted - 11/20/2013 :  11:27:26  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/20/2013 :  11:50:57  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/20/2013 :  12:00:29  Show Profile  Reply with Quote
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

Canada
591 Posts

Posted - 11/20/2013 :  12:57:05  Show Profile  Reply with Quote
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

India
194 Posts

Posted - 11/20/2013 :  13:00:13  Show Profile  Reply with Quote
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

Canada
591 Posts

Posted - 11/20/2013 :  13:47:23  Show Profile  Reply with Quote
quote:


Please tell me what do you mean by ever increasing?




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

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

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 11/20/2013 :  14:01:22  Show Profile  Reply with Quote
"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.

Edited by - James K on 11/20/2013 14:27:17
Go to Top of Page

denis_the_thief
Aged Yak Warrior

Canada
591 Posts

Posted - 11/20/2013 :  14:12:16  Show Profile  Reply with Quote
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:
http://careerride.com/SQL-Server-what-is-page-splits.aspx
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/21/2013 :  03:10:14  Show Profile  Reply with Quote
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.

Edited by - sgondesi on 11/21/2013 03:15:08
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.14 seconds. Powered By: Snitz Forums 2000