Ygthkb

Missing a connection and don't have money to book next flight

What happens if both players misunderstand the game state until it's too late?

What is formjacking?

Can you say "leftside right"?

What does @ mean in a hostname in DNS configuration?

How bad is a Computer Science course that doesn't teach Design Patterns?

Is it possible to detect 100% of SQLi with a simple regex?

How do I make my single-minded character more interested in the main story?

Is there a way to pause a running process on Linux systems and resume later?

How can changes in personality/values of a person who turned into a vampire be explained?

Have any astronauts or cosmonauts died in space?

Why is Shelob considered evil?

Are all power cords made equal?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Will the duration of traveling to Ceres using the same tech developed for going to Mars be proportional to the distance to go to Mars or not?

What does it mean for south of due west?

How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?

Buying a "Used" Router

How do I avoid the "chosen hero" feeling?

Identify non-coding regions from a genome annotation

Is there any danger of my neighbor having my wife's signature?

How to Build a List from Separate Lists

What could cause an entire planet of humans to become aphasic?

Why write a book when there's a movie in my head?

Changing column width

SQL Server VARCHAR Column WidthWhat is the best practice for storing large data when nvarchar(4000) is sufficient 99% of the time?Alternate ways to increase column sizeWill one nvarchar column affect query performance?Need help finding inactive users in my databaseWhy is it taking longer to create an index after column size increases?SSIS - Fixed width flat file mapping tool/techniqueDetect if only one column is changing in a triggerSSDT Drop and Recreate Tables when nothing has changedSQL Server FullText search on non-persisted columns

36

6

Will increasing a column (nvarchar) width necessarily drop the table?

In other words, can the width be changed in a production environment with active users?

I figured that if the size is increasing (as opposed to decreasing) this wouldn't be a problem.

sql-server

share|improve this question

asked Aug 30 '11 at 17:26

FernandoFernando

183124

add a comment | 

36

6

Will increasing a column (nvarchar) width necessarily drop the table?

In other words, can the width be changed in a production environment with active users?

I figured that if the size is increasing (as opposed to decreasing) this wouldn't be a problem.

sql-server

share|improve this question

asked Aug 30 '11 at 17:26

FernandoFernando

183124

add a comment | 

Will increasing a column (nvarchar) width necessarily drop the table?

In other words, can the width be changed in a production environment with active users?

I figured that if the size is increasing (as opposed to decreasing) this wouldn't be a problem.

sql-server

share|improve this question

asked Aug 30 '11 at 17:26

FernandoFernando

183124

share|improve this question

asked Aug 30 '11 at 17:26

FernandoFernando

183124

share|improve this question

asked Aug 30 '11 at 17:26

FernandoFernando

183124

asked Aug 30 '11 at 17:26

FernandoFernando

183124

asked Aug 30 '11 at 17:26

FernandoFernando

183124

2 Answers
2

active

oldest

votes

50

If you're doing it through a T-SQL statement such as below, then no table drop will occur and you can safely do it in a production environment:

alter table <table> alter column <column> nvarchar(biggernumber) [not] null

If you do it through the SSMS Design Table GUI, it will depend on what script it decides to use to implement the change. Sometimes it will insert data into a temporary table, drop the original table, create a new version of that table, and insert it back into the new one. An easy way to find out what it will do is to click the "Generate Script" button and look at the T-SQL it plans on executing.

share|improve this answer

answered Aug 30 '11 at 17:42

Derek KrommDerek Kromm

1,4011116

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I thought even the "Generate Script" button throws a warning that you are about to drop a table before even showing you the script.
  
  – Nick Chammas
  Aug 30 '11 at 18:17
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  It does throw up the error, but it doesn't actually run the script until you click on OK (or run or whatever) on the window with the script. You can copy the script and cancel the window which shows you the script.
  
  – mrdenny
  Aug 31 '11 at 8:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  but does it work on say a change from float to Decimal(n,n) ?
  
  – Nishanth Shaan
  Sep 14 '17 at 8:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What effect does this have if the table is replicated? I'm assuming the subscriber tables wouldn't update automatically?
  
  – tmwoods
  Mar 6 '18 at 18:16
  

  
  
  
  

add a comment | 

18

Increasing the column width of a nvarchar column won't require a table drop. Neither would any ALTER TABLE operation. For details about restrictions when changing table or column properties you can read up on the ALTER TABLE statement.

I copied the most relevant parts from the documentation below:

Changing the Size of a Column

You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. If data exists in the column, the new size cannot be smaller than the maximum size of the data. Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint. See example P.

Locks and ALTER TABLE

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

share|improve this answer

answered Aug 30 '11 at 17:41

MicSimMicSim

1,95111825

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Also great answer. But I could only pick one. Thanks!
  
  – Fernando
  Aug 30 '11 at 18:44
  

  
  
  
  

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
});


}
});

draft saved

draft discarded

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

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f5211%2fchanging-column-width%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

50

If you're doing it through a T-SQL statement such as below, then no table drop will occur and you can safely do it in a production environment:

alter table <table> alter column <column> nvarchar(biggernumber) [not] null

If you do it through the SSMS Design Table GUI, it will depend on what script it decides to use to implement the change. Sometimes it will insert data into a temporary table, drop the original table, create a new version of that table, and insert it back into the new one. An easy way to find out what it will do is to click the "Generate Script" button and look at the T-SQL it plans on executing.

share|improve this answer

answered Aug 30 '11 at 17:42

Derek KrommDerek Kromm

