Solved

SQL Server 2005 - Converting a BIT column to TINYINT - and pitfalls?

Posted on 2011-03-21
3
1,568 Views
Last Modified: 2012-06-27
I have a table in production that needss to be altered, i.e., to change a BIT field to TINYINT.  
The table has existing data, default value for the field is 0.

Are there any pitfalls that I may need to watch out for?
Please advise.

Thanks,
D
0
Comment
Question by:dteshome
3 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35185323
As the column is deined as BIT, you will be having 0/1 values only with a default value as 0. So before altering the column data type, you need to drop the default constraint on the column and then change the data type.
You can add the default constraint once you change the data type. Basically you need this sql.
declare @sql varchar(500)
select @sql = 'alter table [' + t.name +'] drop constraint [' + ds.name + ']'
  from sys.default_constraints ds 
  join sys.tables t on ds.parent_object_id = t.object_id and t.name = 'Tab1'
  join sys.columns c on t.object_id = c.object_id and ds.object_id = c.default_object_id and c.name = 'BitCol'
exec (@sql)
alter table Tab1 alter column BitCol tinyint
alter table Tab1 add constraint df_Tab1_BitCol default 0 for BitCol

Open in new window

Example code.
create table Tab1(Id int,BitCol bit default 0)
insert Tab1 values (1,0),(2,1),(3,0),(4,1)
insert Tab1(Id) values (5),(6)
select * from Tab1
declare @sql varchar(500)
select @sql = 'alter table [' + t.name +'] drop constraint [' + ds.name + ']'
  from sys.default_constraints ds 
  join sys.tables t on ds.parent_object_id = t.object_id and t.name = 'Tab1'
  join sys.columns c on t.object_id = c.object_id and ds.object_id = c.default_object_id and c.name = 'BitCol'
exec (@sql)
alter table Tab1 alter column BitCol tinyint
alter table Tab1 add constraint df_Tab1_BitCol default 0 for BitCol
insert Tab1(Id) values (7) 
select * from Tab1
drop table Tab1

Open in new window

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 35187065
Regardless of how to change it (which already example by Sharath_123)
the different between BIT and TINYINT is the size to store.

TINYINT datatype, every fields will take 1 byte
BIT datatype,  if you have bit  1-8 fields, it will take 1 byte, 9-16 fields will take 2 bytes etc.

beside that, I don't think will have any trouble underlying for change BIT to TINYINT.
0
 

Author Comment

by:dteshome
ID: 35192042
Thank you, Sharath_123 and JoeNuvo. Your input has confirmed what I thought, that it is safe to do the type change.

D
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…
This is an introductory video for CloudBerry Managed Backup. You will learn how to sign up with the service and get started in a few minutes.

911 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