insert a un-ordered data to a clustered indexed column, what would happen?

31 Dec 2005 6:13 AM

Frank Lee

Q1, If a column is clustered indexed and there are more than 500000000
records in the table. Now, if I insert a data which should be inserted to
about the 10th location according to the clustered index, what would happen?
Will system stupidly move all other data after the 10th location to next
location to get a space for inserting the new data and of course make it
takes a long time to insert the new data?

Q2, if I modify a data in the clustered indexed column, make the modified
data unordered, what would happen? Stupidly move data?

31 Dec 2005 7:09 AM

Tibor Karaszi

You get a "page split". If the page where the row should go doesn't have space to accommodate the
new row, the page is split and a new page (physically somewhere else) is added to the linked list of
pages. Half the rows are moved to that new page and the new row is inserted where it belong. You now
gave what we call "external fragmentation". This is easier to draw. Read the topics about indexes,
pages and extents in Books Online. It has a good explanation about "physical database architecture".

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Frank Lee" <Reply@to.newsgroup> wrote in message news:Otx8LFdDGHA.412@TK2MSFTNGP15.phx.gbl...
> Q1, If a column is clustered indexed and there are more than 500000000 records in the table. Now,
> if I insert a data which should be inserted to about the 10th location according to the clustered
> index, what would happen? Will system stupidly move all other data after the 10th location to next
> location to get a space for inserting the new data and of course make it takes a long time to
> insert the new data?
>
> Q2, if I modify a data in the clustered indexed column, make the modified data unordered, what
> would happen? Stupidly move data?
>

4 Jan 2006 2:24 PM

Robert Klemme

Frank Lee wrote:

> Q1, If a column is clustered indexed and there are more than 500000000
> records in the table. Now, if I insert a data which should be
> inserted to about the 10th location according to the clustered index,
> what would happen? Will system stupidly move all other data after the
> 10th location to next location to get a space for inserting the new
> data and of course make it takes a long time to insert the new data?
>
> Q2, if I modify a data in the clustered indexed column, make the
> modified data unordered, what would happen? Stupidly move data?

Sounds like a homework assignment to me...

robert