Solved

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

Posted on 2011-03-21
3
1,778 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 41

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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