Adding new field

Question

Post reply

Adding new field

nadersam

SSCertifiable

Points: 6542
More actions
March 8, 2010 at 1:05 am

#230594

Hi All,
I have a table containing a clustered index , as i know and correct me pls if i am wrong, the clustered index contains all fields in table in its leaf.
My question is if i add a new field to this table , will the clustered index be rebuild to add this new field to its structure.
I am asking this becuase i need to add some new fields to a big table, i am afraid if i do that sql server will rebuild the clustered index which will slow donw performance signifcantly during that operation.
waiting for ur replies
Thanks in advance
Nader

Viewing 15 posts - 1 through 15 (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

as per me it will not cause clustered index to rebuild.

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

nadersam SSCertifiable Points: 6542 More actions · Answer 2

thanks SQLFrenzy

Do u have any idea what happens in that case and how will clustered index reference data in this new field

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 3

rebuild index drops and then creates the index...this process changes the pageids being used by the index...

to check this use the dbcc page command..u can use the below given link for dbcc page

http://www.mssqltips.com/tip.asp?tip=1578

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

nadersam SSCertifiable Points: 6542 More actions · Answer 4

nadersam

SSCertifiable

Points: 6542

March 8, 2010 at 4:10 am

#1129913

Thanks SQLFrenzy

Best Regards

Nader

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

This is quite a complex topic, but I will try to be brief.

When you add a column, what SQL Server has to do, depends on many factors. Essentially, though, there are only two possible outcomes:

1. Only meta-data needs to change

2. Every row might have to physically change

Meta-data is the information about the table structure. Changing it is typically nearly instantaneous (assuming the required lock can be obtained). Rewriting every row is clearly a very resource-intensive process, especially for large tables.

If conditions are met such that SQL Server ends up filling the new column with NULLs, only a meta-data change is required. A simple example is adding a NULLable column that does not specify a DEFAULT constraint (and isn't one of the special types that automatically generates a value, like IDENTITY or ROWVERSION).

If the definition of the new column requires SQL Server to write a non-NULL value in the column, physical changes will be required.

An important point to appreciate, however, is that even a meta-data-only change is potentially not cost-free. If you ever update the value in the new column from NULL to some non-NULL value, in any rows that pre-date the addition of the extra column, SQL Server might find that the extra data does not fit on the page. This will result in the page being re-organized, and possibly split into two, to make room.

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 6

Sqlfrenzy (3/8/2010)
rebuild index drops and then creates the index...this process changes the pageids being used by the index...http://www.mssqltips.com/tip.asp?tip=1578

They might change. Optimizations within the engine exist to avoid allocating new pages where this can be avoided.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

nadersam SSCertifiable Points: 6542 More actions · Answer 7

Thanks Paul for ur reply, its very informative.

To make sure i understand u correctly, for the index if added field is a null value, just its metadata will be updated but if not null then page structure will be updated

Paul White SSC Guru Points: 150468 More actions · Answer 8

nadersam (3/8/2010)
Thanks Paul for your reply, I found it very informative.
To make sure I understand you correctly: For the index, if the added field is a NULL value, just its metadata will be updated but if not null then page structure will be updated

Basically, yes. If the column ends up being added with NULL in every row, it will be a meta-data change.

Maybe an example will make it clearer:

-- Test table

CREATE TABLE #test

(

column1 INTEGER NOT NULL,

padding CHAR(500) NOT NULL DEFAULT ('')

);

GO

-- ==========

-- Test setup

-- ==========

--

-- Add 250,000 wide rows

-- (takes about 10 seconds)

INSERT #test (column1)

SELECT TOP (250000)

ROW_NUMBER() OVER (ORDER BY (SELECT 0))

FROM master.sys.columns C1,

master.sys.columns C2,

master.sys.columns C3;

GO

-- ==========================================

-- Run the following statements ONE AT A TIME

-- ==========================================

GO

-- Meta-data change only (instant)

ALTER TABLE #test ADD column2 INT NULL;

GO

-- Physical changes required (default specified)

-- Takes about 2 seconds

ALTER TABLE #test ADD column3 INT NOT NULL DEFAULT (0);

GO

-- Physical changes required (rowversion)

-- Takes about 2 seconds

ALTER TABLE #test ADD column4 ROWVERSION;

GO

-- Meta-data change only (instant)

ALTER TABLE #test ADD column5 VARCHAR(1000) NULL;

GO

DROP TABLE #test;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

nadersam SSCertifiable Points: 6542 More actions · Answer 9

nadersam

SSCertifiable

Points: 6542

March 8, 2010 at 7:44 am

#1130015

Thanks for ur example

it made it very clear

Paul White SSC Guru Points: 150468 More actions · Answer 10

nadersam (3/8/2010)
Thanks for your example. It made it very clear.

You are welcome.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 11

Paul White (3/8/2010)
This is quite a complex topic, but I will try to be brief.
When you add a column, what SQL Server has to do, depends on many factors. Essentially, though, there are only two possible outcomes:
1. Only meta-data needs to change
2. Every row might have to physically change
Paul

thanks for bringing up this case; however as per my understanding, adding a not null may cause physical change but that will be addition of data pages required to accomodate the data being inserted. It should not cause the entire page structure to change.

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

Paul White SSC Guru Points: 150468 More actions · Answer 12

Sqlfrenzy (3/8/2010)
Paul White (3/8/2010)
This is quite a complex topic, but I will try to be brief.
When you add a column, what SQL Server has to do, depends on many factors. Essentially, though, there are only two possible outcomes:
1. Only meta-data needs to change
2. Every row might have to physically change
Paul
thanks for bringing up this case; however as per my understanding, adding a not null may cause physical change but that will be addition of data pages required to accommodate the data being inserted. It should not cause the entire page structure to change.

When non-NULL data is added to the new column, SQL Server might be able to accommodate it without allocating a new page. The engine tries to avoid allocating new pages if it can be sensibly avoided.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Ahmad Osama SSCertifiable Points: 7676 More actions · Answer 13

Paul White (3/8/2010)
Sqlfrenzy (3/8/2010)
Paul White (3/8/2010)
This is quite a complex topic, but I will try to be brief.
When you add a column, what SQL Server has to do, depends on many factors. Essentially, though, there are only two possible outcomes:
1. Only meta-data needs to change
2. Every row might have to physically change
Paul
thanks for bringing up this case; however as per my understanding, adding a not null may cause physical change but that will be addition of data pages required to accommodate the data being inserted. It should not cause the entire page structure to change.
When non-NULL data is added to the new column, SQL Server might be able to accommodate it without allocating a new page. The engine tries to avoid allocating new pages if it can be sensibly avoided.

hmm...but the index is not rebuild...

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

Paul White SSC Guru Points: 150468 More actions · Answer 14

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi