• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1945
  • Last Modified:

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

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
dteshome
Asked:
dteshome
1 Solution
 
SharathData EngineerCommented:
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
 
JoeNuvoCommented:
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
 
dteshomeAuthor Commented:
Thank you, Sharath_123 and JoeNuvo. Your input has confirmed what I thought, that it is safe to do the type change.

D
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now