What Data_Type should I use.

[Jimmy2128]

I want to add a field in my SQL table called YES/NO. Where the user have the choice. What Data_Type should I use.. Thanks for your help.

 

[fredericofonseca]

look at your copy of books online.

You can use any of the numeric data types available or the bit data type as being the "better" choices for that purpose.

you can also use any character data type.

It is your choice.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[hmckillop]

Personal preference would be a bit datatype, though the problem with this type, is that it cant be included in an index.Use a Smallint if this will be an issue and make a constraint that values must be 1,0.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[r937]

use TINYINT instead of SMALLINT

i also like CHAR(1) for this purpose

r937.com | rudy.ca

 

[JohnBates]

if the values will always be Yes or No, On or Off, True or False... go with a bit data type, and make it non-nullable.

Regards,
John

 

[r937]

well, as has already been mentioned, you cannot use a bit column in an index

nor can you use it in a GROUP BY

see Booleans in SQL Server
(registration may be required, but it's free)

r937.com | rudy.ca

 

[SQLDenis]

If you have only 2 possible values for a field it doesn't matter (to the index) what data type it is since your selectivity is very low

unless..... you include this field as part of a covering index on the table, then make it a tiny int or char(1)

http://sqlservercode.blogspot.com/

 

[vongrunt]

Hm... I just created index/did GROUP by on bit column.

?


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[r937]

hmm, maybe fixed in a later version? my test was on sql server 2000 (see DDL in article linked to above)

r937.com | rudy.ca

 

[r937]

oh, never mind the article i linked to, here is the DDL

create table test_bit
( id tinyint not null identity
, yesno bit not null )

insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (0)
insert into test_bit (yesno) values (1)
insert into test_bit (yesno) values (0)

select * from test_bit

id yesno
---- -----
1 1
2 1
3 0
4 1
5 0

select yesno, count(*)
from test_bit
group
by yesno

Server: Msg 252, Level 16, State 2, Line 1
Cannot group by a bit column.



r937.com | rudy.ca

 

[SQLDenis]

This works on my development machine
version is below
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)

http://sqlservercode.blogspot.com/

 

[vongrunt]

No errors to me (SP4 Std edition).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[r937]

well, what can i say

i don't have access to that machine no more, so i can't say what version sql server it was

live and learn, i guess

i will stick with TINYINT, eh

r937.com | rudy.ca

 

[Evadman]

I can group by just fine on a bit column running SP4. You can not index a bit field however. In all honesty, creating a index on a bit field makes a whole lot of no sense because the index will be ignored anyway when the server does the execution plan. A bit field will always result in a table scan.