Another change that requires SQL Server to examine every row is adding a new constraint. For unique and primary key constraints, SQL Server checks for existing duplicates and, if it finds any, rejects the ALTER. SQL Server must ensure, for check and foreign key constraints, that no existing data violates the constraint. For check and foreign key constraints, you can alternatively include the WITH NOCHECK option, which tells SQL Server not to verify existing data.
The next test adds in NewOrders a new column that has a default value:
ALTER TABLE NewOrders
ADD big_column CHAR(200) NOT NULL
DEFAULT 'big_column'
GO
And the following test changes a data type in NewOrders from int to bigint:
ALTER TABLE NewOrders
ALTER COLUMN ShipVia bigint
GO
The last test changes an int data type to char(10):
ALTER TABLE NewOrders
ALTER COLUMN EmployeeID char(10)
GO
For these three changes, SQL Server must change every row because the row will increase in size. As the altered columns grow, the new row might not fit on the original page, and the page will need to split. You'll notice a lot of page accesses in these cases, perhaps several hundred for the 21-page NewOrders table.
When I ran these tests, I noticed another interesting fact about the rows moving as pages need to split. If in a previous test I'd added a fixed-length column that allowed NULLs, SQL Server didn't change the rows at that time to include the space for the new column. But when a later ALTER TABLE command resulted in page splits and row movement, SQL Server adjusted the rows that were moving to new pages, making space for the previously added column. Rows that didn't move didn't reflect the space for the new column until SQL Server received an UPDATE for the row.
Keep in mind that an int column takes only 4 bytes of disk space even though it can store a 10-digit decimal number. So if you change an int column to char(10), the column must grow. However, if you change an int column to char(4) (assuming all existing data values are less than 10,000), the column doesn't need to grow. SQL Server will modify the rows, as you can verify by using DBCC PAGE, but the rows don't need any new space.
I also tested changing a char column to varchar and vice versa. For these tests, the number of pages STATISTICS IO returned was much higher than the number of pages in the table, so I knew that SQL Server was modifying the pages. I also used the syscolumns table to examine what kinds of changes SQL Server made. SQL Server stores fixed-length and variable-length columns in different ways within the row. All fixed-length columns are in the first part of the row, at a known byte offset within the row. You can find this offset in the syscolumns table's xoffset column. For example, you'd use this query to find an offset for the NewOrders table:
SELECT * FROM syscolumns
WHERE id = object_id('NewOrders')
SQL Server stores variable-length columns at the end of the physical row; they don't have the same byte offset in every row in the table. The syscolumns table shows their offset as a negative number, indicating a position counting from the end of the physical row in which SQL Server stores the column. For example, a column that has an offset value of -1 in syscolumns.xoffset is the last column; the column with an offset value of -2 is the next to last, and so on. (Additional metadata in the row reports exactly where each variable-length column starts, but I won't go into that level of detail here.) When changing a char column to varchar or vice versa, I noticed in the syscolumns table that the xoffset value changed from a fixed number to a negative offset position or from a negative offset position to a fixed number.
This list of tests isn't complete, but it's a good starting point for you to create tests of your own. For an exhaustive list of how every ALTER TABLE variation behaves internally, you'd need to run tests both on tables that have clustered indexes and on heaps—particularly those cases where making the requested change to the table requires moving rows. For a table that has a clustered index, SQL Server splits a page if a new row doesn't fit on the original page. But in a heap, SQL Server moves the updated, enlarged row to a new location and leaves a forwarding pointer in the original location.
Other Changes
SQL Server 2000 and 7.0 also provide another command that you can use to make metadata changes to tables: the sp_tableoption stored procedure. I discussed the text in row option to this procedure in my March 2003 column, "Text in Row Internals," InstantDoc ID 37635. I showed that when you enable this option, the changes to the table are just metadata changes, and when you disable the option, SQL Server physically updates the pages. The other options to sp_tableoption (pintable and table lock on bulk load) change only the table's metadata when the sp_tableoption procedure is executed.
Be aware that if you use Enterprise Manager to change tables, SQL Server might end up working much harder than if you had used Query Analyzer to run the T-SQL statement. Sometimes, particularly in SQL Server 7.0, SQL Server changes an existing table by completely rebuilding the table, moving all the data, dropping the original table, and renaming the new one. Although this process produces the same result as if you merely altered the table, SQL Server does a great deal of extra work, especially if your table contains a lot of data.
End of Article
Prev. page
1
2
[3]
next page -->
