SQL Server Management Studio - 如何在不删除表的情况下更改字段类型 [英] SQL Server Management Studio - how to change a field type without dropping table

问题描述

在 SQL Server Management Studio 中，将设计视图中的现有字段从 DECIMAL (16,14) 更改为 DECIMAL (18,14) 时，它不会允许我保存而不删除整个表及其所有数据.

In SQL Server Management Studio, when changing an existing field in design view from DECIMAL (16,14) to DECIMAL (18,14) it will not allow me to save without dropping the whole table and all its data.

一旦数据库填充了记录，是否可以更改字段类型?

Is it possible to change a field type once a database is populated with records?

推荐答案

只需使用 T-SQL 脚本而不是可视化设计器即可实现您的目标:

Just use T-SQL script instead of the visual designer to achieve your goal:

ALTER TABLE dbo.YourTableNameHere
ALTER COLUMN YourColumnNameHere DECIMAL(18, 14) 

你应该没事.

视觉设计师采取了格外小心的方法，即使用新结构创建表格，然后复制所有数据 - 可行，但很乏味.通常，只要您不截断列(使其更短)，您绝对可以使用 T-SQL 语句就地"更改列的数据类型.

The visual designer takes the extra careful route of creating the table with the new structure and then copying over all the data - it works, but it's tedious. Normally, as long as you don't truncate a column (make it shorter), you can definitely change the column's datatype "in place" using a T-SQL statement.

此外:默认情况下，SSMS 设计器会格外小心，不允许任何需要删除和重新创建表周期的更改.您可以在 Tools > 中禁用这种额外的小心操作.选项然后在这个对话框中:

Also: by default the SSMS designer is extra careful and won't allow any changes that require a drop-and-recreate table cycle. You can disable this extra carefullness in Tools > Options and then in this dialog box:

如果取消选中该选项，您将能够在视觉设计器中进行破坏性"更改

If you uncheck that option, you will be able to do "destructive" changes in your visual designer

这篇关于SQL Server Management Studio - 如何在不删除表的情况下更改字段类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！