Change column type bit to char 2

[micang]

SQL 2005

Hi All,

I have inherited a table i SQL 2005. One of the columns is a "bit" type and has 1 or 0 as the data. I would like to change this to a char (1) as I need to add 2 more variants (I need to show,0 or 1 or 2 or 3) in the column.

Is it possible to do this or would I have to create a new column? The table has 45 million rows.

Any info apprecited.

Michael

 

[gmmastros]

I would suggest you change the data type to tinyint or int.

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] #Temp (Data [COLOR=blue]Bit[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] #Temp [COLOR=blue]Values[/color](1)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] #Temp [COLOR=blue]Values[/color](0)

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] #Temp

[COLOR=blue]Alter[/color] [COLOR=blue]Table[/color] #Temp [COLOR=blue]Alter[/color] [COLOR=blue]Column[/color] Data [COLOR=blue]TinyInt[/color]

[COLOR=blue]Select[/color] * [COLOR=blue]From[/color] #Temp

[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] #Temp

-George

"the screen with the little boxes in the window." - Moron

 

[SQLDenis]

alter table TableName alter column ColumnName char(1)


example

create table testBla (id bit)
go
insert testBla values(1)

select * from testBla
go
alter table testBla alter column id char(1)
go
insert testBla values(3)


select * from  testBla 

drop table testBla

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[micang]

Thanks! Double confirmation!

Michael

 

[micang]

Apologies, one more thing:

I have a fear of starting this on a 45 million rows and am afraid it will "hang".

I have tested it on a 50,000 row table, and it took seconds, but will it be OK on 45 million?

Reason I have this fear is that I have had little success in updating large tables and would like to not encounter the same thing here!

PS - Is there an easy way of updating columns in batches?

Many thanks

Michael

 

[SQLDenis]

>>PS - Is there an easy way of updating columns in batches?

no because you are changing a datatype

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[micang]

Thank you, I ran the query took 24 minutes.

Appreciate your help.

Michael