March 8, 2010 at 1:05 am
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
March 8, 2010 at 3:02 am
as per me it will not cause clustered index to rebuild.
Regards,
[font="Verdana"]Sqlfrenzy[/font]
March 8, 2010 at 3:55 am
thanks SQLFrenzy
Do u have any idea what happens in that case and how will clustered index reference data in this new field
March 8, 2010 at 4:08 am
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]
March 8, 2010 at 4:10 am
Thanks SQLFrenzy
Best Regards
Nader
March 8, 2010 at 7:16 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 7:22 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 7:23 am
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
March 8, 2010 at 7:35 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 7:44 am
Thanks for ur example
it made it very clear
March 8, 2010 at 7:51 am
nadersam (3/8/2010)
Thanks for your example. It made it very clear.
You are welcome.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 9:41 pm
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]
March 8, 2010 at 9:52 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 8, 2010 at 9:55 pm
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]
March 8, 2010 at 9:58 pm
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply