Boolean datatypes

Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd

Boolean datatypes

Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kd
kd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd

Boolean datatypes

Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can cho
ose whatever you want to
represent what you want to call "true" and "false", these will only be value
s of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and b
ind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). Ther
e has been several
discussions ion this group and .programming on the subject and I recommend t
hat you check the
archives and determine whether you want to use bit or, for example, char(1)
with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other opt
ions exists).
(*) One strange thing with the bit datatype in SQL Server is that you will n
ot get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be
1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd

Bitwise operator question

What is the biggest values that SQL Server supports for bitwise AND and OR.
The BOL says any of the integer datatypes. But in the examples, it only r
eferences int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some confirmat
ion from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBASure:
SELECT CONVERT(BIGINT, 35184372088833) & 1
Note: You do need to explicitly convert it to BIGINT or else SQL Server wil
l try to implicitly convert it to NUMERIC, which does not support bitwise co
mparisons.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rick Sawtell" <quickening@.msn.com> wrote in message news:eBlLVTM%23EHA.1396
@.tk2msftngp13.phx.gbl...
What is the biggest values that SQL Server supports for bitwise AND and OR.
The BOL says any of the integer datatypes. But in the examples, it only r
eferences int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some confirmat
ion from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBA|||Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to hold
data in a strongly-typed database though, so you shouldn't need bitwise
operators very often.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3772EF4-A53D-4740-9835-642A6BBF767F@.microsoft.com...
> Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to
> hold
> data in a strongly-typed database though, so you shouldn't need bitwise
> operators very often.
> --
> David Portas
> SQL Server MVP
> --
>
How would you suggest tracking 16000 different options.
Rick|||"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23svAjAO%23EHA.1524@.TK2MSFTNGP09.phx.gbl...
> How would you suggest tracking 16000 different options.
Certainly not in a 16000-bit bitmask... Assuming those options were for
users, I would do something like this:
CREATE TABLE Options
(
OptionID INT PRIMARY KEY,
OptionName VARCHAR(50),
..
)
CREATE TABLE UserOptions
(
OptionID INT,
UserID INT,
CONSTRAINT FK_UserOptions_Options FOREIGN KEY (OptionID) REFERENCES
Options (OptionID),
CONSTRAINT FK_UserOptions_Users FOREIGN KEY (UserID) REFERENCES
Users (UserID),
CONSTRAINT PK_UserOptions PRIMARY KEY (OptionID, UserID)
)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||In a table, with atomic columns representing real attributes. How else? :-)
If you have that many elements to track then I would say bitmaps are
definitely no-go. Bitmaps don't benefit from optimization on individual
attributes and every query will force a table-scan with the bitwise
operations performed on every row.
David Portas
SQL Server MVP
--

Bitwise operator question

What is the biggest values that SQL Server supports for bitwise AND and OR.
The BOL says any of the integer datatypes. But in the examples, it only references int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some confirmation from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBA
Sure:
SELECT CONVERT(BIGINT, 35184372088833) & 1
Note: You do need to explicitly convert it to BIGINT or else SQL Server will try to implicitly convert it to NUMERIC, which does not support bitwise comparisons.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Rick Sawtell" <quickening@.msn.com> wrote in message news:eBlLVTM%23EHA.1396@.tk2msftngp13.phx.gbl...
What is the biggest values that SQL Server supports for bitwise AND and OR.
The BOL says any of the integer datatypes. But in the examples, it only references int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some confirmation from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBA
|||Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to hold
data in a strongly-typed database though, so you shouldn't need bitwise
operators very often.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3772EF4-A53D-4740-9835-642A6BBF767F@.microsoft.com...
> Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to
> hold
> data in a strongly-typed database though, so you shouldn't need bitwise
> operators very often.
> --
> David Portas
> SQL Server MVP
> --
>
How would you suggest tracking 16000 different options.
Rick
|||"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23svAjAO%23EHA.1524@.TK2MSFTNGP09.phx.gbl...
> How would you suggest tracking 16000 different options.
Certainly not in a 16000-bit bitmask... Assuming those options were for
users, I would do something like this:
CREATE TABLE Options
(
OptionID INT PRIMARY KEY,
OptionName VARCHAR(50),
...
)
CREATE TABLE UserOptions
(
OptionID INT,
UserID INT,
CONSTRAINT FK_UserOptions_Options FOREIGN KEY (OptionID) REFERENCES
Options (OptionID),
CONSTRAINT FK_UserOptions_Users FOREIGN KEY (UserID) REFERENCES
Users (UserID),
CONSTRAINT PK_UserOptions PRIMARY KEY (OptionID, UserID)
)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||In a table, with atomic columns representing real attributes. How else? :-)
If you have that many elements to track then I would say bitmaps are
definitely no-go. Bitmaps don't benefit from optimization on individual
attributes and every query will force a table-scan with the bitwise
operations performed on every row.
David Portas
SQL Server MVP

