July 25, 2011

SQL Server Bit Data Type and how it Stores Values




SQL Server supports the bit datatype which can be used to store flag values like 'true' or 'false'. However it should be noted how the values are stored in the bit column. This article gives you an overview of the same.

Consider this example

declare @b1 bit,@b2 bit, @b3 bit
select @b1=1,@b2=0,@b3=null
select @b1 as bit1,@b2 as bit2,@b3 as bit3
GO


sql-server-bit4

The result shows 1,0 and null.

You can also use the string values 'true' and 'false' in place of 1 and 0.

declare @b1 bit,@b2 bit, @b3 bit
select @b1='true',@b2='false',@b3=null
select @b1 as bit1,@b2 as bit2,@b3 as bit3
GO


sql-server-bit5

The result shows 1,0 and null.

However other than the string values 'true' and 'false', the bit datatype will not accept any other string as shown below:

declare @b1 bit
select @b1='test'
select @b1


The above query will throw the following error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'test' to data type bit.


You can also assign any number to the bit value, but it will convert it to 1,0 or null. Remember that a bit datatype can only store these three values.

The following example shows that how numbers other than 0 or 1 are converted by the bit datatype

sql-server-bit3

OUTPUT
sql-server-bit6

As the result shows, any value which is not null or 0 will be converted to 1.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan

 
  Feedback:

comments

0 Responses to "SQL Server Bit Data Type and how it Stores Values"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions