September 24, 2008 at 2:08 pm
We are looking at adding a column to a table that stores transactions, that will serve as a single column Index, non-clustered. The column will store a number (between 1 and 27) that is used to identofy what type of transaction a row of data represents.
I've always focused on trying to find the fastest way to do things in code and have always enjoyed using bit opertaors in code because they are so computer friendly in terms of speed. SQL Server does have a Bit data type that is great for Boolean values but I need something with a few more choices beyond 2.
Obviously SQL Server can perform faster operations when using numbers as criteira then string/text but what type of numeric value other then Bit is the fastest?
Any thoughts?
Thanks
Kindest Regards,
Just say No to Facebook!September 25, 2008 at 12:39 am
TINYINT?
N 56°04'39.16"
E 12°55'05.25"
September 25, 2008 at 7:01 am
SQL Server will store up to 8 bits in the same byte. 1 bit field takes 8 bytes of space, 2 bits takes 8 bytes, 9 bits takes 16 bytes.
However, bits don't index all that well. They only have two values. You would need multiple bits to handle 27 values so to get good performance you would need multiple composite indexes - this would give you a lot of control over your search patterns, but getting it right is complicated and could take a lot of space. I would just stick to a smallint for your situation - you will get a single index, it covers all of your possible values, and it does not take up much space.
Don't overthink this one.
September 25, 2008 at 8:54 am
Thanks to each who have replied so far. Currently I am testing this using the TNYINT data type and I now feel better seeing others respond with a suggestion of using this data type. If I recall correctly, the TINYINT is a new data type and because of that I wasn't certain if going with a more established data type (INT for example) would be a better idea.
Thanks Again and if anyone else has input that differs from using TINYINT, please post your toughts!
Kindest Regards,
Just say No to Facebook!September 25, 2008 at 10:03 am
It's not a new data type. I know it's been around since 2000, I think it's been around much longer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2008 at 11:01 am
I dont think TINYINT is a new data type
http://msdn.microsoft.com/en-us/library/ms187745.aspx
But my guesses are either BIT or TINYINT
September 29, 2008 at 8:19 am
YSLGuru (9/24/2008)
The column will store a number (between 1 and 27)
This tells me that BIT would be too small. You'd have to use 5 BIT fields to cover all of the possibilities, so it would be much easier to just use one column of type TINYINT.
September 30, 2008 at 8:53 am
If you are using a field to determine what type of data is being stored in a row, I suspect that your data is not normalized. If it is trully different data base upon the field, then it might be better to have one table for each type of data.
Steve
September 30, 2008 at 12:46 pm
.
Kindest Regards,
Just say No to Facebook!September 30, 2008 at 4:31 pm
sblock (9/30/2008)
If you are using a field to determine what type of data is being stored in a row, I suspect that your data is not normalized. If it is trully different data base upon the field, then it might be better to have one table for each type of data.Steve
It's not properly normalized but there's nothing we can do about it because changes to the schema at that level would at a minimum void our pre-paid support plan with the sofwtare vendor whose product uses the DB and at worse completely break the applications ability to use it.
Kindest Regards,
Just say No to Facebook!Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply