April 4, 2008 at 9:07 am
We have a database which has a big table. Someone used Management Studio (right click on Design) to add a new column (the last column) to the table and to change another column from "not null" to allow "null". Then the log file and data file of the database both grew tremendously. We found that this is due to the creation of a clustered index, but nobody said s/he was creating clustered index. The table already has one clustered index which is the primary key. Is it possible that the adding of a new column to a large table and changing from not null to null to another column cause the dropping of the existing clusterd index and recreation of the clustered index? I doubt it. Or maybe someone is lying, meaning there is someone who was really creating the clustered index? If so how can it be found out?
This is a puzzle for us and we need to find out. Thanks for the help in advance!!
April 4, 2008 at 9:32 am
Well - just ran a little test to confirm, but it seems to me that most DDL changes (certainly adding or removing columns, or modifying from null to not null) definitely affect the Clustered index big time. It seems to leave the UCI in such a state that it SHOULD be rebuilt (e.g. heavily fragmented).
If your table grew substantially (like - doubled in size) - then perhaps run a rebuild of the UCI, and you should see that your fragmentation will drop like a rock, as will the space used by that table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 4, 2008 at 9:38 am
Thanks for the answer! I thought adding rows would cause fragmentation and then possibly rebuilding of index but not adding columns.
April 4, 2008 at 9:38 am
In many cases, when you make a change to a table through Management Studio, the change is scripted as the creation of an entirely new table structure and then the table data is copied from the old table into the new one, the old table is dropped and the new table is renamed to the old name.
This is done for various reasons (one is the lack of ability to specify an ordinal position on a new column).
Here is a way to see if this is what happened to you. Go to your test server, design the table, make the changes your developer made, and then rather than saving the changes, click the "Generate script" button. This is what Management Studio is going to run.
April 4, 2008 at 9:47 am
I would wonder if it was adding a column or changing the Not Null to allow nulls that caused the issue?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2008 at 9:53 am
Thanks for all your answers! But just adding a column and changing from not null to null would cause both data and log file to grow?
April 4, 2008 at 9:55 am
Michael Earl (4/4/2008)
In many cases, when you make a change to a table through Management Studio, the change is scripted as the creation of an entirely new table structure and then the table data is copied from the old table into the new one, the old table is dropped and the new table is renamed to the old name.This is done for various reasons (one is the lack of ability to specify an ordinal position on a new column).
Here is a way to see if this is what happened to you. Go to your test server, design the table, make the changes your developer made, and then rather than saving the changes, click the "Generate script" button. This is what Management Studio is going to run.
You're right - I forgot that using the GUI tends to results in drop/create instead of ALTER scripts. THAT would cause an actual build of the UCI, and not just page splits.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 4, 2008 at 10:04 am
Unless you add the column as the last column in the table, the script MUST create a new table and copy the data (causing a new set of indexes to be generated).
In the case of modifying existing fields or adding the field to the end of the table, I think in some cases you may get an ALTER script from Management studio, but you often get a create / copy script.
You will usually get warned by Management studio if you do something that will cause this type of action on a table with a lot of records.
April 4, 2008 at 12:02 pm
Got it, thanks all for replying!!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply