I am trying to alter a table field - with some rows in it - from DateTime to DateTime2(3).
我试图改变一个表字段 - 其中包含一些行 - 从DateTime到DateTime2（3）。
But the SQL Server Management Studio complains that I have drop and re-create the table.
但SQL Server Management Studio抱怨我已经删除并重新创建表。
But why?
但为什么？
Isn't DateTime2(3) has more precision than DateTime type? It should be fine, should not it be?
是不是DateTime2（3）比DateTime类型更精确？应该没问题，不应该吗？
2 个解决方案
#1
6
There is a setting in SSMS that will allow you to do what you want.. Menu-Tools-Options-Designers-Prevent saving changes that require table re-creaction.
SSMS中有一个设置可以让你做你想做的事情。菜单 - 工具 - 选项 - 设计师 - 防止保存需要表重新创建的更改。
SSMS has a habit of recreating almost any changes you do. It should be just fine to only alter the column data type with something like this.
SSMS习惯于重新创建几乎所做的任何更改。只使用这样的方法改变列数据类型应该没问题。
alter table TableName alter column ColName datetime2(3)
#2
3
You can also do this without rebuilding the table (as Management Studio does behind the scenes).
您也可以在不重建表的情况下执行此操作（因为Management Studio在幕后执行）。
ALTER TABLE T ALTER COLUMN D DateTime2(3) [NOT NULL]
This will be less resource intensive up front but leave the "old" column behind in the data pages so will have an effect ongoing until you rebuild the table.
这将预先减少资源密集度，但在数据页中留下“旧”列，因此在重建表之前会产生影响。
#1
6
There is a setting in SSMS that will allow you to do what you want.. Menu-Tools-Options-Designers-Prevent saving changes that require table re-creaction.
SSMS中有一个设置可以让你做你想做的事情。菜单 - 工具 - 选项 - 设计师 - 防止保存需要表重新创建的更改。
SSMS has a habit of recreating almost any changes you do. It should be just fine to only alter the column data type with something like this.
SSMS习惯于重新创建几乎所做的任何更改。只使用这样的方法改变列数据类型应该没问题。
alter table TableName alter column ColName datetime2(3)
#2
3
You can also do this without rebuilding the table (as Management Studio does behind the scenes).
您也可以在不重建表的情况下执行此操作（因为Management Studio在幕后执行）。
ALTER TABLE T ALTER COLUMN D DateTime2(3) [NOT NULL]
This will be less resource intensive up front but leave the "old" column behind in the data pages so will have an effect ongoing until you rebuild the table.
这将预先减少资源密集度，但在数据页中留下“旧”列，因此在重建表之前会产生影响。