May 11, 2011 at 3:22 pm
I have a production move tomo. I have to drop 4 columns in a table. The table has 250036240 rows and 160 columns. I know its a bad design to have 160 columns in single sql server table. we are planning to break down the table into 3 in future release. The time to complete this activity is 30 mins.
Datatype of columns are char(1) for 2 columns and varchar(20) for 2 columns.
Will the drop be performed within 30 mins considering the columns and rows we have in the table?
Will the drop writes all the data (columns dropped) to log file?
Let me know your comments.
Thank you
Ganesh
May 16, 2011 at 1:28 pm
Coolganny (5/11/2011)
I have a production move tomo. I have to drop 4 columns in a table. The table has 250036240 rows and 160 columns. The time to complete this activity is 30 mins.Datatype of columns are char(1) for 2 columns and varchar(20) for 2 columns.
Will the drop be performed within 30 mins considering the columns and rows we have in the table?
Will the drop writes all the data (columns dropped) to log file?
Will the drop be performed within 30 mins considering the columns and rows we have in the table?
Keep you fingers crossed. I don't think so. Do you have a development environment that mimics your production environment?
If you perform a Dropped column in SQL Server 2008 it marks the columns as unused. If you subsequently perform an ALTER TABLE ... REBUILD then you are recreating the table.
In SQL Server 2005 it is:
ALTER TABLE tblName DROP COLUM columnName
ALTER TABLE ... DROP UNUSED COLUMNS
Marking the columns as unused is not that costly but when you rebuild the table you are going to take a hit so simple schedule it during a maintenance window after you have tested it in a non production environment.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 19, 2011 at 1:10 am
Coolganny (5/11/2011)
Will the drop be performed within 30 mins considering the columns and rows we have in the table?Will the drop writes all the data (columns dropped) to log file?
Let me know your comments.
If you are using UI to alter the table, use ALTER TABLE command. It works faster than UI.
Yes, all the data is written to log.
May 19, 2011 at 1:34 am
Welsh Corgi (5/16/2011)
ALTER TABLE ... DROP UNUSED COLUMNSMarking the columns as unused is not that costly but when you rebuild the table you are going to take a hit so simple schedule it during a maintenance window after you have tested it in a non production environment.
I thought 'DROP UNUSED COLUMNS' was an Oracle or suchlike command, could you check that as I'm struggling to find a reference in the MS documentation? It wouldn't be the first time I haven't looked properly.
BrainDonor.
May 19, 2011 at 9:09 am
You are correct SQL Server does not yet have 'DROP UNUSED COLUMNS'.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply