Solved

Which one is best approach to alter a datatype?

Posted on 2013-05-30
6
275 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

635 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