Add columns to an existing table

Question:
If I take an existing production table that already has data in it, and add
some columns to it. Does that fragment the table?
What I'm really getting at is: if I alter a table, will it perform
differently than it would have if I had dropped the table, rebuilt it with
the new columns, and then re-inserted the data?Yes, it's different. As far as I understand, when you add
new column (especially the fixed length columns) on
existing table, it will normally have page split if the
page is already very full, and thus gets fragmented. You
can rebuild the indexes after you added the column.
>--Original Message--
>Question:
>If I take an existing production table that already has
data in it, and add
>some columns to it. Does that fragment the table?
>What I'm really getting at is: if I alter a table, will
it perform
>differently than it would have if I had dropped the
table, rebuilt it with
>the new columns, and then re-inserted the data?
>
>.
>|||Hi Jeremy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with your issue.
From your description, I understand that you would like to know if there are difference
behaviors (especially on fragments) between altering a table and creating a new table. Have I
fully understood you? If there is anything I misunderstood, please feel free to let me know.
If you alter the table by means of scripting the code that EM uses you will see that it actually
creates a new table from scratch and then populates it with the old data. On the other hand, if
you create a new table, when inserting a new row into a table, SQL Server must determine
where to put it.
When a table has no clustered index, that is, when the table is a heap-a new row is always
inserted wherever room is available in the table. Even without a clustered index, space
management is quite efficient. If no pages with space are available, SQL Server must allocate
a whole new extent to the table.
A clustered index directs an insert to a specific page based on the value the new row has for
the clustered index key columns. The insert occurs when the new row is the direct result of an
INSERT statement or when it's the result of an UPDATE statement executed via a delete-
followed-by-insert (delete/insert) strategy. New rows are inserted into their clustered position,
splicing in a page via a page split if the current page has no room.
When the index is first created, an explicit FILLFACTOR setting can be applied. SQL Server
does not dynamically keep the specified percentage of empty space in the pages. If
FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent full. An INSERT
or UPDATE made after the creation of an index with a 100 percent FILLFACTOR causes page
splits for each INSERT and possibly each UPDATE.
For more detailed information on the page splitting, I'd recommend you read the book "Inside
SQL Server 2000" written by "Kalen Delaney" on the topic "Creating Tables" and "Data
Modification Internals".
Jeremy, does this answer your question? Please feel free to let me know if this answers your
question. If there is anything more I can still assist you with, please feel free to post it in the
group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you, I believe that you have answered my question completely.
Regards
Jeremy
""Billy Yao [MSFT]"" <v-binyao@.online.microsoft.com> wrote in message
news:cXkVlEZqDHA.1804@.cpmsftngxa06.phx.gbl...
> Hi Jeremy,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
> From your description, I understand that you would like to know if there
are difference
> behaviors (especially on fragments) between altering a table and creating
a new table. Have I
> fully understood you? If there is anything I misunderstood, please feel
free to let me know.
>
> If you alter the table by means of scripting the code that EM uses you
will see that it actually
> creates a new table from scratch and then populates it with the old data.
On the other hand, if
> you create a new table, when inserting a new row into a table, SQL Server
must determine
> where to put it.
> When a table has no clustered index, that is, when the table is a heap-a
new row is always
> inserted wherever room is available in the table. Even without a clustered
index, space
> management is quite efficient. If no pages with space are available, SQL
Server must allocate
> a whole new extent to the table.
> A clustered index directs an insert to a specific page based on the value
the new row has for
> the clustered index key columns. The insert occurs when the new row is the
direct result of an
> INSERT statement or when it's the result of an UPDATE statement executed
via a delete-
> followed-by-insert (delete/insert) strategy. New rows are inserted into
their clustered position,
> splicing in a page via a page split if the current page has no room.
> When the index is first created, an explicit FILLFACTOR setting can be
applied. SQL Server
> does not dynamically keep the specified percentage of empty space in the
pages. If
> FILLFACTOR is 100, SQL Server creates indexes with leaf pages 100 percent
full. An INSERT
> or UPDATE made after the creation of an index with a 100 percent
FILLFACTOR causes page
> splits for each INSERT and possibly each UPDATE.
>
> For more detailed information on the page splitting, I'd recommend you
read the book "Inside
> SQL Server 2000" written by "Kalen Delaney" on the topic "Creating Tables"
and "Data
> Modification Internals".
>
> Jeremy, does this answer your question? Please feel free to let me know if
this answers your
> question. If there is anything more I can still assist you with, please
feel free to post it in the
> group.
>
> Best regards,
>
> Billy Yao
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>
>