Solved

Which one is best approach to alter a datatype?

Posted on 2013-05-30
6
273 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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:
Scott Pletcher 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

820 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