SQL Varchar(255) 到 Varchar(MAX)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2695994/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Varchar(255) to Varchar(MAX)
提问by Darbio
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 2008 数据库中的列类型从varchar(255)更改为 ，varchar(MAX)而无需删除表并重新创建？
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.
每次我尝试使用它时，SQL Server Management Studio 都会向我抛出一个错误 - 但为了避免自己头疼，如果我可以更改类型而不必 DROP 和 CREATE，那就太好了。
Thanks
谢谢
回答by Adriaan Stander
You should be able to do it using TSQL.
您应该能够使用 TSQL 来做到这一点。
Something like
就像是
ALTER TABLE [table] ALTER COLUMN [column] VARCHAR(MAX)
回答by marc_s
'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).
这是 SQL Server Management Studio 2008 中的一项新“功能”，默认情况下已启用。每当您进行较大的更改时，SSMS 只能通过创建一个新表然后从旧表中移动数据来重新创建表 - 所有这些都在后台进行（这些更改包括对列进行重新排序等）。
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!
这个选项默认是关闭的，因为如果你的表有 FK 约束和东西，这种重新做表的方式可能会失败。但是您绝对可以打开该功能！
It's under Tools > Optionsand once you uncheck that option you can do these kind of changes to table structure in the table designer again.
它位于下方Tools > Options，取消选中该选项后，您可以再次在表设计器中对表结构进行此类更改。
回答by Slava
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
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
如果您将其增加到varchar(100 - 8000)（即，除了varchar(max))您通过 TSQL 而不是 SSMS GUI 执行此操作之外的任何其他内容）
ALTER TABLE YourTable ALTER COLUMN YourCol varchar(200) [NOT] NULL并且不将列的可空性从
NULLNULLto更改NOT NULLNOT NULL（这将在验证所有行时锁定表并可能在某些情况下写入或从NOT NULLNOT NULLto写入， NULLNULL那么这是一个快速的元数据更改。它可能需要等待SCH-MSCH-M表上的锁定，但一次它获取更改将几乎是即时的。需要注意的一个警告是，在等待SCH-MSCH-M锁定期间，其他查询将被阻塞，而不是在它前面跳入队列，因此您可能需要考虑添加SET LOCK_TIMEOUTSET LOCK_TIMEOUT第一个。也使确保在ALTER TABLEALTER TABLE语句中明确指定NOT NULLNOT NULL这是否是原始列状态，否则该列将更改为 allow NULLNULL。