char vs. varchar storage size 2

[ks1392]

Hi folks, I'm pretty new to SQL server and have a few easy questions. I'm setting up a table in which a text field will always be 8 characters long. What, if any, storage size impact would occur using char(8) vs. varchar datatypes?

Also, what is the difference between nvarchar and varchar datatypes?

Additionally, I perused the datatypes in enterprise manager and didn't come across any boolean types. Will I have to define one for this purpose?

 

[fluteplr]

If the field is always a specific length use a char, varchar just adds unneeded overhead.

Also I rarely set varchar fields less than 40 characters, but that is just my preference.

All of the 'N' char types are designed for unicode characters which take 2 bytes per character rather than the normal 1 byte. In other words a nchar(8) takes 16 bytes a char(8) takes 8.

The bit data type is a boolean It can store 0,1 and in 2000 null.

It cannot be indexed. So I frequently use a tinyint instead.


(Before this creates a long thread indicating why indexing a bit field is dumb since it can only have 2 values, it is usefull to have an index on a field with only two values if 5% or less of the records have one of the two values.

As in I have 100,000 records and in 100 of them the field is 1 and the rest the field is 0 )

Also if you are using VB as the front end because of the stupid way vb handles booleans (using -1) you may need to use a smallint.

 

[ks1392]

Thanks for the quick response, which leads me to another question. Why when I'm using DTS to transfer a db from Access to SQL server does it default all the text based fields to nvarchar(255)? Safety measure? Plus, I'm not sure in what situation I would use the unicode datatypes vs. the non-unicode. Any scenarios or advice?

 

[fluteplr]

You only need unicode if you are storing data in a unicode language, chinese, japanese, korean etc.

I have found that all transfers from access are dumb by default. Rather than let dts create the table structure. Create the structure ahead of time and just use dts to transfer the data.

 

[pheiner]

I agree, "If the field is always a specific length use a char". A varchar adds a byte so that the server can tell how long it is plus the cpu cycles to work it out.


When I decide between char and varchar I am considering the storage space used. If you use a char(30) and the average data stored is 19 and you have 1,000,000 records you have just used up 30,000,000 bytes and 11,000,000 bytes of that disk space that is storing nothing. If you used a varchar(30) you have just used 20,000,000 bytes (notice that I used 19 and added 1 for the varchar sizing). That is a saving of 10,000,000 bytes on and that is for one column in one table.


Books online is a good source of information.

 

[ks1392]

Great, thanks for the help guys. Making changes based on this info will definitely get my db down to a manageable size.