6

I am trying to alter a table field - with some rows in it - from DateTime to DateTime2(3).

But the SQL Server Management Studio complains that I have drop and re-create the table.

But why?

Isn't DateTime2(3) has more precision than DateTime type? It should be fine, should not it be?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pencilCake
  • 51,323
  • 85
  • 226
  • 363
  • 1
    Which *database system* are you using? – Lion Dec 29 '11 at 14:05
  • 4
    @Lion "*Management Studio*" implies SQL Server. – Yuck Dec 29 '11 at 14:06
  • 1
    See this: http://stackoverflow.com/questions/3896713/sql-server-management-studio-adding-moving-columns-require-drop-and-re-create – ypercubeᵀᴹ Dec 29 '11 at 14:07
  • Are you using the GUI tool to alter columns or a real ALTER TABLE statement? The GUI tools often incorrectly insist you drop and recreate tables when the pure SQL operation performs fine. – Ben Brocka Dec 29 '11 at 14:10
  • and this: http://stackoverflow.com/questions/2431901/forcing-management-studio-to-use-alter-table-instead-of-drop-create – ypercubeᵀᴹ Dec 29 '11 at 14:10

2 Answers2

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 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.

alter table TableName alter column ColName datetime2(3)
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
3

You can also do this without rebuilding the table (as Management Studio does behind the scenes).

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • didn't know that about alter table alter column. Not saying you're wrong, but do you have a reference? Thanks! – Ben Thul Dec 30 '11 at 00:58
  • 1
    @BenThul [Here's one](http://sqlblog.com/blogs/kalen_delaney/archive/2006/10/13/alter-table-will-not-reclaim-space.aspx) – Martin Smith Dec 30 '11 at 01:44