How do you add a NOT NULL Column to a large table in SQL Server?

by

I ran into this problem for my work also. And my solution is along #2.

Here are my steps (I am using SQL Server 2005):

1) Add the column to the table with a default value:

ALTER TABLE MyTable ADD MyColumn varchar(40) DEFAULT('')

2) Add a NOT NULL constraint with the NOCHECK option. The NOCHECK does not enforce on existing values:

ALTER TABLE MyTable WITH NOCHECK
ADD CONSTRAINT MyColumn_NOTNULL CHECK (MyColumn IS NOT NULL)

3) Update the values incrementally in table:

GO
UPDATE TOP(3000) MyTable SET MyColumn = '' WHERE MyColumn IS NULL
GO 1000

  • The update statement will only update maximum 3000 records. This allow to save a chunk of data at the time. I have to use “MyColumn IS NULL” because my table does not have a sequence primary key.

  • GO 1000 will execute the previous statement 1000 times. This will update 3 million records, if you need more just increase this number. It will continue to execute until SQL Server returns 0 records for the UPDATE statement.

More Related Contents:

  1. How do I use cascade delete with SQL Server?
  2. How can foreign key constraints be temporarily disabled using T-SQL?
  3. An explicit value for the identity column in table can only be specified when a column list is used and IDENTITY_INSERT is ON SQL Server
  4. How to add Active Directory user group as login in SQL Server
  5. Maximum size of a varchar(max) variable
  6. How to update large table with millions of rows in SQL Server?
  7. How to Use UTF-8 Collation in SQL Server database?
  8. How do you specify a different port number in SQL Management Studio?
  9. How Do I Split a Delimited String in SQL Server Without Creating a Function?
  10. What’s the longest possible worldwide phone number I should consider in SQL varchar(length) for phone
  11. Export table to file with column headers (column names) using the bcp utility and SQL Server 2008
  12. Should every User Table have a Clustered Index?
  13. Sequence as default value for a column
  14. Aggregate bitwise-OR in a subquery
  15. How to connect to MS SQL Server using Inno Setup?
  16. VarBinary vs Image SQL Server Data Type to Store Binary Data?
  17. SQL count rows in a table
  18. Convert month name to month number in SQL Server
  19. Decimal values in SQL for dividing results
  20. How do I programmatically set the connection string for Entity-Framework Code-First?
  21. Need help in calculation using two Datasets using Expression SSRS
  22. SQL Server Xml query with multiple namespaces
  23. T-SQL incrementing counter based on change in a column value
  24. How can I fill a column with random numbers in SQL? I get the same value in every row
  25. The request failed or the service did not respond in a timely fashion?
  26. Bulk insert fixed width fields
  27. How do you open an SDF file (SQL Server Compact Edition)? [closed]
  28. What are row, page and table locks? And when they are acquired?
  29. Bootstrapping SQL Express from WiX?
  30. CHECKSUM and CHECKSUM_AGG: What’s the algorithm?

Leave a Comment