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
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/21/2013 :  03:17:33  Show Profile  Reply with Quote
quote:
Originally posted by James K

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



I got your point.
Thank a lot for the 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

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/21/2013 :  03:55:15  Show Profile  Reply with Quote
I am sending this message after seeing this site.
http://careerride.com/SQL-Server-what-is-page-splits.aspx

so when there is no sufficient space for the new row in a page, then without moving some rows from this page to another page, the new row will be stored in the space registered for fill factor right? (If the fill factor option is set to reserve free space on each page)

--
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/21/2013 :  08:57:30  Show Profile  Reply with Quote
quote:
Originally posted by sgondesi

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.




If you insert a row in the middle of a table and run out of room, this is a page split.

If you insert a row at the end of a table (i.e. Clustered Identity Column/ ever-increasing Clustered Index) and run out of room, this is NOT a page split.
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/21/2013 :  11:43:02  Show Profile  Reply with Quote
Thank you, thanks a lot.

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

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/25/2013 :  18:22:47  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


I'm sure this is six-of-one and half-a-dozen-of-the-other, and it depends on the circumstances for each table, but:

Clustered index should be based on keys that are actually used for reference, in particular the most common means of single record retrieval, and range retrieval, will be most efficient using the clustered index. If the record is never/rarely accessed by the Identity ID then having that as the Clustered Index is unlikely to be the best choice.

In many/most databases the occurrence of Reads is far more common than Writes, so personally I don't get too hung up about clustered index page splits. If I will read a record 10 times, or maybe 100x ... 1,000x even?? for every new record inserted then I would prefer to optimise the SELECT rather than the INSERT.

My only other main consideration is that I would prefer to choose key fields for clustered index where the data will never/very rarely change.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3573 Posts

Posted - 11/26/2013 :  08:39:57  Show Profile  Reply with Quote
quote:
I'm sure this is six-of-one and half-a-dozen-of-the-other, and it depends on the circumstances for each table, but:


I disagree. It is half-a-dozen of one, and six of the other.

Kimberly Tripp's take on the clustered index debate: http://technet.microsoft.com/en-us/sqlserver/gg508879.aspx
Go to Top of Page

sgondesi
Posting Yak Master

India
194 Posts

Posted - 11/27/2013 :  08:39:53  Show Profile  Reply with Quote
Thanks for your valuable response sir.

--
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/27/2013 :  10:31:48  Show Profile  Reply with Quote
Answer for the question "How identity column reduces the page splits?"

In addition to narrow, unique, and static, an integer identity column is an excellent example of an ever-increasing column. The identity property continuously increments by the value defined at creation, which is typically one. This allows SQL Server, as new rows are inserted, to keep writing to the same page until the page is full, then repeating with a newly allocated page.

There are two primary benefits to an ever-increasing column:

Speed of insert – SQL Server can much more efficiently write data if it knows the row will always be added to the most recently allocated, or last, page
Reduction in clustered index fragmentation – this fragmentation results from data modifications and can take the form of gaps in data pages, so wasting space, and a logical ordering of the data that no longer matches the physical ordering.

I am posting this information from the below mentioned link
https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/

--
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
Page: of 2 Previous Topic Topic Next Topic  
Previous 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.08 seconds. Powered By: Snitz Forums 2000