October 15, 2010 at 1:39 pm
I know that I can get into SSMS, bring up the design of any table and change its data type, but what happens to the data that's there? For example, if I were to change a column that is a VARCHAR data type to NVARCHAR, and leave the length of the field alone, would it retain all of the data that's already there?
Kindest Regards, Rod Connect with me on LinkedIn.
October 15, 2010 at 1:55 pm
Changing the column type as you indicated should not produce any issues, so long as the data currently there fits into the new datatype.
Whenever you alter the structure of a table, it can cause index fragmentation, though. You will want to use DBCC INDEXDEFRAG for each altered table.
The data get's converted in place as long as they fit into the new datatype. Inyour case there shouldn't The only thing you have to keep in mind is while you can store up 8000 char in avarchar column, nvarchar uses twice the space and, thus, the limit is 4000 characters.
October 18, 2010 at 9:12 am
upstart (10/15/2010)
Changing the column type as you indicated should not produce any issues, so long as the data currently there fits into the new datatype.Whenever you alter the structure of a table, it can cause index fragmentation, though. You will want to use DBCC INDEXDEFRAG for each altered table.
The data get's converted in place as long as they fit into the new datatype. Inyour case there shouldn't The only thing you have to keep in mind is while you can store up 8000 char in avarchar column, nvarchar uses twice the space and, thus, the limit is 4000 characters.
Thank you for our response. And I really appreciate your information about the possible cutting in half of large amounts of VARCHAR data! I think we've got a few VARCHAR fields that are large (3000 to 4000 characters). So, before we make those changes, we've got to come up with some idea of saving that data.
Kindest Regards, Rod Connect with me on LinkedIn.
October 18, 2010 at 10:39 am
Can you rename the existing column, create a new column of the type and size you want, populate from the old (renamed) column to the new column, and then validate the data to check for loss?
It's an easy operation to do, but it will take up some extra disk space.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 10:47 am
Nvarchar can go up to 4,000 explicitly, or to "max" (as long as you're on SQL 2005 or later), which allows for a LOT of characters.
So long as your varchar fields aren't more than 4,000, you're fine. If they go longer than that, you'll need to use nvarchar(max) to hold the data.
Varchar and nvarchar allow for implicit conversion, so it should just be a question of will it fit.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 18, 2010 at 10:50 am
As an addendum, don't use SSMS's GUI to redesign tables. It often has to create a temp table with the new structure, insert all the data into that, drop the existing table, and then insert from the temp table into that. And it has to log all of that, so it can roll it back if the server goes down or whatever in the middle.
That's no big deal on a table with a few hundred rows, but on anything with any real substance to it (and wide, many-row table are exactly what I mean by that), it can take a LONG time.
A simple Alter Table command, on the other hand, will usually take MUCH less time, and require a lot less work and a lot less I/O.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply