Today one of my friend called me up early in the morning from office. He was having some difficulty updating a particular column in a table in Prod. Everything looked fine except when he checked the data, modifications were not there. He updated the columns again but faced same issue. He called me up (I was sleeping but had to wake up because of the urgency in his voice).
He did a screen share with me. I told him to run all the modification inside a Transaction and he did the same thing. I will create a dummy scenario here :
— I have a created a Table called Test.
— Inside Transaction I am modifying the Flag Column
Great..I think we are all set then. Wait …. Lets check the table again..
SELECT * FROM test
Then I checked the data type of the Flag Column and found that it was “Bit“. I understood the problem. Because Bit data type can only have Boolean value 0 and 1, so the modification was not working.
But I think SQL Server should handle this in a better way. It should raise some kind of an alert or error message. What do you think ??
Note: Converting to bit promotes any nonzero value to 1.