SQL Server Management Studio - how to change a field type without dropping table
Just use T-SQL script instead of the visual designer to achieve your goal:
ALTER TABLE dbo.YourTableNameHere
ALTER COLUMN YourColumnNameHere DECIMAL(18, 14)
and you should be fine.
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.
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
How to change a column without dropping a table in SQL 2008
In SQL Server 2008, go to Tools >> Options. In the little window, click "Designer". Uncheck "Prevent saving changes that require ..."
=====
Edited on Sept 4th, 2015.
I have added this answer here a long, long time ago describing the way I would solve the situation described on the question above. Since then, users on the threads below have exposed several concerns on doing things the way I recommended at the time. Basically, the solution I described could be problematic on some scenarios. I then suggest you to keep on reading to check other users' comments and pick the best solution for you.
Changing the data type without dropping the table in sql server 2008
See this answer:
SQL Server Management Studio - how to change a field type without dropping table
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.
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
How to change column datatype in SQL Server database without losing data?
You can easily do this using the following command. Any value of 0 will be turned into a 0 (BIT = false), anything else will be turned into 1 (BIT = true).
ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumnName BIT
The other option would be to create a new column of type BIT
, fill it from the old column, and once you're done, drop the old column and rename the new one to the old name. That way, if something during the conversion goes wrong, you can always go back since you still have all the data..
How to change the data type of a column without dropping the column with query?
If ALTER COLUMN doesn't work.
It is not unusual for alter column to fail because it cannot make the transformation you desire. In this case, the solution is to create a dummy table TableName_tmp, copy the data over with your specialized transformation in the bulk Insert command, drop the original table, and rename the tmp table to the original table's name. You'll have to drop and recreate the Foreign key constraints and, for performance, you'll probably want to create keys after filling the tmp table.
Sound like a lot of work? Actually, it isn't.
If you are using SQL Server, you can make the SQL Server Management Studio do the work for you!
- Bring up your table structure (right-click on the table column and select "Modify")
- Make all of your changes (if the column transformation is illegal, just add your new column - you'll patch it up in a moment).
- Right-click on the background of the Modify window and select "Generate Change Script." In the window that appears, you can copy the change script to the clipboard.
- Cancel the Modify (you'll want to test your script, after all) and then paste the script into a new query window.
- Modify as necessary (e.g. add your transformation while removing the field from the tmp table declaration) and you now have the script necessary to make your transformation.
Change column type without losing data
You don't need to add a new column two times, just remove the old one after updating the new one:
ALTER TABLE table_name ADD new_column_name decimal(18,2)
update table_name
set new_column_name = convert(decimal(18,2), old_column_name)
ALTER TABLE table_name DROP COLUMN old_column_name
Note that if the old_column_name
is not numeric, the convert
may fail.
How to change datatype whithout dropping dependencies
We found a way to do this. Although it probably isn't the best solution, it worked for us so if anyone has the same problem, try the following:
In SQL Server Management Studio go to Tools -> Options -> Designer
and uncheck the box of "Prevent saving changes that require table re-creation"
Next rightclick the table you want to modify the column datatypes and click on "Design".
In the designer, edit the column datatype to the one you need.
Finally, right-click and choose "Generate Change Script".
What it does is the following:
- Drop the constraints on the table
- Create a temp table with the new datatype of the column
- Readd constraints to the temp table
- Set the
IDENTITY_INSERT
on the temp table toON
- Copy all data from the old table to the new temp table
- Set the
IDENTITY_INSERT
on the temp table toOFF
- Drop the old table
- Rename the temp table to the name of the old table
- Readd the primary key constraint
- Recreate the indexes
- Readd the foreign key constraints
Additionally you have to refresh all depending views.
You can generate the statements with this script:
SELECT DISTINCT 'EXEC sp_refreshview ''' + s.name + '.' + so.name + '''' AS 'dbo.TABLENAME'
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
ON so.object_id = sed.referencing_id
INNER JOIN sys.schemas AS s
ON so.schema_id = s.schema_id
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('dbo.TABLENAME');
- Check the box of "Prevent saving changes that require table re-creation" in
Tools -> Options -> Designer
Please be careful about this! See if it really does what you are looking for. Keep in mind that this drops the old table. Test this in a development environment!
Saving changes after table edit in SQL Server Management Studio
Go into Tools -> Options -> Designers-> Uncheck "Prevent saving changes that require table re-creation". Voila.
That happens because sometimes it is necessary to drop and recreate a table in order to change something. This can take a while, since all data must be copied to a temp table and then re-inserted in the new table. Since SQL Server by default doesn't trust you, you need to say "OK, I know what I'm doing, now let me do my work."
Related Topics
Does Inner Join Performance Depends on Order of Tables
How to Fix Ora-01427 Single-Row Subquery Returns More Than One Row in Select
Ms-Access -> Select as + Order by = Error
Stop MySQL Reusing Auto_Increment Ids
How to Have a Tableless Select with Multiple Rows
Setting Up Foreign Key with Different Datatype
SQL Server Regular Expressions
How to Deal with Single Quote in Word Vba SQL Query
Register Clr Function (Wcf Based) in SQL Server 2012
Using 'In' with a Sub-Query in SQL Statements
How to Convert the System Date Format to Dd/Mm/Yy in SQL Server 2008 R2
How to Return Empty Groups in SQL Group by Clause
Why Does Comparing a SQL Date Variable to Null Behave in This Way
Ora-30926: Unable to Get a Stable Set of Rows in the Source Tables When Merging Tables