SQL Server 7.0 does have a few additions to the available datatypes. This is due to the Unicode support that is now available. Unicode is character set definition that encompasses the available languages in the world. It is similar to the ASCII standard in that it secified an encoding scheme for a set of chacaters that allowed them to be accuraely translated across multiple platforms. But the ASCII standard suffers from one major flaw. It was primarily designed with the English alphabet in mind. As such it does not have the capacity to encompass additional alphabets such as the Chinese alphabet. The Unicode specification was founded to address this. Standard ASCII based systems will store each character in a single byte. Unicode based systems will use two bytes to store each character.

This does not mean that Unicode datatypes have twice the storage capacity as ASCII datatypes. You still have a maximum of 8000 bytes that can be stored in a character datatype. If you are using an ASCII datatype, you can store 8000 characters. If you are using a Unicode datatype, you can only store 4000 characters.

Within the tables below, you will see a designation like the following: nchar(n). This is translated to mean a datatype of nchar with a defined capacity of n. For example, nchar(16) can store up to 16 Unicode characters.

Unicode Datatypes

Character Data

Character data comes in two varieties: variable width and fixed width. This can be very important when concatenating data together. A char column will space pad the data to the maximum width of the column. A varchar column will only use as much space as is required to store the data. This is why you will see different results based upon the datatype that is selected. Choosing one or the other can mean the difference between seeing Michael Hotek and Michael Hotek. There is a performance penalty though. A char(8) column will process more quickly than a varchar(8) column. The trade off between storage and speed is at about 8 characters. Anything of 8 characters or less should be defined as char within your tables unless space is at a premium. The nullability of a column also has an effect. If you define a char column as nullable, it is treated just like a varchar column.

This brings up another short aside on proper datatype selection. There have been many instances where I have seen varchar(1) and varchar(2) columns. Further, in many cases these columns are defined as nullable. Datatype definitions such as this are a complete waste of time and effort.

SQL Server 7.0 Datatypes 1 2 3 4 5 6