Solved

Which one is best approach to alter a datatype?

Posted on 2013-05-30
6
269 Views
Last Modified: 2013-06-20
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
Comment
Question by:Easwaran Paramasivam
6 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 39206876
please check this article
0
 
LVL 19

Assisted Solution

by:Bhavesh Shah
Bhavesh Shah earned 100 total points
ID: 39206879
IF COLUMN Is not having foreign key relation or default then

ALTER TABLE <<TABLENAME>>
ALTER COLUMN <<COLUMNNAME>> NEW DATATYPE
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 39206934
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39207869
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
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 150 total points
ID: 39209352
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 39210457
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now