Indexing Tables with Byte type fields
-
Newbie Question:
Just wandering, should lookup tables with primary key columns defined as byte be indexed? Most of my lookup tables only have about between 3-25 items.
Thanks.
--Lenard
-
What is the record size? Will all data fit on one SQL page?
-
You should still consider a Clustered index even on a small table. It will add very minimal overhead to storage (statistics stuff which you generally see created anyway, table will still be in same space just sorted).
-
Agreed, contradicted myself. If one do a proper db design it should not be an issue, secondly index can only help optimizer!
-
Great, that is what I was doing....just wanted to make sure I was on the right track.
Thanks guys.
--Lenard
-
What is a datatype of BYTE? Did you mean a BIT datatype, or something like a CHAR(1)?
I disagree with having indexes on all tables and can create a situation where the index will hurt the optimizer even with current statistics. Indexes are overhead, no unique clustered indexes also have to store a unique identifier, which is more overhead. If the table is small enough then let SQL Server pull the table into memory. Also, you have to consider the overhead to transactions (update, delete, and inserts). Lot's to think about when creating an index!! Of course, this is just one persons opinion..from someone who doesn't like EM creating a clustered index..I think that needs to be a decision, not a default.
If you are talking about a BIT datatype..Good question!! Since SQL Server does not allow indexes to be built on BIT datatypes (probably because the selectivity will be poor). I am going to test this out; create a composite primary key with one of the attributes being of type BIT.
"Keep Your Stick On the Ice" ..Red Green -
Good rule of thumb..
Tuning is all about I/O
"Keep Your Stick On the Ice" ..Red Green -
I was referring to a "TinyInt" as a "byte".
-
quote:
What is a datatype of BYTE? Did you mean a BIT datatype, or something like a CHAR(1)?I disagree with having indexes on all tables and can create a situation where the index will hurt the optimizer even with current statistics. Indexes are overhead, no unique clustered indexes also have to store a unique identifier, which is more overhead. If the table is small enough then let SQL Server pull the table into memory. Also, you have to consider the overhead to transactions (update, delete, and inserts). Lot's to think about when creating an index!! Of course, this is just one persons opinion..from someone who doesn't like EM creating a clustered index..I think that needs to be a decision, not a default.
If you are talking about a BIT datatype..Good question!! Since SQL Server does not allow indexes to be built on BIT datatypes (probably because the selectivity will be poor). I am going to test this out; create a composite primary key with one of the attributes being of type BIT.
As stated he was referring to TINYINT which supports 256 possible values (0-255 no negatives). A bit will either be 1 or 0 which menas it is a 50/50 chance so table scanning is as efficient do to the low selectivity of the situation and would never make a good index. Similarly if you have a relatively low number of unique values for a column it generally will not make a good index.
-
just a quick note on bit data type, you can index on it, although it does not make sense to do so. weird thing is in enterprise manager to index on it, you can not do it in design mode, you must insted right click on the table name go to all tasks, manage indexes, then you can select bit field.
-
What is the point in an index on a bit data type column. btw i have a background in c/c++ and thought that B-Tree meant Binary Tree. Now MS are telling me that it means Balance Tree. Has this always been the case?
-
Balance Tree? What the heck is that? I know what a B-tree is and a B* tree, but....
....biting my toungue!!
"Keep Your Stick On the Ice" ..Red Green -
Hi jeffwe,
quote:
Balance Tree? What the heck is that? I know what a B-tree is and a B* tree, but........biting my toungue!!
Definition: A tree where no leaf is much farther away from the root than any other leaf. Different balancing schemes allow different definitions of "much farther" and different amounts of work to keep them balanced.
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply