Solved

Which one is best approach to alter a datatype?

Posted on 2013-05-30
6
265 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

23 Experts available now in Live!

Get 1:1 Help Now