Posted by Emin on 01/03/07 18:42

Dear Roy, Erland, and Hugo,

Thank you very much for your excellent responses. It sounds like a
single logical table is the way to go since the frequency of inserts is
not very high. If necessary, where would I get more information about
low level details like page splits (e.g., how to control the page size,
how to determine when and how many page splits are occuring, etc.)?

Thanks again,
-Emin

On Jan 2, 5:53 pm, Hugo Kornelis <h...@perFact.REMOVETHIS.info.INVALID>
wrote:
> On 2 Jan 2007 08:57:21 -0800, Emin wrote:
>
> (snip)
>
> >if I insert a record for a given stock does the database have to
> >physicall "move down" all the records that are below that or does it do
> >something smarter?Hi Emin,
>
> Roy answered most of your questions, but was friendly enough to leave
> this bit for me to comment on.
>
> Yes, it does something smarter. Rows are *NOT* physically stored in the
> order imposed by the clustered index. The clustered index imposes a
> *logical* ordering on the data, which is implemented by pointers that
> form a logical chain of database pages.
>
> If a row in inserted, SQL Server first navigates the clustered index to
> find the correct location (database page). Then, there are two
> possibilities:
>
> 1. There's still space left on the page. The row is added and the insert
> is finished.
>
> 2. There's not enough free space on the page. A page split occurs: half
> of the data on the page is moved to a newly allocated page and pointers
> are updated to reflect the location of the new page in the chain. After
> that, the new row is inserted.
>
> If your database has to process *LOTS* of inserts (in the order of
> thousands per second or more), it makes sense to define your clustered
> index such that new rows are always inserted at the logical end of the
> pointer chain. In that case, page splits will never happen.
> For lower amounts of inserts, the overhead of a page split is
> insignificant.
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]