nchar or char or nvarchar or varchar??

Hi,
Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ?
Also, what really is the benefit of one over the over :confused:
ThanksOriginally posted by Joozh
Hi,

Which of the above data type (alongwith size) should be used for storing things like Customer Name, Company name etc . ?

Also, what really is the benefit of one over the over :confused:

Thanks

the difference between char and varchar is that: char is a fixed length datatype meaning, if suppose u have char(8) and you store a value say 'Harsh' in this variable then it gets stored a 'Harsh___' where at the right the remaining space is padded with blanks, in short all the 8 bits are utilised.
whereas if it would have been a varchar(8) it would have saved it as 'Harsh' meaning only the exact required size is allocated which is 5 in this case.
When you want to store the data in unicode format use nchar or nvarchar .|||Thanks harshal_in :-) :)

That clarifies... One last quick question:

Is it okay to assume then that the best approach is to use nvarchar OR varchar?|||nvarchar uses double the amount of storage compared to varchar, so use varchar unless you need to store double byte data (eg japanese, chinese)|||Originally posted by Joozh
Thanks harshal_in :-) :)

That clarifies... One last quick question:

Is it okay to assume then that the best approach is to use nvarchar OR varchar?

Don't use the NVARCHAR or NCHAR data types unless you need to store 16-bit character (Unicode) data. They take up twice as much space as VARCHAR or CHAR data types, increasing server I/O and wasting unnecessary space in your buffer cache.

If the text data in a column varies greatly in length, use a VARCHAR data type instead of a CHAR data type. The amount of space saved by using VARCHAR over CHAR on variable length columns can greatly reduce I/O reads, improving overall SQL Server performance.

Another advantage of using VARCHAR over CHAR columns is that sorts performed on VARCHAR columns are generally faster than on CHAR columns. This is because the entire width of a CHAR column needs to be sorted.|||There are some advantages to using the CHAR and NCHAR types, but those advantages are somewhat esoteric. If you want more information see Kalen Delaney's Inside SQL Server 2000 (http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid=mHu18NPJNB&isbn=0735609985&itm=1) (which I see as a "must read" for a SQL geek anyway).

Basically it boils down to bookkeeping. Variable length columns require the DBE (data base engine) to compute both the start and the length of the variable columns. This adds considerable overhead at the row manager level, which makes access to all of the columns in a row with variable width columns take longer. This isn't significant in most cases, but it does matter for bulk loads and similar operations. Particularly for staging very large warehouses, it can be significant.

-PatP|||But the size of the row can be just as much a performance factor...

For example Char(255) will store all of that...now think about returning all the data, as comp[ared to what is just there...

I beleive a rule of thumb is something like 10 chars...

10 or less make char, otherwise varchar...

Pat?|||It is actually more complicated than that, but you could approximate the rule by using: If the maximum column length minus the minimum column length is more than 10 characters, then use a variable length column. I still say you should just read the book so you'll understand the gist of the rule (and 10,000 other important things) and the factors that weigh into it, but the approximation is a lot better than nothing.

-PatP