September 25, 2009 at 11:45 am
Hi. Quick question, I'm looking to migrate our product to SQL Server 2008. I want to take advantage of BIGINT. As I'm migrating the db files to 2008 is there a way to avoid having to do some of the manual work associated with migrating to bigint? I.e. can I create the table structure and then bring in just the data? As I understand I would need to:
* Restore the 2005 DB to 2008
* Either BCP the data out, modify the column, BCP back in. OR... Create a copy table and insert back in.
Is BCP the only option here??? Some of our tables will be close to 1 billion rows so I'm trying to come up with the quickest way to do this...
Many thanks.
September 25, 2009 at 12:24 pm
Alter the table, add the column as nullable, copy the data over, drop the other column at a later date. That should minimize downtime on the system.
Otherwise, you've outlined the solution. I prefer the mechanism of creating a copy of the table only because it minimizes downtime & locking during the migration, but then you've got to have enough space to duplicate the table you're moving.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 25, 2009 at 12:39 pm
Thanks Grant. Do you know if SQL does an implicit conversion (or has an impact on the optimizer) if the column in the where clause is a bigint but the variable is an int, i.e.
select * from my_table where my_big_int_col = @my_int_var
I'm wondering if I have to go big bang and fix all of the underlying SQL also to leverage this data type.
Thanks for your help
September 25, 2009 at 1:01 pm
Yeah, it'll convert an int parameter to a bigint... but, what happens the first time you pass a bigint to the int parameter? You're going to have to update those params as well, eventually.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply