Adding Column to a SQL Server Table

DBCC Page

The next thing you could do is, examine the data pages. For this as in the previous case, the  DBCC PAGE is an undocumented DBCC command.

DBCC TRACEON(3604)
DBCC PAGE (AddColumn, 1, 154, 3);

You need to switch on trace switch 3604 for DBCC Page command.

If you analyze page number 154 as shown in the example above, you will see the newly added column.

What does this mean? Is to say that even a Nullable column will modify the data pages? If that is the case, there shouldn’t be much different from adding a nullable column or adding a column with a default value, which is not what we saw in the first table.

Let us analyse lsn number of the page. Lsn is the Log Sequence Number of the last log record that changed the page.

 

You will see that there is no change to the lsn before and after adding the nullable column while there is a change to the lsn value, if you add a column with a default value.

This tells us two things.

1. This confirms that there are no physical changes to the data pages when you are adding a nullable column.
2. DBCC PAGE is not simply showing data dump of the data page.

Conclusion

When adding a column to a large table, the best practice would be adding a nullable column. However, if you want to update existing rows, consider using the Update statement after adding a nullable column.

Pages: 1 2




Related Articles :

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |