September 22, 2009 at 5:34 pm
I was going to submit this as a QOTD, however our browsers are totally locked down here, so ...
I have a problem (well, one of many), where I can never remember whether true is 0,1 or -1 (probably because I work in a few different languages all the time), so I wrote the following and totally expected this to error out when it came to the 3rd "Set" statement.
DECLARE @true BIT
SET @true = 0
IF @true = 'True'
PRINT 'True = 0'
ELSE
PRINT 'False = 0'
SET @true = 1
IF @true = 'True'
PRINT 'True = 1'
ELSE
PRINT 'False = 1'
-- This is where I expected it to die - 66 should never fit into a bit field
SET @true = 66
IF @true = 'True'
PRINT 'True = nonzero'
ELSE
PRINT 'False = nonzero'
September 22, 2009 at 6:28 pm
I guess it means anything other than 0 is true.. I tried -1 and got true as well.
CEWII
September 22, 2009 at 6:46 pm
Yes - I also got that as well, however neither -1 or 66 shoudl fit into a bit field, which *should* be only zero or one. Interesting...
September 23, 2009 at 2:02 am
in C, if (variable) will process the true statement if variable is anything other than 0. So SQL is just consistent with that in this respect.
Typically -1 is true and 0 is false because those two numbers are a complete 'bit flip' of any size of integer in a two's complement based system (i.e. 99.9% of computers in the world).
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2009 at 4:31 am
Technically, a bit value of 1 is true as if you print the variable, you'll see 66 has implicitly converted to 1.
Good idea for a QOTD, I wouldn't have guessed that any numeric value != 0 implicitly converts to a bit value of 1
September 23, 2009 at 8:03 am
HowardW (9/23/2009)
Technically, a bit value of 1 is true as if you print the variable, you'll see 66 has implicitly converted to 1.Good idea for a QOTD, I wouldn't have guessed that any numeric value != 0 implicitly converts to a bit value of 1
Just to support Howard on this, there are two types of conversions, implicit and explicit. There is a chart here provided in MSDN. Here we can see int to bit is an implicit conversion. Thanks.
http://msdn.microsoft.com/en-us/library/ms187928.aspx
---------------------------------------------------------------------------------
September 23, 2009 at 4:39 pm
Matt - bit fields are either 1 or 0, not -1 - that's all they can be, as there is only one "bit" to hold the value and binary is either 1 or 0.
Anyway, after finding this little quirk yesterday, I had a bit more of a look at it, and found a bit of an inconsistency in the way SQL Server handles truncations.
As we're all aware, if you create a column as varchar(3), and attempt to stuff > 3 characters into it, you get a truncation error and sql server refuses to insert the row (this is a good thing!). Where the inconsistency comes in, is when you have a variable of the same type and length and you try to assign a larger value, you do not get the same truncation error - it just automatically truncates or implicitly converts as you will see below if you run the code below.
DECLARE @var varchar(3)
, @bit bit
SET @var = '123'
SET @bit = 0
PRINT @var
PRINT convert(varchar,@bit)
CREATE TABLE #tmp (field1 varchar(3), field2 bit, field3 char(3), [id] int not null identity)
-- Next line should insert without any issues
INSERT INTO #tmp (field1, field2, field3) values (@var, @bit, @var)
-- Next line produces a truncation error
INSERT INTO #tmp (field1, field2, field3) values ('Testing,1,2,3',@bit, @var) -- field1 truncates, row not inserted, error
INSERT INTO #tmp (field1, field2, field3) values (@var,@bit,'Testing,1,2,3' ) -- field3 truncates, row not inserted, error
SET @var = 'Testing,1,2,3' -- why doesn't this assignment do the same?
INSERT INTO #tmp (field1, field2, field3) values (@var,@bit,@var) -- variable truncates value, row inserts ok, but why no warning?
-- As we know, this is implicitly converted to 1, but why don't we get a warning?
SET @bit = 66
INSERT INTO #tmp (field1, field2,field3) values (@var,66,@var) -- row inserts OK, but why no warning of the implicit conversion/truncation?
INSERT INTO #tmp (field1, field2,field3) values (@var,@bit,@var) -- row inserts ok
SELECT * FROM #tmp
DROP TABLE #tmp
September 23, 2009 at 5:11 pm
niall.baird (9/23/2009)
Matt - bit fields are either 1 or 0, not -1 - that's all they can be, as there is only one "bit" to hold the value and binary is either 1 or 0.
Perhaps I wasn't clear - I was talking about in C, just stating that the implicit conversion that takes place is entirely consistent with the way C handles it. In computing, in general, true is -1 because that is the number you get if you fill any length of two's complement integer with 1s. I.e. true is a complete bit flip of false, whatever the length of the integer.
For more info: http://en.wikipedia.org/wiki/Two's_complement
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2009 at 5:45 pm
Matt - thanks for that. -1 is still more than a bit field, as it takes 8 bits (I think) to store it, whereas 0 & 1 only need a single bit. We're talking about two different things though.
What really does concern me a little though, is that certain fields give either a warning or an error when they are truncating, but truncating a value to stuff it into a variable doesn't. This is inconsistent - especially as you get (in vb6 days anyway) an error 13 when you try to stuff a large value into a small variable...
September 23, 2009 at 6:01 pm
The 'silent truncation of varchar variables' issue is a well-known - I believe it was subject of a recent QotD?
The reason the bit field doesn't warn is because it's an implicit conversion, along the lines of C. In more type safe languages like C#, the compiler will throw a wobbly if you try anything like that - but under SQL it's just an implicit conversion. I think someone linked to the page about it earlier in the thread. 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 23, 2009 at 7:34 pm
niall.baird (9/23/2009)
Matt - thanks for that. -1 is still more than a bit field, as it takes 8 bits (I think) to store it, whereas 0 & 1 only need a single bit. We're talking about two different things though.What really does concern me a little though, is that certain fields give either a warning or an error when they are truncating, but truncating a value to stuff it into a variable doesn't. This is inconsistent - especially as you get (in vb6 days anyway) an error 13 when you try to stuff a large value into a small variable...
Actually - if you're storing SIGNED integers, a 1-bit-wide number can only store 2 values (represented in decimal): 0 and -1 (since the 1 bit is both the sign AND the value)
in UNSIGNED integer notation - a 1-bit number can be only 0 or 1 (decimal values)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2009 at 8:09 pm
Yes - but we are using BIT fields, not Int.
September 23, 2009 at 8:16 pm
niall.baird (9/23/2009)
Yes - but we are using BIT fields, not Int.
You're asking about the numerical value being stored in binary form in something that is 1-bit wide. Bit columns happen to store values with no fractional component (i.e integers), which could upon convention happen to represent 1 or -1.
For what it's worth, BOL tends to lean towards unsigned integer notation, so the official answer would be 1 (in Access - it would be -1, since the designers there decided they were using signed notation).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2009 at 8:22 pm
Actually, I'm asking why certain data types throw an error when you try to assign a larger value to it, and other times it doesn't - and why variables are treated differently to columns.
ie:
field1 varchar (3) - will error if you try to put 'FRED' into it, but if you assign 'FRED' to a varchar(3) variable, it doesn't complain, and then you will not get an error if you put that variable in an insert statement, however if you have a bit field (which should only be able to contain 0 or 1 in MS Sql Server 2K5) it implicitly converts any 'non-zero' int to 1.
September 23, 2009 at 8:22 pm
Sorry I never use bit, I use tinyint and name the field something ending in Flag, meaning it is an effective boolean, 1 is enabled, all other values are considered disabled.. It has worked well for me and I don't see any reason to change..
CEWII
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply