CODEWITHSUNDEEP
c#.netsql-server
user1765862
user1765862

Reputation: 14145

Changing size of db column from varchar50 to varchar255

Inside microsoft sql server database I have table User with column Password. Password is of type varchar(50) and I want to change to varchar(255).

I tried using microsoft sql server management studio and using design view I tried manually to change 50 to 255 but on saving that action I'm getting alert

Following tables will be saved to your database. Do you want to continue?

User
xx
xx
xx

On chosing yes I'm waiting for whole min with mouse cursor busy and I'm getting back

Errors were encountered during the save process. Some database objects were not saved. User table, Unable to delete relationship FK..... Timeout expired.

I'm confused since I'm not changing anything but this lenght of Password column.

Is there any simple way to complete this simple operation, change varchar(50) to varchar(255)

Upvotes: 0

Views: 2087

Answers (1)

Cᴏʀʏ
Cᴏʀʏ

Reputation: 107508

When you use the designer to make changes, behind the scenes SSMS generates a huge script that creates a new temporary table, dumps all the data from the old one into the temporary one, deletes the old table and then renames the temporary table back to the name of the original name. It also is smart enough to drop foreign keys, constraints, etc. and then tries to put them all back later. Depending on the complexity of the table the script can be hundreds or thousands of lines long.

Instead, since you're really just asking for a little more space in your column which is keeping the same underlying data type, you could directly alter the data type of the column with a simple two-liner:

ALTER TABLE [dbo].[User]
    ALTER COLUMN [Password] varchar(255) NOT NULL
GO

(Adjust the script according to whether the column is nullable, has defaults, etc.)

Of course you should have a backup of the database that table is in before you do any of this. You may also need to drop existing connections to the database. It could be that the script is waiting for exclusive access to the table before it does any work.

Upvotes: 3

Related Questions