Adding new field

Question

Adding new field

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 1

Paul White (3/8/2010)
Sqlfrenzy (3/8/2010)
hmm...but the index is not rebuild...
Which index are you talking about? The clustered index? I don't recall saying anything about indexes - can you clarify your point please? I'm afraid you have lost me slightly.

I was lost....:hehe:

Regards,
[font="Verdana"]Sqlfrenzy[/font]

Paul White SSC Guru Points: 150467 More actions · Answer 2

Ok.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

john.campbell-1020429 Ten Centuries Points: 1194 More actions · Answer 3

Nader,

I would suggest that you research a clustered index a little more. Your are in the circle but not dead on in the middle of the target. A table can have a clustered index or not. If the table has a clustered index then the natural order of the table itself will be based on the clustered index. Dbase called this the "sort order" or the table. If you add columns to change the clustered key, then the clustered index will be rebuilt. If you only add "blank" columns to the table, this will normally be a non intensive operation. If you use defaults for these new columns and they do not change the clustered index order then you will only be doing inserts for the column value for the table, again a usually non intensive process.

John.

nadersam SSCertifiable Points: 6542 More actions · Answer 4

nadersam

SSCertifiable

Points: 6542

March 9, 2010 at 7:11 am

#1130635

Thanks John

Paul White SSC Guru Points: 150467 More actions · Answer 5

john.campbell-1020429 (3/9/2010)
If you add columns to change the clustered key, then the clustered index will be rebuilt.

john.campbell-1020429 (3/9/2010)
If you use defaults for these new columns and they do not change the clustered index order...

How would adding a column change the key columns of the clustered index?

How would adding a default change the clustered index order?

john.campbell-1020429 (3/9/2010)
...then you will only be doing inserts for the column value for the table, again a usually non intensive process.
John.

No, it is always an intensive process. Minimally, the changes must be logged for redo and undo, and the affected data pages must be rewritten. The operation is also quite likely to cause at least one page split.

Remember that a meta-data-only change, such a adding a NULLable column with all NULL values, makes no change to the physical data row storage structures - those costs will be borne by any process that subsequently attempts to place real data in the new column.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi