score:4
You can alter a field and make it not null without it checking the fields. If you are really concerned about not doing it off hours you can add a constraint to the field which checks to make sure it isn't null instead. This will allow you to use the with no check option, and not have it check each of the 4 million rows to see if it updates.
CREATE TABLE Test
(
T0 INT Not NULL,
T1 INT NUll
)
INSERT INTO Test VALUES(1, NULL) -- Works!
ALTER TABLE Test
WITH NOCHECK
ADD CONSTRAINT N_null_test CHECK (T1 IS NOT NULL)
ALTER COLUMN T1 int NOT NULL
INSERT INTO Test VALUES(1, NULL) -- Doesn't work now!
Really you have two options (added a third one see edit):
- Use the constraint which will prevent any new rows from being updated and leave the original ones unaltered.
- Update the rows which are null to something else and then apply the not null alter option. This really should be run in off hours, unless you don't mind processes being locked out of the table.
Depending on your specific scenario, either option might be better for you. I wouldn't pick the option because you have to run it in off hours though. In the long run, the time you spend updating in the middle of the night will be well spent compared the headaches you'll possibly face by taking a short cut to save a couple of hours.
This all being said, if you are going to go with option two you can minimize the amount of work you do in off hours. Since you have to make sure you update the rows to not null before altering the column, you can write a cursor to slowly (relative to doing it all at once)
- Go through each row
- Check to see if it is null
- Update it appropriately. This will take a good while, but it won't lock the whole table block other programs from accessing it. (Don't forget the with(rowlock) table hint!)
EDIT: I just thought of a third option: You can create a new table with the appropriate columns, and then export the data from the original table to the new one. When this is done, you can then drop the original table and change the name of the new one to be the old one. To do this you'll have to disable the dependencies on the original and set them back up on the new one when you are done, but this process will greatly reduce the amount of work you have to do in the off hours. This is the same approach that sql server uses when you make column ordering changes to tables through the management studio. For this approach, I would do the insert in chunks to make sure that you don't cause undo stress on the system and stop others from accessing it. Then on the off hours, you can drop the original, rename the second, and apply dependencies etc. You'll still have some off hours work, but it will be minuscule compared to the other approach.
Link to using sp_rename.
Read More
- SQL Server ALTER field NOT NULL takes forever
- SQL Server 2012 takes long time on simple alter to add NULL columns
- NOT Like or <> in an NVARCHAR field is also filtering out Null - SQL Server 2008
- Alter NVARCHAR column from NULL to NOT NULL in sql server
- why can't I convert a field in sql server from not null to null
- How to remove not null constraint in sql server using query
score:2
Sorry for the discouragement, but:
- Any ways to speed it up: No, not if you want to change the table structure itself
- or am I stuck just doing it overnight during off-hours? Yes, and that's probably for the best, as @HLGEM pointed out
- Also could this cause a table lock? Yes
Not directly relevant to you (because it's about going from NOT NULL to NULL), but interesting read on this topic: http://beyondrelational.com/blogs/sankarreddy/archive/2011/04/05/is-alter-table-alter-column-not-null-to-null-always-expensive.aspx
And finally some ancient history - on an equivalent question in a forum in 2005, the same suggestion was made as @Kevin offered above - using a constraint insteadof making the column itself non-nullable: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=50671
score:4
The only way to do this "quickly" (*) that I know of is by
- creating a 'shadow' table which has the required layout
- adding a trigger to the source-table so any insert/update/delete operations are copied to the shadow-table (mind to catch any NULL's that might popup!)
- copy all the data from the source to the shadow-table, potentially in smallish chunks (make sure you can handle the already copied data by the trigger(s), make sure the data will fit in the new structure (ISNULL(?) !)
- script out all dependencies from / to other tables
- when all is done, do the following inside an explicit transaction :
- get an exclusive table lock on the source-table and one on the shadowtable
- run the scripts to drop dependencies to the source-table
- rename the source-table to something else (eg suffix _old)
- rename the shadow table to the source-table's original name
- run the scripts to create all the dependencies again
You might want to do the last step outside of the transaction as it might take quite a bit of time depending on the amount and size of tables referencing this table, the first steps won't take much time at all
As always, it's probably best to do a test run on a test-server first =)
PS: please do not be tempted to recreate the FK's with NOCHECK, it renders them futile as the optimizer will not trust them nor consider them when building a query plan.
(*: where quickly comes down to : with the least possible downtime)
More questions
- Checking an input param if not Null and using it in where in SQL Server
- WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value
- Marking persisted computed columns NOT NULL in SQL Server Management Studio
- Oracle considers empty strings to be NULL while SQL Server does not - how is this best handled?
- SQL Server return Rows that are not equal <> to a value and NULL
- How to determine the field value which can not convert to (decimal, float,int) in SQL Server
- SQL Server view takes a long time to alter but query itself finishes quickly?
- SQL Server is not numeric and not null
- ALTER TABLE my_table ADD COLUMN column_name VARCHAR(50) AFTER col_name not supported in SQL Server 2000
- How to insert NULL into SQL Server DATE field *from XML*
- Why does SQL Server force me to drop the table to be able to alter a field from DateTime to DateTime2(3)?
- NOT NULL clause in a SQL SERVER check constraint
- Adding "null" or NOT NULL column to a huge SQL Server table
- Update script for updating a table field in SQL Server from NULL to non-NULL
- Sql Server ODBC Date Field - Optional feature not implemented
- SQL Server custom not null data type with default value is returning null
- SQL Server concatenation separated by comma only when column is not null
- SQL Server : how to force NOT NULL in a column only if another column in the same table is a specific value?
- SQL Server view with a 'select where x is not null' takes ages to complete
- Empty Textbox still saves data in SQL Server even columns in table are not allowed null
- SQL Server CASE inside where clause not working for NULL value
- SQL Server: How to check if the value of returned field is not null
- How Can I ALTER COLUMN to NOT NULL and ADD PRIMARY KEY in the same SQL
- Alter Table 'DEFAULT' not setting default values - SQL Server
More questions with similar tag
- SQL Server does not recognize table even after being refreshed
- Grouped string aggregation / LISTAGG for SQL Server
- How can I create an auto incrementing Id in a temp table?
- SSDT2012 version SSIS package can be execute on SQL Server 2012?
- How to query Alert System Enable mail profile properties
- Transfer stored procedures
- How to solve the Error Sql Server Management Studio when Create DB Diagrams(Error in spanish && and error number unknown)?
- What tool generates diagrams from SQL Server hierarchical data?
- Can these two queries be considered same or not?
- How to increase the server timeout in asp.net?
- Execute multiple statements inside Stored Procedure
- What is the Postgres MD5() equivalent in SQL Servrer?
- Real life example, when to use OUTER / CROSS APPLY in SQL
- SSAS MDX WHERE clause syntax - filtering multiple values from the same hierarchy
- Can I use the SqlServer NTEXT data type in LightSwitch?
- Azure API App vs Mobile App vs Web App
- How to use ORDER BY in SQL Server on a column of a table?
- why com.ExecuteNonQuery() return -1 Always . in all cases
- Conditional Execution in SSIS based on previous SQL Task output
- Wants to get list of all junior doctors working under senior doctor and vice versa?
- Reusing SqlCommand?
- Cross Join Performance issue
- Execute sp_rename SQL command using pypyodbc
- What is better create new table or add columns in existing table
- Switch between INNER JOIN and LEFT JOIN
- SQL script to kill all blocked processes?
- Using MSSQL 2016 "AT TIMEZONE" feature from Entity Framework?
- Error while trying to connect to SQL Server - localhost
- SQL Server : move data to another table with additional data
- Moving SQL Server database decrease performance