Increase speed altering column on large table to NON NULLRunning out of Transaction Log space during Alter TableQuickly change NULL column to NOT NULLAltering a table to add a New Column vs Create table with New ColumnAdding nullable column to table costs more than 10 minutesSQL Server - Export large table without primary keyAlter All Data Types To Different Data TypeWill altering an nvarchar(max) column to nvarchar(50) lock the table?Does ALTER TABLE - ADD COLUMN lock the table?Architecture design for a large number of db records upgradeWhat is the fastest way to convert many nullable ints 1:1 to a binary string?
How do I become a better writer when I hate reading?
How to know whether to write accidentals as sharps or flats?
How do you translate “talk shit”?
Arcane Tradition and Cost Efficiency: Learn spells on level-up, or learn them from scrolls/spellbooks?
Do items with curse of vanishing disappear from shulker boxes?
Interview was just a one hour panel. Got an offer the next day; do I accept or is this a red flag?
My players want to use called-shots on Strahd
Can an opamp have its own voltage regulator?
Will users know a CardView is clickable
Manager wants to hire me; HR does not. How to proceed?
Does PC weight have a mechanical effect?
What is wind "CALM"?
How do credit card companies know what type of business I'm paying for?
...and then she held the gun
What is the difference between state-based effects and effects on the stack?
How to test soql with For Update statement
Difference between "drift" and "wander"
How to avoid offending original culture when making conculture inspired from original
How to make a villain when your PCs are villains?
How can this shape perfectly cover a cube?
Was the Lonely Mountain, where Smaug lived, a volcano?
Nth term of Van Eck Sequence
Does an African-American baby born in Youngstown, Ohio have a higher infant mortality rate than a baby born in Iran?
Struggling to present results from long papers in short time slots
Increase speed altering column on large table to NON NULL
Running out of Transaction Log space during Alter TableQuickly change NULL column to NOT NULLAltering a table to add a New Column vs Create table with New ColumnAdding nullable column to table costs more than 10 minutesSQL Server - Export large table without primary keyAlter All Data Types To Different Data TypeWill altering an nvarchar(max) column to nvarchar(50) lock the table?Does ALTER TABLE - ADD COLUMN lock the table?Architecture design for a large number of db records upgradeWhat is the fastest way to convert many nullable ints 1:1 to a binary string?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time routine to assign 0 or 1 to all existing rows (updating the rows in small batches). Every row should now have a 0 or 1 in that column.
I want to make the bit column non-nullable however when I tried doing so via ALTER TABLE t1 ALTER COLUMN c1 bit not null, it started running for 3 minutes and I stopped it because it was blocking all reads to the table and I suspected it was going to take a long time to complete. It's possible it wouldn't take too long but I couldn't risk too much unavailability. The rollback itself took 6 minutes.
Do you have any suggestions on how I can make the column non-nullable without it taking potentially hours to complete? Additionally is there any way to estimate how long the ALTER TABLE ALTER COLUMN statement I started and then cancelled would take to complete?
I am using SQL Server 2017 Web Edition.
sql-server sql-server-2017 alter-table null
edited Jun 7 at 16:55
Comment Converter
1,4511327
asked Jun 7 at 12:51
Ben AmadaBen Amada
1615
New contributor
Ben Amada is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time routine to assign 0 or 1 to all existing rows (updating the rows in small batches). Every row should now have a 0 or 1 in that column.
I want to make the bit column non-nullable however when I tried doing so via ALTER TABLE t1 ALTER COLUMN c1 bit not null, it started running for 3 minutes and I stopped it because it was blocking all reads to the table and I suspected it was going to take a long time to complete. It's possible it wouldn't take too long but I couldn't risk too much unavailability. The rollback itself took 6 minutes.
Do you have any suggestions on how I can make the column non-nullable without it taking potentially hours to complete? Additionally is there any way to estimate how long the ALTER TABLE ALTER COLUMN statement I started and then cancelled would take to complete?
I am using SQL Server 2017 Web Edition.
sql-server sql-server-2017 alter-table null
edited Jun 7 at 16:55
Comment Converter
1,4511327
asked Jun 7 at 12:51
Ben AmadaBen Amada
1615
New contributor
Ben Amada is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time routine to assign 0 or 1 to all existing rows (updating the rows in small batches). Every row should now have a 0 or 1 in that column.
I want to make the bit column non-nullable however when I tried doing so via ALTER TABLE t1 ALTER COLUMN c1 bit not null, it started running for 3 minutes and I stopped it because it was blocking all reads to the table and I suspected it was going to take a long time to complete. It's possible it wouldn't take too long but I couldn't risk too much unavailability. The rollback itself took 6 minutes.
Do you have any suggestions on how I can make the column non-nullable without it taking potentially hours to complete? Additionally is there any way to estimate how long the ALTER TABLE ALTER COLUMN statement I started and then cancelled would take to complete?
I am using SQL Server 2017 Web Edition.
sql-server sql-server-2017 alter-table null
edited Jun 7 at 16:55
Comment Converter
1,4511327
asked Jun 7 at 12:51
Ben AmadaBen Amada
1615
New contributor
Ben Amada is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I recently added a NULL-able bit column to a table which has close to 500 million rows. There's not a default on the column, however all inserts are specifying a value of 0 or 1, and I ran a one-time routine to assign 0 or 1 to all existing rows (updating the rows in small batches). Every row should now have a 0 or 1 in that column.
I want to make the bit column non-nullable however when I tried doing so via ALTER TABLE t1 ALTER COLUMN c1 bit not null, it started running for 3 minutes and I stopped it because it was blocking all reads to the table and I suspected it was going to take a long time to complete. It's possible it wouldn't take too long but I couldn't risk too much unavailability. The rollback itself took 6 minutes.
Do you have any suggestions on how I can make the column non-nullable without it taking potentially hours to complete? Additionally is there any way to estimate how long the ALTER TABLE ALTER COLUMN statement I started and then cancelled would take to complete?
I am using SQL Server 2017 Web Edition.
sql-server sql-server-2017 alter-table null
sql-server sql-server-2017 alter-table null
edited Jun 7 at 16:55
Comment Converter
1,4511327
asked Jun 7 at 12:51
Ben AmadaBen Amada
1615
New contributor
Ben Amada is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Jun 7 at 16:55
Comment Converter
1,4511327
asked Jun 7 at 12:51
Ben AmadaBen Amada
1615
New contributor
Ben Amada is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited Jun 7 at 16:55
Comment Converter
1,4511327
edited Jun 7 at 16:55
Comment Converter
1,4511327
edited Jun 7 at 16:55
Comment Converter
1,4511327
1,4511327
asked Jun 7 at 12:51
Ben AmadaBen Amada
1615
New contributor
Ben Amada is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked Jun 7 at 12:51
Ben AmadaBen Amada
1615
asked Jun 7 at 12:51
Ben AmadaBen Amada
1615
1615
New contributor
Ben Amada is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Ben Amada is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held.
You can then remove the constraint and change the column definition during your next maintenance window.
answered Jun 7 at 15:38
Joe ObbishJoe Obbish
23.3k43499
Thanks Joe for the idea. I almost went with adding a check constraint but had a maintenance window over the weekend and was able to alter the column to be non-nullable. I'm not sure if it helped, but to try and get the table data into the buffer pool, right before making the column non-nullable, I ranSELECT c1, count(*) FROM t1 GROUP BY c1which took about 9 minutes to run. The actualALTER TABLE ALTER COLUMNstatement after that took 25 minutes to complete. Not too bad.
– Ben Amada
Jun 10 at 19:07
add a comment |
If you are on Enterprise Edition (EE) a better strategy might have been to add it as NOT NULL with a default of 0 or 1 (whichever is most common).
This is a metadata-only change in EE. Then update the ones needing to be flipped. This means fewer updates and no need to change the column nullability when finished. - martin-smith
Interestingly I noticed this feature last week when I tested adding a non-nullable bit column with a default value to this same large table on my local machine which is running SQL developer edition -- the column added instantly and I couldn't figure out why. It later him me that it must be because developer edition includes EE features.
– Ben Amada
Jun 10 at 19:01
add a comment |
Try copying the data to a new table, then rename it. You have to take care of any constraints and indexes. That's what SSMS table designer is doing when you want to reorder the columns (for example), but you should check the script to see if there is anything that doesn't look right.
During the copying, read access to the source table is not a problem, but if there are any writes they may be blocked or not copied, depending on the isolation level.
answered Jun 9 at 16:13
Razvan SocolRazvan Socol
1906
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Ben Amada is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f240055%2fincrease-speed-altering-column-on-large-table-to-non-null%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held.
You can then remove the constraint and change the column definition during your next maintenance window.
answered Jun 7 at 15:38
Joe ObbishJoe Obbish
23.3k43499
Thanks Joe for the idea. I almost went with adding a check constraint but had a maintenance window over the weekend and was able to alter the column to be non-nullable. I'm not sure if it helped, but to try and get the table data into the buffer pool, right before making the column non-nullable, I ranSELECT c1, count(*) FROM t1 GROUP BY c1which took about 9 minutes to run. The actualALTER TABLE ALTER COLUMNstatement after that took 25 minutes to complete. Not too bad.
– Ben Amada
Jun 10 at 19:07
add a comment |
Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held.
You can then remove the constraint and change the column definition during your next maintenance window.
answered Jun 7 at 15:38
Joe ObbishJoe Obbish
23.3k43499
Thanks Joe for the idea. I almost went with adding a check constraint but had a maintenance window over the weekend and was able to alter the column to be non-nullable. I'm not sure if it helped, but to try and get the table data into the buffer pool, right before making the column non-nullable, I ranSELECT c1, count(*) FROM t1 GROUP BY c1which took about 9 minutes to run. The actualALTER TABLE ALTER COLUMNstatement after that took 25 minutes to complete. Not too bad.
– Ben Amada
Jun 10 at 19:07
add a comment |
Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held.
You can then remove the constraint and change the column definition during your next maintenance window.
answered Jun 7 at 15:38
Joe ObbishJoe Obbish
23.3k43499
Instead of changing the column definition you could add a CHECK CONSTRAINT that doesn't allow NULLs for that column. The table will still need to be scanned but it won't need to modify every single data page, so it should be a much faster operation. Regrettably, a Sch-M lock will still be held during the operation. One trick is to try to get as much of the table into the buffer pool as possible before attempting to add the constraint. That may reduce the amount of time the Sch-M lock is held.
You can then remove the constraint and change the column definition during your next maintenance window.
answered Jun 7 at 15:38
Joe ObbishJoe Obbish
23.3k43499
edited Jun 7 at 15:52
answered Jun 7 at 15:38
Joe ObbishJoe Obbish
23.3k43499
answered Jun 7 at 15:38
Joe ObbishJoe Obbish
23.3k43499
answered Jun 7 at 15:38
Joe ObbishJoe Obbish
23.3k43499
23.3k43499
Thanks Joe for the idea. I almost went with adding a check constraint but had a maintenance window over the weekend and was able to alter the column to be non-nullable. I'm not sure if it helped, but to try and get the table data into the buffer pool, right before making the column non-nullable, I ranSELECT c1, count(*) FROM t1 GROUP BY c1which took about 9 minutes to run. The actualALTER TABLE ALTER COLUMNstatement after that took 25 minutes to complete. Not too bad.
– Ben Amada
Jun 10 at 19:07
add a comment |
Thanks Joe for the idea. I almost went with adding a check constraint but had a maintenance window over the weekend and was able to alter the column to be non-nullable. I'm not sure if it helped, but to try and get the table data into the buffer pool, right before making the column non-nullable, I ranSELECT c1, count(*) FROM t1 GROUP BY c1which took about 9 minutes to run. The actualALTER TABLE ALTER COLUMNstatement after that took 25 minutes to complete. Not too bad.
– Ben Amada
Jun 10 at 19:07
Thanks Joe for the idea. I almost went with adding a check constraint but had a maintenance window over the weekend and was able to alter the column to be non-nullable. I'm not sure if it helped, but to try and get the table data into the buffer pool, right before making the column non-nullable, I ran
SELECT c1, count(*) FROM t1 GROUP BY c1 which took about 9 minutes to run. The actual ALTER TABLE ALTER COLUMN statement after that took 25 minutes to complete. Not too bad.– Ben Amada
Jun 10 at 19:07
Thanks Joe for the idea. I almost went with adding a check constraint but had a maintenance window over the weekend and was able to alter the column to be non-nullable. I'm not sure if it helped, but to try and get the table data into the buffer pool, right before making the column non-nullable, I ran
SELECT c1, count(*) FROM t1 GROUP BY c1 which took about 9 minutes to run. The actual ALTER TABLE ALTER COLUMN statement after that took 25 minutes to complete. Not too bad.– Ben Amada
Jun 10 at 19:07
add a comment |
If you are on Enterprise Edition (EE) a better strategy might have been to add it as NOT NULL with a default of 0 or 1 (whichever is most common).
This is a metadata-only change in EE. Then update the ones needing to be flipped. This means fewer updates and no need to change the column nullability when finished. - martin-smith
Interestingly I noticed this feature last week when I tested adding a non-nullable bit column with a default value to this same large table on my local machine which is running SQL developer edition -- the column added instantly and I couldn't figure out why. It later him me that it must be because developer edition includes EE features.
– Ben Amada
Jun 10 at 19:01
add a comment |
If you are on Enterprise Edition (EE) a better strategy might have been to add it as NOT NULL with a default of 0 or 1 (whichever is most common).
This is a metadata-only change in EE. Then update the ones needing to be flipped. This means fewer updates and no need to change the column nullability when finished. - martin-smith
Interestingly I noticed this feature last week when I tested adding a non-nullable bit column with a default value to this same large table on my local machine which is running SQL developer edition -- the column added instantly and I couldn't figure out why. It later him me that it must be because developer edition includes EE features.
– Ben Amada
Jun 10 at 19:01
add a comment |
If you are on Enterprise Edition (EE) a better strategy might have been to add it as NOT NULL with a default of 0 or 1 (whichever is most common).
This is a metadata-only change in EE. Then update the ones needing to be flipped. This means fewer updates and no need to change the column nullability when finished. - martin-smith
If you are on Enterprise Edition (EE) a better strategy might have been to add it as NOT NULL with a default of 0 or 1 (whichever is most common).
This is a metadata-only change in EE. Then update the ones needing to be flipped. This means fewer updates and no need to change the column nullability when finished. - martin-smith
edited Jun 7 at 15:37
community wiki
2 revs, 2 users 91%
Comment Converter
Interestingly I noticed this feature last week when I tested adding a non-nullable bit column with a default value to this same large table on my local machine which is running SQL developer edition -- the column added instantly and I couldn't figure out why. It later him me that it must be because developer edition includes EE features.
– Ben Amada
Jun 10 at 19:01
add a comment |
Interestingly I noticed this feature last week when I tested adding a non-nullable bit column with a default value to this same large table on my local machine which is running SQL developer edition -- the column added instantly and I couldn't figure out why. It later him me that it must be because developer edition includes EE features.
– Ben Amada
Jun 10 at 19:01
Interestingly I noticed this feature last week when I tested adding a non-nullable bit column with a default value to this same large table on my local machine which is running SQL developer edition -- the column added instantly and I couldn't figure out why. It later him me that it must be because developer edition includes EE features.
– Ben Amada
Jun 10 at 19:01
Interestingly I noticed this feature last week when I tested adding a non-nullable bit column with a default value to this same large table on my local machine which is running SQL developer edition -- the column added instantly and I couldn't figure out why. It later him me that it must be because developer edition includes EE features.
– Ben Amada
Jun 10 at 19:01
add a comment |
Try copying the data to a new table, then rename it. You have to take care of any constraints and indexes. That's what SSMS table designer is doing when you want to reorder the columns (for example), but you should check the script to see if there is anything that doesn't look right.
During the copying, read access to the source table is not a problem, but if there are any writes they may be blocked or not copied, depending on the isolation level.
answered Jun 9 at 16:13
Razvan SocolRazvan Socol
1906
add a comment |
Try copying the data to a new table, then rename it. You have to take care of any constraints and indexes. That's what SSMS table designer is doing when you want to reorder the columns (for example), but you should check the script to see if there is anything that doesn't look right.
During the copying, read access to the source table is not a problem, but if there are any writes they may be blocked or not copied, depending on the isolation level.
answered Jun 9 at 16:13
Razvan SocolRazvan Socol
1906
add a comment |
Try copying the data to a new table, then rename it. You have to take care of any constraints and indexes. That's what SSMS table designer is doing when you want to reorder the columns (for example), but you should check the script to see if there is anything that doesn't look right.
During the copying, read access to the source table is not a problem, but if there are any writes they may be blocked or not copied, depending on the isolation level.
answered Jun 9 at 16:13
Razvan SocolRazvan Socol
1906
Try copying the data to a new table, then rename it. You have to take care of any constraints and indexes. That's what SSMS table designer is doing when you want to reorder the columns (for example), but you should check the script to see if there is anything that doesn't look right.
During the copying, read access to the source table is not a problem, but if there are any writes they may be blocked or not copied, depending on the isolation level.
answered Jun 9 at 16:13
Razvan SocolRazvan Socol
1906
answered Jun 9 at 16:13
Razvan SocolRazvan Socol
1906
answered Jun 9 at 16:13
Razvan SocolRazvan Socol
1906
answered Jun 9 at 16:13
Razvan SocolRazvan Socol
1906
1906
add a comment |
add a comment |
Ben Amada is a new contributor. Be nice, and check out our Code of Conduct.
Ben Amada is a new contributor. Be nice, and check out our Code of Conduct.
Ben Amada is a new contributor. Be nice, and check out our Code of Conduct.
Ben Amada is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f240055%2fincrease-speed-altering-column-on-large-table-to-non-null%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
