May 17, 2005 at 8:38 pm
Q1
can someone tell me the exact difference between varchar and nvarchar in sql server 2000.
I know my string will be between 8 and 32 character long. Which data type should I use for better performance.
Q2.
Would I gain any performance improvement by using bit instead of tinyint or integer for Boolean?
Q3
Is true-false bit test significantly faster than comparing two dates?
May 17, 2005 at 9:53 pm
Q1 : nvarchar exists so that you can store unicode data (characters that require 2 bytes to be represented). Each character of an nvarchar column requires 2 bytes of storage whereas a varchar column requires 1 byte per character. Potentially, varchar will be quicker but that may well mean that you cannot store the data that you need.
Q2 : You should be designing your database so that the datatypes of your columns match the range of values that are expected to be stored in that column. If you only need 2 values, bit is OK. If you need up to 256 values, tinyint is the one, etc. This will give you a set of tables that occupy the least amount of disk space. From a performance perspective this means that when you ask SQL to give you a row, it has the least amount of work possible. This means less data to transfer from disk to memory. Less data means this takes less time. And this applies to both indexes and to data. Once in memory, the filtering should be quicker because the filter needs to look at less data. i.e. choosing smaller (but appropriate) data types can lead to better performance. Having said that, if you are regularly filtering on a bit datatype, you may be better off defining that as a tinyint so that it can be indexed.
Q3 : I don't understand how you would apply a true/false test that would sensibly replace the comparison of two dates. Bit datatypes cannot be indexed so depending on how you are coding the test, the date comparison might actually be quicker because a the bit comparison might actually cause a table scan.
May 18, 2005 at 8:42 am
good advice above, but bits cannot be used in GROUP BY and some other operations. Check BOL for the restrictions and be sure they will not byte you. One note, the first 8 bits share a single byte, so if you only have one bit, oit is the same as tinyint. I tend to stick with tinyint since ofnte someone says bit and we wind up needing 3 or 4 values.
If you are not storing non-english values, stick with vawrchar.
May 18, 2005 at 4:22 pm
Steve,
Is "vawrchar" a typo in your posting?
I will be dealing with English character only.
About bit:
If I have single bit column in my table then probably it won't help to declare it as bit unless sql server store things column by column and not record by record. Special databases store things column by column instead of record by record.
Only mutliple of 8 bit can be stored.
In addition to get to the right bit cpu has to do left or right shift several time that may take more cpu time than if we declare it as tiny integer. That is the main reason why I asked the original question.
The smallest quanta for hard disk is segment (512 to 2k bytes), for memory is 1 bytes, for cpu is 1 bit with cost of left and right shift.
Even if I have 8 boolean columns in a table even then bit may not be the fastest way to test because of left and right shift by the cpu to get to the right bit.
I will appretiate further insight into this.
Munzer
May 18, 2005 at 5:08 pm
vawrchar was a typo - should be varchar
May 19, 2005 at 4:02 pm
Steve,
Bit can be grouped on:
declare @bit table (cbit bit)
Insert @bit values (1)
Insert @bit values (0)
select cbit, count(*)
from @Bit
group by cbit
Signature is NULL
May 19, 2005 at 8:03 pm
Can anyone shed light on the following?
If I have single bit column in my table then probably it won't help to declare it as bit unless sql server store things column by column and not record by record. Special databases store things column by column instead of record by record.
Only mutliple of 8 bit can be stored.
In addition to get to the right bit cpu has to do left or right shift several time that may take more cpu time than if we declare it as tiny integer. Please see my original question at the top of this link.
The smallest quanta for hard disk is segment (512 to 2k bytes), for memory is 1 bytes, for cpu is 1 bit with the cost of left and right shift.
If I have 8 boolean columns in a table even then bit may not be the fastest way to test because of left and right shift required by the cpu to get to the right bit. If it is tiny integer (8bit) then one clock cycle cpu can tell if it is zero or 1.
I will appretiate further insight into this.
Munzer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply