[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Which one is best approach to alter a datatype?

A table tAudit contains Millions of Millions of records. Now I would like to change a dataype of of a column int to bit. Which one is faster whether Alter column option or

Rename the column
Create a new column with bit datatype with existing name.
Update the new column with old column value
Drop the old column.

Please suggest.
0
Easwaran Paramasivam
Asked:
Easwaran Paramasivam
3 Solutions
 
Om PrakashCommented:
please check this article
0
 
Bhavesh ShahLead AnalysistCommented:
IF COLUMN Is not having foreign key relation or default then

ALTER TABLE <<TABLENAME>>
ALTER COLUMN <<COLUMNNAME>> NEW DATATYPE
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
The advantage of creating a new column updating then dropping is that you can step back not really the speed.  I do it this way just in case something fails in the process.
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Brian CroweDatabase AdministratorCommented:
I would definitely use the ALTER TABLE method but I would run something like

SELECT CAST(myColumn as bit) FROM tAudit

to check for any conversion errors.
0
 
Scott PletcherSenior DBACommented:
First, you need to correct any existing values that will not fit into a bit column:


SELECT *
FROM dbo.tAudit
WHERE
    NIOT ( column_int IS NULL OR column_int = 0 OR column_int = 1 )


After that's insured, it's just a straightforward, simple ALTER TABLE ... ALTER COLUMN statement.  It will have to update every page, but since the page will get smaller, I wouldn't expect any page splits to occur because of the change.
0
 
Anthony PerkinsCommented:
One advantage of adding a new column is that you can update the data in batches and this way prevent your Transaction Log from blowing up.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now