Changing 2 fields - will I loose data?
-
We've got a table with 2 fields in it, both are defined as varchar(2000). I am thinking of changing the fields to varchar(MAX), or maybe even nvarchar(MAX). If I do, will we loose the data that is correctly stored in those fields of that table? We've got 10 years worth of data in that table, which we cannot afford to loose.
Kindest Regards, Rod Connect with me on LinkedIn.
-
Begin Try
Begin Tran
Alter table Table1
alter column column1 varchar(max) not null
End Try
Begin Catch
Rollback
End Catch
This works.
If you are worried, try it in Development. You do have backups dont you...!!!!!
Go for it.
-
oops..
Forgot the Commit Tran at the end......You get the essence of it...
-
Feels like Monday. The commit is before the End Try...
-
Ya try in developpement first. Once you get it working. Take backup of production db and run on Production DB. Take another backup and go take a break
. -
"Take another backup and go take a break..." I love it!
Maybe I'll make the change and then go on a vacation until after the Holidays. 
Kindest Regards, Rod Connect with me on LinkedIn.
-
If you do that I can garantee you another extended vacation right after that one
. -
Another alternative to try in development first is this:
1. Create a new table called tmp_tablename that is identical to your existing table, but with the columns your are changing defined as varchar(max) or nvarchar(max).
2. Insert all data from tablename to tmp_tablename.
3. drop (or rename using the function in 4) tablename.
4. exec sp_rename 'dbo.tmp_tablename', 'tablename';
-
I see no need to use an intermediate table for this... The task is too simple.
-
Just an alternative, and it ensures you don't lose data if that is a concern.
-
Can't lose data if you test the solution and have a backup before running the solution
.Also another solution I use to test something like this :
Select IDCol FROM dbo.MyTable WHERE ISNULL(Col1, '') <> ISNULL(CONVERT(VARCHAR(newsize)), '')
If nothing is found then the alter command will be safe so you can run something like this :
--you could even lock the entire table before runnig the exists statement just to be 100% sure
IF NOT EXISTS (Check query)
ALTER TABLE
else
error out or whatever
end
--another alter...
--unlock here
-
There are several ways to change fields. In place as was originally provided; using a temporary table; and a third is to add two new columns, copy the data from the original columns to the new columns, and then drop the old columns.
Your method above is a variation of the first.
It really is up to the individual charged with making the change, plus any policies that may be in place regarding schema changes, and which way they feel the most comfortable doing it.
Beyond that, we can only provide suggestions on how to things can be done.
-
Ninja,
You wrote:
Select IDCol FROM dbo.MyTable WHERE ISNULL(Col1, '') <> ISNULL(CONVERT(VARCHAR(newsize)), '')
What I am not following is the condition in the WHERE clause:
ISNULL(Col1, '') <> ISNULL(CONVERT(VARCHAR(newsize)), '')
What are you accomplishing here, please?
Kindest Regards, Rod Connect with me on LinkedIn.
-
On the contrary, this is a much simpler way to handle this than creating a full backup. When making a change on a single table, I've often done a simple Select Into to "backup" a single table. In many ways this is actually safer. If others are actively using the database (developers in dev or client users/app's in production), you don't have to roll back the entire database if an error happens losing work being done by other developers or client transactions.
Of course, in SQL 2005, I can just use a snapshot and query from that directly to roll back the data in the table.
Of course, the question wasn't how to do it. The question was would he lose data if he converted a varchar(2000) column to either varchar(max) or nvarchar(max). The answer to that question is no. You can potentially lose data if you change it to a smaller or more restrictive data type. You only lose it if can not fit into the new column definition.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply