John,
My initial opinion is not to use the VARCHAR column. It'll eat up more
storage space for starters (not to say this is the definitive reason), but
"Best Practice" dictates you should eliminate the possibility of storing bad
data by limiting the valid character set you are able to store. Since you
can store any character in a VARCHAR field, allowing something other than a
representation of TRUE/FALSE could lead to unnecessary hassles. Someone
could store a "ONE" which could throw things for a loop.
So that narrows the question down to BIT or TINYINT? It shouldn't matter
which you use performance wise; both data types are indexable so it's hard to
determine which to use based on pure performance metrics. Storage wise,
though, up to eight BIT columns will take up the same amount of space (1
Byte) as one TINYINT column. See below:
BIT: http://msdn2.microsoft.com/en-us/library/ms177603.aspx
TINYINT: http://msdn2.microsoft.com/en-us/library/ms187745.aspx
My recommendation is to use a BIT column if you have the need for one or more
Boolean fields in your table. Use a TINYINT column if for some reason you
might add a "Maybe" value to your acceptable character set. Otherwise in
terms of a one (column) to one (column) comparison they are basically the
same.
HTH,
John Eisbrener
SQL Database Administrator
Capitol Insurance Companies
-----Original Message-----
From: Ligda John
[mailto:mssqldba-ezmlmshield-x34169238.[Email address protected]
Sent: Thursday, November 29, 2007 12:41 PM
To: LazyDBA Discussion
Subject: data type tinyint?
If I'm going to sort by a column will SQL Server be faster with a tiny
int? 0/1 a bit 0/1, or a varchar(3) 'Yes'/'No'
--
DISCLAIMER
Confidentiality Note: This e-mail is intended only for the person or entity
to
which it is addressed and may contain information that is privileged,
confidential or otherwise protected from disclosure. Dissemination,
distribution or copying of this e-mail or the information herein by anyone
other than the intended recipient, or an employee or agent responsible for
delivering the message to the intended recipient, is prohibited. If you have
received this e-mail in error, please notify us immediately (telephone
415-288-0544 or e-mail [Email address protected] and destroy the original
message and all copies.
Hall Capital Partners LLC reserves the right to monitor and review the
content
of all e-mail communications sent and/or received by its employees.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page