RE: data type tinyint?

RE: data type tinyint?

 

  

VARCHAR(3) is a waste of resources, both to store the bits that indicate
the length, and the extra passes required for interpreting those bits
and translating the following bytes accordingly. Kimberley Tripp told
me anything under VARCHAR(5) is more efficient to use CHAR(n). Just be
aware of the few cases where the trailing spaces are respected in
comparisons.

-----Original Message-----
From: Eisbrener John
[mailto:mssqldba-ezmlmshield-x69349416.[Email address protected]
Sent: Thursday, November 29, 2007 1:26 PM
To: LazyDBA Discussion
Subject: RE: data type tinyint?

Michael,

Sorry, but bit columns are indexable in 2000. You can also include them
within a compound key. The catch is that you couldn't do any of this
via the GUI, but you could if you scripted it out. If you run this
script on a SQL 2000 database, I guarantee it will work:

USE [TestDB]
GO

CREATE TABLE [dbo].[TestTable](
[Col01] [varchar](50) NOT NULL,
[Flag] [bit] NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY NONCLUSTERED
(
[Col01] ASC,
[Flag] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_TestTable_BitIndex] ON [dbo].[TestTable] (
[Flag] ASC
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_TestTable_CompoundKey] ON [dbo].[TestTable] (
[Col01] ASC,
[Flag] ASC
) ON [PRIMARY]
GO

As you can see it includes an index, a compound key, and a composite
(and in this case a covering) index. I was mislead by the GUI too until
someone pointed it out to me.

John Eisbrener
SQL Database Administrator
Capitol Insurance Companies


-----Original Message-----
From: Shorkey Michael F
[mailto:mssqldba-ezmlmshield-x16949563.[Email address protected]
Sent: Thursday, November 29, 2007 3:05 PM
To: LazyDBA Discussion
Subject: RE: data type tinyint?

To bit or not to bit? That is the question. Bit fields are ONLY good
for true/false, yes/no type answers. It's either one or the other. The
data is actually stored as zero (0) or one (1) with no other
possibilities. They (bit columns) however are NOT indexible, at least
in older versions of SQL (like 2000). This may have changed in 2005,
but I doubt it. They can't be included in a multiple-column (compound)
key either.

So the real choice: varchar or tinyint? Always go with a number!

But why tinyint? The range for tinyint values is only +/- 255! You
stated there's at least 12,000 records in your data table, so tinyint
shouldn't work at all. SMALLINT is +/- 32,000 (roughly) and INT +/-
2Billion. Most people use INT for IDENTITY columns.

Does that help?

-----Original Message-----
From: Ligda John
[mailto:mssqldba-ezmlmshield-x1964073.[Email address protected]
Sent: Thursday, November 29, 2007 3: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



---------------------------------------------------------------------
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


MS Sql Server LazyDBA home page