Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-21
3
Medium Priority
?
1,854 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 41

Accepted Solution

by:
Sharath earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

916 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