April 3, 2014 at 11:51 am
Can I have something like this:
ALTER TABLE t1
ALTER COLUMN c1 INT
ALTER COLUMN c2 varchar(10)
What is right syntax?
Thanks
April 3, 2014 at 12:16 pm
Hi
No, you cannot go your way - according to http://msdn.microsoft.com/en-us/library/ms190273.aspx
You can go with the ADDs and DROPs of columns since there is [ ,...n ] in the syntax
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
April 3, 2014 at 12:21 pm
The proper syntax is quite simple.
ALTER TABLE t1
ALTER COLUMN c1 INT;
ALTER TABLE t1
ALTER COLUMN c2 varchar(10);
_______________________________________________________________
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/
April 3, 2014 at 12:28 pm
Sean Lange (4/3/2014)
The proper syntax is quite simple.
ALTER TABLE t1
ALTER COLUMN c1 INT;
ALTER TABLE t1
ALTER COLUMN c2 varchar(10);
I am just doing this way - separate ALTER statements, but my table is huge, > 250 mln records, and it takes significant amount of time for each ALTER COLUMN. I hoped that if to combine them into a common ALTER TABLE would make things faster.
April 3, 2014 at 12:52 pm
SQL Guy 1 (4/3/2014)
Sean Lange (4/3/2014)
The proper syntax is quite simple.
ALTER TABLE t1
ALTER COLUMN c1 INT;
ALTER TABLE t1
ALTER COLUMN c2 varchar(10);
I am just doing this way - separate ALTER statements, but my table is huge, > 250 mln records, and it takes significant amount of time for each ALTER COLUMN. I hoped that if to combine them into a common ALTER TABLE would make things faster.
Well even if the syntax would support it this would not make it faster. In fact, it would have the opposite effect because there would twice as much data to log. The engine still has to roll through your table rbar and convert every single row to the new datatype. It has to log all of this so it is able to rollback when it encounters an issue at row 249 million.
How many columns do you have to alter?
_______________________________________________________________
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/
April 3, 2014 at 1:07 pm
How many columns do you have to alter?
Totally 9 columns:
7 : Nvarchar --> varchar
2 : text --> varchar
April 3, 2014 at 1:22 pm
SQL Guy 1 (4/3/2014)
How many columns do you have to alter?
Totally 9 columns:
7 : Nvarchar --> varchar
2 : text --> varchar
Ouch. Not sure what your table looks like but maybe you could create a table to hold all the info in the new datatypes. Then you could batch insert into this table. Finally renaming the current table to something else and renaming your new copy as the original table name. Not sure if you have the disc space for that as with that many rows it could take a bit of space.
How long does it take on your existing table to change a column?
_______________________________________________________________
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/
April 3, 2014 at 1:27 pm
Sean Lange (4/3/2014)
SQL Guy 1 (4/3/2014)
How many columns do you have to alter?
Totally 9 columns:
7 : Nvarchar --> varchar
2 : text --> varchar
Ouch. Not sure what your table looks like but maybe you could create a table to hold all the info in the new datatypes. Then you could batch insert into this table. Finally renaming the current table to something else and renaming your new copy as the original table name. Not sure if you have the disc space for that as with that many rows it could take a bit of space.
I already ran this version. It took close to 13 hours.
How long does it take on your existing table to change a column?
Average 45 min for Nvarchar - varchar fro eacg column.
For text - varchar : seconds
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply