Any consequences when re-creating a table?

Question

If I want to extend a column length from 30 to 60 characters for a varchar column in SQL Server Management Studio, I have to uncheck the box Prevent saving changes that require table re-creation.

Are there any side effects to doing this table re-creation? Will I reset the identity or increment seed, loose indices or find any other unwanted effects?

I need to do this on a live DB and I don't want to find out the hard way that something has changed.

score 1 · Accepted Answer · answered May 14 '13 at 11:21

This is a metadata only change that does not require rebuilding the table.

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR(60) [NOT] NULL

Regarding your question "are there any side effects to doing this table re-creation" through the GUI.

It is completely unnecessary overhead in this case and would block concurrent users until the table is copied and recreated. Additionally the designer has known bugs when doing this (e.g. drops FILESTREAM attribute from columns)

Mm, better not mess around with SQLSMMS then if it's this easy. — Niklas, May 14 '13 at 11:38

score 0 · Answer 2 · edited May 23 '17 at 12:30

0

There are similar questions like this asked before. Look at the answers to: "Prevent saving changes that require the table to be re-created" negative effects and Can't change table design in SQL Server 2008

Good luck. //Flipbed

edited May 23 '17 at 12:30

Community

1
1

answered May 14 '13 at 11:18

Flipbed

719
9
18

Any consequences when re-creating a table?

2 Answers2