I wish to add an integer column to some table with a lot of rows and several indexes (Its an information warehouse Fact Table).
To help keep the row width as narrow as you possibly can all of the posts within this table are understood to be not null. And So I want the brand new column to become not null having a default of zero.
From experience adding this column will require a while, most probably since the database will have to rewrite all of the rows with a brand new column having a filled value. Which most probably calls for upgrading the clustered index and all sorts of the non-clustered indexes.
So must i drop all of the indexes before adding the column after which recreate all of them. Or perhaps is there an simpler method of doing this?
Also I do not really realise why adding a column that's nullable is really much faster. How come this not involve re-writng the records by having an extra Is Null bit turned for every row.
It may need upgrading the clustered index, yes - this IS the table data, in the end.
However I aren't seeing why the non-clustered indices would need to up-to-date - your brand-new column will not be person in the non-clustered indices.
Also, I do not observe how shedding and re-creating the indices would help you within this scenario. Should you be bulk-loading into the millions existing rows from another table or database - yes, then it may be faster (because of the Card inserts being considerably faster) - but adding a column does not really are afflicted by any indices or constraints being around, I do not think.
SQL Server is really a row oriented database. This really is as opposed to a column oriented database. Which means that in SQL Server, all the data for any given row is saved together around the disk. Let us come with an example:
If you have a person table with 3 posts, FirstName, MiddleInitial, and LastName. Then, if you have 3 records within this table for Jabba T. Hutt, Dennis T. Menace, and George W. Rose bush.
Consecutively oriented database (like SQL Server), the records is going to be saved on disk as a result:
Jabba, T, Hutt Dennis, T, Menace George, W, Rose bush
In comparison, a column oriented database would keep records on disk such as this:
Jabba, Dennis, George T, T, W Hutt Menace, Rose bush
Where posts are arranged together rather than rows.
Now, when you attend give a column to some table consecutively oriented database (SQL Server, for instance), the brand new data for every column needs to be placed alongside the present rows, shifting the rows needing lots of read/write procedures. So, should you place a brand new column for that customer prefix that defaults to 'Mr', this is exactly what you'd get:
Mr, Jabba, T, Hutt Mr, Dennis, T, Menace Mr, George, W, Rose bush
As you can tell, all the original data continues to be moved right. However, whenever you place a brand new column that defaults to NULL, no new data needs to be placed in to the existing rows. Thus, there's less shifting, needing less disk read/write procedures.
Obviously, this an oversimplification of what is really happening on disk. You will find other activities to consider when confronted with indexes, pages, etc. But, it will help give you the picture.
For clarification I am not whatsoever recommending you progress to some column oriented database, I simply put that info in exist for explain what Row oriented meant.
"Also I do not really realise why adding a column that's nullable is really much faster. How come this not involve re-writng the records by having an extra Is Null bit turned for every row."
Adding a nullable column basically changes the phrase the table. The person records aren't affected.