Bit datatypes physical storage

Can anyone explain to me how a column defined with a "bit null" datatype is physically stored in MSSQL? Is it stored like a "tinyint null" physically? In other words, how many bytes on the row on the page does a "bit null" datatype consume (assuming a non-null value 0, or 1 is the current value).

Is there any good documentation about the physical storage layout for a data page?

Thanks -From SQL BOL: Microsoft SQL Server optimizes the storage used for bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on.A Null column does not consume any storage. SQL Server stores Nullable columns only if they exist. That's good unless they change frequently, then it can be bad.

In general, avoid Nulls as much as you possibly can for two reasons: Data Integrity
Reduced Page Splits|||i won't question point 2 because it seems to be another instance of DBA arcana (with all of which i am not familiar), but point 1 cannot go unchallenged

are you suggesting 6th normal form? because i have a real problem with that

and if not, then what are you suggesting? zeros instead of nulls in numeric columns, and empty strings in character columns? how does this improve data integrity? if anything, this decreases data integrity

or were you specifically referring to null bit fields only? because that wasn't clear (and doesn't make a lot of sense either)|||Ok, if I have 2 bit fields which are then stored in the same byte, and the first field is null, there must be a way that MSSQL knows that without thinking that the first bit of that byte which stores the bit fields is the first bit column value. I'm thinking that there is an offset table on the row, or some kind of null "indicator". Which is it? An offset table or an indicator?

If an offset table, then how are bit fields represented in it? If an indicator, then whoa, I'm lost on why I would choose such a datatype.|||Thanks for the challenges, r937. I'm not prone to making sense. My point is if a value is expected, then don't allow Nulls.

How often do you see DDL posted and all columns but the primary key are Nullable (just be happy there's a primary key, right?). I see it too often.

I like the aracana misconception - no secrets here as I'm a recovering pragmatist. However, I quesiton my ability to explain it succinctly and I'm burnt out on searching MSN for links that do the job for me.

But, I'll try for posterity.

Page Splits occur when SQL Server has to reorganize data on a page and Page Splits are one of the most expensive operations that SQL Server has to do. Updating a value from Null to something that Is Not Null may cause a Page Split on a page that is > 50% full (I'll leave the research up to you for determing the conditions under which SQL Server splits a page).

For a heavy OLTP system, it gets expensive. Thus, if you force users to enter values that are needed, you reduce the probability they'll have to go back and add the values later, thus reducing the probability of a Page Split.
Finally, my working mode is enterprise applications. So, I answer questions with the assumption the answer should apply to an enterprise architecture. I'm often amused how my assumptions lead to useless typing, but hey, I'll keep trying.

Which is it? An offset table or an indicator? Offset. Which led Microsoft to disallow Null bits in the beginning. They succumbed to pressure. Your seeking internals knowledge, and Microsoft has been relaxing their rules around providing it, but I'm not sure they've gone into any depth regarding offsets. I'd tell you more but I'd have to shoot you.

Hope this helps. I gotta Split.|||My point is if a value is expected, then don't allow Nulls.well, sure, if one knows what one's doing (although i would change the word "expected" to "required")

but that's a lot different than "avoid Nulls as much as you possibly can"

the latter advice will cause some people to use DEFAULT 0 and DEFAULT '' needlessly, wantonly, and, in most cases, flat out incorrectly

as far as all columns but PK being nullable, i myself like that idea :)

it allows the referential integrity action "ON INSERT CASCADE [to parent]" (yes, i made that up) whereby if you try to insert a child row with a foreign key for a parent that doesn't exist yet, it takes the foreign key value and uses it to insert a row with that value of the primary key in the parent table (so all other parent columns are therefore assigned NULL or get their defaults) before inserting the child row

would save writing a trigger

and don't say you haven't seen an application where this is a valid business rule ;)|||I was gonna leave this alone, but then I'm a pedant.

