How to store yes/no data? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to store yes/no data?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2006-08-10 : 16:11:47

In Access, there a yes/no data type. What data type should I select to store yes/no data in SQL table?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-10 : 16:17:14

Can use
type : tinyint
values 1 / 0

Srinika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-10 : 16:22:23

Use bit data type to store this data.

Tara Kizer

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-10 : 16:30:04

Agree with Tara, you should use a bit for this.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-11 : 07:08:44

Yes/no = bit
Yes/no/maybe/yesno/sometimes/notyesbutnotno = tinyint (this will give you 0-255)

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-11 : 07:33:41

Depends on the environment you work in.
Can sometimes be good to use Y/N.
I'm on a system at the moment where sometimes 1 means yes and sometimes no depending on where the data comes from. Could say the data is incorrect or the column is misnamed or they should have a lookup table.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

airjrdn
Starting Member

35 Posts

Posted - 2006-08-11 : 11:02:22

It depends on whether or not you'll ever need an index on the column. You can't put an index on a bit column, but you can the tinyint.

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-11 : 13:30:35

quote:

It depends on whether or not you'll ever need an index on the column. You can't put an index on a bit column, but you can the tinyint.

Why on earth would you ever want to waste space creating an index on a column that is either 'Y' or 'N'????

Ken

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-11 : 14:03:14

quote:
Originally posted by airjrdn

It depends on whether or not you'll ever need an index on the column. You can't put an index on a bit column, but you can the tinyint.

You do know that this type of index will never be used in the execution plan due to its selectivity, right? Selectivity of a column that has values of 1,0 or Y,N is so low that SQL Server will never use the index.

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-11 : 15:13:16

I prefer


USE Northwind
GO


CREATE TABLE myTable99(   pkKey int NOT NULL
			, Something_Ind char(1) NOT NULL DEFAULT('N')
			, CHECK(Something_Ind IN ('N','Y'))
)
GO

INSERT INTO myTable99(pkKey,Something_Ind)
SELECT 1, 'Y'
GO

INSERT INTO myTable99(pkKey,Something_Ind)
SELECT 2, 'N'
GO

INSERT INTO myTable99(pkKey,Something_Ind)
SELECT 3, 'X'
GO

SELECT * FROM myTable99
GO

DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

airjrdn
Starting Member

35 Posts

Posted - 2006-08-11 : 15:47:23

quote:
Originally posted by tkizer

quote:
Originally posted by airjrdn

It depends on whether or not you'll ever need an index on the column. You can't put an index on a bit column, but you can the tinyint.

You do know that this type of index will never be used in the execution plan due to its selectivity, right? Selectivity of a column that has values of 1,0 or Y,N is so low that SQL Server will never use the index.

Tara Kizer

Yes, but I hadn't thought of that before posting. Thanks for catching it.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-11 : 16:25:05

quote:
Originally posted by airjrdn

It depends on whether or not you'll ever need an index on the column. You can't put an index on a bit column, but you can the tinyint.

It is a common misconception that you cannot create an index on a bit column. You just can't do it with Enterprise Manager.

Don't believe me? Try this code.


use tempdb
go
create table xx_test ( MyBit bit not null )
go
create index xx_test_MyBit on xx_test ( MyBit )
go
drop table xx_test

I'm not saying it's a good idea, but it is not impossible to do.

CODO ERGO SUM

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-12 : 07:28:12

quote:
Originally posted by tkizer

You do know that this type of index will never be used in the execution plan due to its selectivity, right? Selectivity of a column that has values of 1,0 or Y,N is so low that SQL Server will never use the index.

Tara Kizer

Another common misconception.
Nothing wrong with putting an index on a bit column.
Sometimes it is the only way to run a system.

Bit similar to the statement "You should never put a clustered index on an identity".
Both statements are very wrong in some systems.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-12 : 10:51:19

i thought it's great to put a clustered index on identity, isn't it?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-12 : 11:31:47

Depends on the system.
It used to be in bol that it was a bad idea so was a good interview question to see whether people understood what a clustered index did.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-12 : 13:48:55

an index on a Y/N column (or any low cardinality column) is only valuable in sql server if the data distribution is not even. if you have a 50/50 mix or 60/40 or whatever the index will never be used.

if you have a distribution that is 99 % yes and 1% no and your query is looking for the 'no' values, then the index might get used - depending on the number of rows in the table, etc.

Oracle has a solution to this problem because they have a different index type called a bitmap index. It is effective even if the distribution of data is even. SQL Server doesn't have this capability though. The bitmap index is what would be used in this kind of situation, although it has a lot more overhead to maintain in comparison to the standard b-tree indexes.

-ec

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-12 : 14:25:03

Not quite. If you want to count the number of values or do an existance check then the index would be useful as it is covering.

One use would be if you have a table storing transactions and the bit is to flag if they have been processed. Then (after a short time) then majority will be true and just a few false. You would only be querying for false so the index would be valuable.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-08-12 : 14:30:06

quote:
Originally posted by nr

Not quite. If you want to count the number of values or do an existance check then the index would be useful as it is covering.

ahh, I see. good point.

-ec

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-13 : 01:34:13

You might also want to have bit columns as part of a compound index in conjunction with other columns that have high selectivity.

Even though they have low selectivity by themselves, each individual bit column could cut the number of page lookups in half, even with a 50-50 distribution. Four bit columns in an index should reduce the number of page lookups needed to 1/16th the number without the bit columns.

CODO ERGO SUM

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-13 : 09:07:46

Or considerably more if it makes the index covering.
Think the question is really about having the bit column as the first entry in the index.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources