With 12,000 records you could use whatever data type you wanted as the amount
of data you're touching in that table is rather small. If this table isn't
going to grow, row wise, then leave it the way it is. Just remember that
your current VARCHAR(3) field will accept all manner of invalid data; it's an
issue you might not ever face if you have good application developers, but if
you listen to Ed, they will inevitably insert the wrong value and blame you
when it blows up their code.
Regarding an index on the column, you won't need one (or be asked to have one
for that matter) until you start to see performance degradation. If you were
to have one I'd imagine the BIT data type would be the easiest to maintain
followed by the TINYINT with the VARCHAR(3) being the most computationally
intensive of the three based on valid character sets for each data type.
Honestly though, I've never looked at indexing in SQL 2000 or 2005 on that
granular of a scale.
John Eisbrener
SQL Database Administrator
Capitol Insurance Companies
-----Original Message-----
From: Ligda John
[mailto:mssqldba-ezmlmshield-x1964073.[Email address protected]
Sent: Thursday, November 29, 2007 2:39 PM
To: LazyDBA Discussion
Subject: RE: data type tinyint?
Thanks Jeremy and John.
My data set is only twelve thousand records and I tested a sort and
where clause with all three types just for the eck of it. They are all
the same. I cant get anything from the business analyst to indicate
there should be an index on this column but I am curious about which
type is easier for the server to index/maintain.
Right now the column is varchar(3) with a check constraint for either
'Yes' or 'No'. Probably I'll just leave it.
Thanks all
-----Original Message-----
From: Eisbrener John
[mailto:mssqldba-ezmlmshield-x43413388.[Email address protected]
Sent: Thursday, November 29, 2007 12:23 PM
To: LazyDBA Discussion
Subject: RE: data type tinyint?
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
---------------------------------------------------------------------
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
--
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