"Why are we getting a ? in our SQL database in people's names?" Well a while back we came across this problem from one of our clients in the first name and last name fields in their client database. The problem was that we were using varchar (which only accepts regular English language type letters and symbols) and not nvarchar which accepts unicode characters such as â or é for other languages. I thought I'd help out people having the same problem and explain the difference between char, varchar, nchar and nvarchar here.
Char(n)
Char is a fixed length variable of (n) characters...that is to say if you have a field called Active and it accepts Y and N as input and is not null (must be filled in) you would use char(1) as a variable type. For each row of data put into the database this variable type will reserve enough space (one byte per 'n') for (n) characters. So if you set FirstName(20), the database will allocate 20 bytes for each row of data in that field. (Max 'n' is 8000 characters)
Varchar(n)
Varchar is a dynamic length variable of (n) characters...or, as per the last example of FirstName(20) it will only use up 5 bytes of space if you input the name Brian but uses slightly more processor power than a char. Therefore if you always have a variable that is 5 characters long you might want to use a char for processor savings or if you have something that fluctuates a fair bit more in size you might want to use a varchar for hard drive space savings. (Max 'n' is 8000 characters)
Nchar(n)
Nchar is the same as char except for the fact it can store unicode (â, é, etc.) data and always allocates 2 bytes of space per 'n' characters. That means that if you have a nchar(20) it will allocate 40 bytes of space for each row of data with this field type. (Max 'n' is 4000 characters)
Nvarchar(n)
Nvarchar is the same as varchar except for the fact it can store unicode (â, é, etc.) data and allocates 2 bytes of space per 'n' characters used. So if you stored Brian in a nvarchar(20) it would allocate 10 bytes of space for each row of data with this field type. (Max 'n' is 4000 characters)