1,4011116

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I thought even the "Generate Script" button throws a warning that you are about to drop a table before even showing you the script.
  
  – Nick Chammas
  Aug 30 '11 at 18:17
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  It does throw up the error, but it doesn't actually run the script until you click on OK (or run or whatever) on the window with the script. You can copy the script and cancel the window which shows you the script.
  
  – mrdenny
  Aug 31 '11 at 8:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  but does it work on say a change from float to Decimal(n,n) ?
  
  – Nishanth Shaan
  Sep 14 '17 at 8:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What effect does this have if the table is replicated? I'm assuming the subscriber tables wouldn't update automatically?
  
  – tmwoods
  Mar 6 '18 at 18:16
  

  
  
  
  

add a comment | 

50

If you're doing it through a T-SQL statement such as below, then no table drop will occur and you can safely do it in a production environment:

alter table <table> alter column <column> nvarchar(biggernumber) [not] null

If you do it through the SSMS Design Table GUI, it will depend on what script it decides to use to implement the change. Sometimes it will insert data into a temporary table, drop the original table, create a new version of that table, and insert it back into the new one. An easy way to find out what it will do is to click the "Generate Script" button and look at the T-SQL it plans on executing.

share|improve this answer

answered Aug 30 '11 at 17:42

Derek KrommDerek Kromm

1,4011116

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I thought even the "Generate Script" button throws a warning that you are about to drop a table before even showing you the script.
  
  – Nick Chammas
  Aug 30 '11 at 18:17
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  It does throw up the error, but it doesn't actually run the script until you click on OK (or run or whatever) on the window with the script. You can copy the script and cancel the window which shows you the script.
  
  – mrdenny
  Aug 31 '11 at 8:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  but does it work on say a change from float to Decimal(n,n) ?
  
  – Nishanth Shaan
  Sep 14 '17 at 8:51
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What effect does this have if the table is replicated? I'm assuming the subscriber tables wouldn't update automatically?
  
  – tmwoods
  Mar 6 '18 at 18:16
  

  
  
  
  

add a comment | 

If you're doing it through a T-SQL statement such as below, then no table drop will occur and you can safely do it in a production environment:

alter table <table> alter column <column> nvarchar(biggernumber) [not] null

If you do it through the SSMS Design Table GUI, it will depend on what script it decides to use to implement the change. Sometimes it will insert data into a temporary table, drop the original table, create a new version of that table, and insert it back into the new one. An easy way to find out what it will do is to click the "Generate Script" button and look at the T-SQL it plans on executing.

share|improve this answer

answered Aug 30 '11 at 17:42

Derek KrommDerek Kromm

1,4011116

alter table <table> alter column <column> nvarchar(biggernumber) [not] null

share|improve this answer

answered Aug 30 '11 at 17:42

Derek KrommDerek Kromm

1,4011116

answered Aug 30 '11 at 17:42

Derek KrommDerek Kromm

1,4011116

answered Aug 30 '11 at 17:42

Derek KrommDerek Kromm

1,4011116

18

Increasing the column width of a nvarchar column won't require a table drop. Neither would any ALTER TABLE operation. For details about restrictions when changing table or column properties you can read up on the ALTER TABLE statement.

I copied the most relevant parts from the documentation below:

Changing the Size of a Column

You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. If data exists in the column, the new size cannot be smaller than the maximum size of the data. Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint. See example P.

Locks and ALTER TABLE

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

share|improve this answer

answered Aug 30 '11 at 17:41

MicSimMicSim

1,95111825

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Also great answer. But I could only pick one. Thanks!
  
  – Fernando
  Aug 30 '11 at 18:44
  

  
  
  
  

add a comment | 

18

Increasing the column width of a nvarchar column won't require a table drop. Neither would any ALTER TABLE operation. For details about restrictions when changing table or column properties you can read up on the ALTER TABLE statement.

I copied the most relevant parts from the documentation below:

Changing the Size of a Column

You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. If data exists in the column, the new size cannot be smaller than the maximum size of the data. Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint. See example P.

Locks and ALTER TABLE

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

share|improve this answer

answered Aug 30 '11 at 17:41

MicSimMicSim

1,95111825

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Also great answer. But I could only pick one. Thanks!
  
  – Fernando
  Aug 30 '11 at 18:44
  

  
  
  
  

add a comment | 

Increasing the column width of a nvarchar column won't require a table drop. Neither would any ALTER TABLE operation. For details about restrictions when changing table or column properties you can read up on the ALTER TABLE statement.

I copied the most relevant parts from the documentation below:

Changing the Size of a Column

You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. If data exists in the column, the new size cannot be smaller than the maximum size of the data. Also, the column cannot be defined in an index, unless the column is a varchar, nvarchar, or varbinary data type and the index is not the result of a PRIMARY KEY constraint. See example P.

Locks and ALTER TABLE

The changes specified in ALTER TABLE are implemented immediately. If the changes require modifications of the rows in the table, ALTER TABLE updates the rows. ALTER TABLE acquires a schema modify lock on the table to make sure that no other connections reference even the metadata for the table during the change, except online index operations that require a very short SCH-M lock at the end. In an ALTER TABLE…SWITCH operation, the lock is acquired on both the source and target tables. The modifications made to the table are logged and fully recoverable. Changes that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records. These ALTER TABLE statements should be executed with the same care as any INSERT, UPDATE, or DELETE statement that affects many rows.

share|improve this answer

answered Aug 30 '11 at 17:41

MicSimMicSim

1,95111825

share|improve this answer

answered Aug 30 '11 at 17:41

MicSimMicSim

1,95111825

answered Aug 30 '11 at 17:41

MicSimMicSim

1,95111825

answered Aug 30 '11 at 17:41

MicSimMicSim

1,95111825