61

Is it possible to change a column type in a SQL Server 2008 database from varchar(255) to varchar(MAX) without having to drop the table and recreate?

SQL Server Management Studio throws me an error every time I try to do it using that - but to save myself a headache would be nice to know if I can change the type without having to DROP and CREATE.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Darbio
  • 11,286
  • 12
  • 60
  • 100
  • 2
    Strange, tried it out in SQL Server 2005, works fine for me. – anonymous Apr 23 '10 at 03:22
  • 2
    BTW, what was the error thrown? – anonymous Apr 23 '10 at 03:29
  • 1
    'Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require table to be re-created.' Option 'Prevent saving changes' is not enabled... – Darbio Apr 23 '10 at 03:34
  • SQL Management Studio does that (see the change script for the SQL they use to alter the table), @astander's answer is a better alternative. Glad you found your solution :D – anonymous Apr 23 '10 at 04:25

3 Answers3

104

You should be able to do it using TSQL.

Something like

ALTER TABLE [table] ALTER COLUMN [column] VARCHAR(MAX)
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
15

'Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require table to be re-created.' Option 'Prevent saving changes' is not enabled..

That's a new "feature" in SQL Server Management Studio 2008 which by default is turned on. Whenever you make a larger change, SSMS can only recreate the table by creating a new one and then moving over the data from the old one - all in the background (those changes include re-ordering of your columns amongst other things).

This option is turned off by default, since if your table has FK constraints and stuff, this way of re-doing the table might fail. But you can definitely turn that feature on!

alt text

It's under Tools > Options and once you uncheck that option you can do these kind of changes to table structure in the table designer again.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks marc_s. I already had this un-ticked and it was still stopping me from making the changes. The alter statements by astander above worked when I wrote them themselves. Thanks :) – Darbio Apr 23 '10 at 06:54
  • @JD: hmm... odd... usually that is the issue - glad you already have a solution, though! – marc_s Apr 23 '10 at 11:56
3

Be aware
with Something like

ALTER TABLE [table] ALTER COLUMN [column] VARCHAR(MAX)

https://dba.stackexchange.com/questions/15007/change-length-of-varchar-on-live-prod-table

Martin Smith's answare:

If you are increasing it to varchar(100 - 8000) (i.e. anything other than varchar(max)) and you are doing this through TSQL rather than the SSMS GUI 

ALTER TABLE YourTable ALTER COLUMN YourCol varchar(200) [NOT] NULL
and not altering column nullability from NULL to NOT NULL (which would lock the table while all rows are validated and potentially written to or from NOT NULL to NULL in some circumstances then this is a quick metadata only change. It might need to wait for a SCH-M lock on the table but once it acquires that the change will be pretty much instant. One caveat to be aware of is that during the wait for a SCH-M lock other queries will be blocked rather than jump the queue ahead of it so you might want to consider adding a SET LOCK_TIMEOUT first. Also make sure in the ALTER TABLE statement you explicitly specify NOT NULL if that is the original column state as otherwise the column will be changed to allow NULL.
Community
  • 1
  • 1
Slava
  • 39
  • 3