The world in which I live requires parents to exist for a child to exist. Now, that could just be my world and with the advent of cloning, it may quickly be invalidated and I'll have to move to a different world.

If you do the math on your interesting CASCADE [to parent] you will find that the parent insert should require knowing all the parent's attributes to do the insert. My interpretation of your proposition is that parent attributes are irrelevant. They're relevant in my world.

Where ya gonna put 'em and how ya gonna get them if you just ignored them?

I have not seen a valid business for which this type of an application would rule. :p|||Avoid nulls as much as you can?

Bullcrap! The value NULL provides information that simply cannot be provided by a default value, that being that absence of information. If a value is unknown, then the last thing I would want would be to have a value place their by default.

I'd classify that as bad data in a heartbeat.

Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."

There is nothing wrong with a table schema that allows many columns to be null. Are you going to prevent a user from entering a record with incomplete information just to satisfy the obsesive compulsions of an overly anal-retentive dba?

Ultimately, the business rules drive the design. If they do not require a value, then allow NULL.|||Your statement that "if a value is expected, then don't allow Nulls" is closer to reality, but the best rule is simply "if a value is REQUIRED, then don't allow Nulls."Who's being anal-retentive?

Expecting values implies they are required. Saying as much as you can implies you are competent enough to know when you can't. I'll switch to explicit mode to avoid illogical conclusions.

I didn't say completely avoid Nulls, I said avoid Nulls as much as you can.

I agree - if a value is not required, then allow Null. But, avoid Nulls as much as you can.

I didn't say ignore them. I didn't say they don't mean anything even though their meaning is unknown. I said avoid Nulls as much as you can.

I didn't say mislead others with default values. I said avoid Nulls as much as you can.

If a value is expected, then don't allow Nulls. That is reality.

That's my story and I'm sticking to it.

Avoid Nulls as much as you can.|||Man, I love this. MaxA, - you rule!!!!

But looking back at my own designs, - I can tell thatt the better I understood the business requirements, the more complex but at the same time cleaner the final design looked like. And ironicly I did notice that fewer nulls were present. Offloading NULLable columns to a separate table always leads to more complex queries, but a LEFT OUTER JOIN is a LEFT OUTER JOIN, and if PKs/FKs are properly indexed, - you'll see a better performance as well...But that's just me ;)

EDITED: By better performance I mean that your table will naturally be theoretically wider when nullable columns are present, and at the time of optimizing your SELECT you have 2 choices: avoid selecting nullable columns, or include them into existing indexes to avoid bookmark lookups. But if you offload those columns into a separate table that will only contain records where the column is not null, it'll result in fewer records to go through and all indexes are used.|||Avoid NULLS as much as you can? That is just a tautology. If you are saying not to allow NULLS when a value is required, then I completely agree with you.

How about this statement: "Avoid making a field required as much as you can."

I didn't say completely avoid Non-Nulls, I said avoid Non-Nulls as much as you can.

I agree - if a value is required, then disallow Null. But, avoid Non-Nulls as much as you can.

I didn't say ignore Non-Nulls. I didn't say they should be used even when a value is unknown. I said avoid Non-Nulls as much as you can.

If a value is not required, then allow Non-Nulls. That is reality.

That's my story and I'm sticking to it.

Avoid Non-Nulls as much as you can.|||Oh that's just plagiarism, Mr. Lindman, come up with your own style! ;)|||Plagiarism IS a style. Just look at Hollywood or the Grammy Awards! ;)

"Plagiarism is the sincerest form of flattery".
I forget who originally said that...|||Apparently Charles Caleb Colton:

http://www.quotationspage.com/quote/27484.html

I just don't think he has ever gotten enough credit for his work, do you?