December 12, 2012 at 7:27 am
Good Morning.
Hoping someone can give me some insight as to the best approach for changing char fields to varchar/nvarchar fields in a large table.
Currently we have an old SQL 2000 DB that we will be migrating over to 2008 R2. This DB was created almost 10 years ago and is approximately 170GB.
Alot of that size is lost space as the original design had all text fields defined as char fields even though many of them do not use the full length. I'm looking at converting all of these fields to regain all the lost white space.
My question:
What is the best approach to do this?
1) Convert the fields in the table
2) Create a new table with properly defined field types and insert the data into it (trimming all spaces)
3) Something else I don't know about? 🙂
Any and all suggestions would be greatly appreciated.
Tar
December 12, 2012 at 8:04 am
I think you've got the right ideas. I'd create the table on the new server, based on information you can gather from your existing table, such as selecting the MAX of the LEN of the RTRIM of each of the char fields, so you'll know what the largest field sizes are. When you copy the table over, the query will need to RTRIM each of the char fields. However long that takes, is however long it takes, although, if you wanted to spend the disk space on the older system, you could create the new values in a separate table that has an identical primary key, and at migration time, those values go instead of the ones from the old table, but that could give you maintenance headaches. Alternatively, you could create a computed field for each of the text fields, but that would impact the existing table in a large way with respect to size, plus there's the potential that any column changes to the original table could cause problems for existing views or stored procs, so it might not be worth the headache.
Good luck !
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 8:06 am
Not sure why you want to create a new table and copy all this data into it, especially if you the point of this is to save space. Alter the columns to varchar, then update the table.
alter table MyTable
alter column MyCharColumn varchar(50)
update MyTable
set MyCharColumn = rtrim(MyCharColumn)
Of course, do this in a test db first but I don't see the need to create a second copy of all this data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2012 at 8:54 am
Sean,
They're migrating to a new SQL instance, so there's going to be a new copy anyway. They can do the RTRIM on the way over, so to speak. It's mostly a matter of how long will the copy take across the network. If they use a PULL, by using SELECT FROM OPENQUERY (running on the new server), the old server can do the RTRIM and only send the remaining characters across the network, which would probably save quite a bit of time, if the claim that there's a lot of empty space is indeed true.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 9:14 am
There is definitely alot of blank space. I'm very familiar with the data 🙂
That being said, something I failed to mention is I'm talking about the conversion of 25+ fields. This is why I had considered using a new table and inserting the data into that table, as running the convert function across 200 million+ rows for 25+ fields would probably require 6 weeks of processing time and 10TB of disk space. 🙂
Tar
December 12, 2012 at 9:58 am
How many rows do you have? The function to do it is RTRIM. CONVERT isn't necessary. You create a Linked Server on the new instance that points to the old, and then run an INSERT query on the new instance, something like this:
INSERT INTO dbo.NEW_SERVER_TBL_NAME (F1, F2, ...)
SELECT *
FROM OPENQUERY('Linked_Server_Name',
'SELECT RTRIM(F1), RTRIM(F2), ...
FROM dbo.SOURCE_TABLE_NAME')
This way, for the fields that get RTRIM'ed, only the relevant characters traverse the network, as the query specified by OPENQUERY runs on the old server, and only the result set gets transmitted. This avoids any excess copies of data and I'm not sure there's a faster way.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 12, 2012 at 10:37 am
sgmunson (12/12/2012)
How many rows do you have? The function to do it is RTRIM. CONVERT isn't necessary. You create a Linked Server on the new instance that points to the old, and then run an INSERT query on the new instance, something like this:
INSERT INTO dbo.NEW_SERVER_TBL_NAME (F1, F2, ...)
SELECT *
FROM OPENQUERY('Linked_Server_Name',
'SELECT RTRIM(F1), RTRIM(F2), ...
FROM dbo.SOURCE_TABLE_NAME')
This way, for the fields that get RTRIM'ed, only the relevant characters traverse the network, as the query specified by OPENQUERY runs on the old server, and only the result set gets transmitted. This avoids any excess copies of data and I'm not sure there's a faster way.
I agree. I was commenting on Sean's suggestion of using a convert function instead of an insert into a new table.
Tar
December 12, 2012 at 10:58 am
Taragor (12/12/2012)
I agree. I was commenting on Sean's suggestion of using a convert function instead of an insert into a new table.
Tar
I merely suggested changing the existing table and then an update. That might be faster than doing the trim on the fly. It is hard to say but I would try both ways on a test box and see. You have a lot of data and it is going to take some time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 12, 2012 at 12:16 pm
Sean Lange (12/12/2012)
Taragor (12/12/2012)
I agree. I was commenting on Sean's suggestion of using a convert function instead of an insert into a new table.
Tar
I merely suggested changing the existing table and then an update. That might be faster than doing the trim on the fly. It is hard to say but I would try both ways on a test box and see. You have a lot of data and it is going to take some time.
There may be implications to changing the existing column, and it could potentially complicate recovery if the original has been changed and then needs to be changed back because something went wrong on the new box that is an un-related item but is a show-stopper for the conversion window. That was why I suggested using the Linked Server and isolating the changes to the new environment. That way, if you have to go back, you haven't mucked up what you already have, and you get a clean new environment. The other way has potential for some serious fragmentation to occur within the database file, depending on how much free space they have. Since the RTRIM has to happen either way, why not have it happen in concert with the transfer (and before it hits the network cable, of course) ? I can't see how it could possibly go faster to do the same thing and then separately do the transfer... Let me know if I'm missing something...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply