Solved

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

Posted on 2011-03-21
3
1,618 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 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