I have some columns using a bit datatype. The end user now wants an N/A option so what before I was using a bit to capture "Yes/No" (1 being yes and 0 being no) I now need to have three options Yes/No/NA which I can store just as text in a varchar.
Is there a way to change the datatypes of the columns from bit to varchar AND also update the current records data where any 0's I change to "No" and 1's I change to "Yes"?
Why not just use NULL to mean "n/a"?
Else use TINYINT with a lookup table containing values 0, 1, and 2 (or perhaps 1, 2, and 3) and their meanings. Then you add a FK between the two tables to enforce that only those values get entered.
The last choice would be to go with a CHAR(1) column. This would be ok only if you also specified a binary Collation, such as Latin1_General_100_BIN2. The binary Collation will make it so that you don't lose performance over the other two options. Without the binary Collation, the string column will compare values against linguistic rules that take extra time yet have no meaning for this usage. You would also need an AFTER INSERT, UPDATE Trigger to first UPPER() this value for consistency, and then to enforce that all values are either Y, N, or A (for "n/a"). This is more readable than 0, 1, and 2 (via TINYINT) and just as efficient for searching, and also just 1 byte, BUT uses will need to remember to specify upper-case letters only, else they might not get the expected results.
There is no reason at all to use VARCHAR(3) and store the full values N/A / Yes / No, unless you just don't care about the system getting slower ;-).
Alter Table TableName
Alter Column ColumnName Varchar(3)
Update TableName
Set ColumnName = 'Yes'
Where ColumnName = '1'
Update TableName
Set ColumnName = 'No'
Where ColumnName = '0'
Actually char(3) would be more space efficient
or use tinyint with FK table as suggested by scutzly
I was dead was dead surprised but I can able to just convert bit to char(3) with right click design in SSMS.
