Binary Data Type
The binary data type is used to hold fixed length binary-formatted data. This is useful for storing information that must be encrypted, such as passwords. The size of binary columns is specified as between one and eight thousand bytes.
VarBinary Data Type
The varbinary data type is a variable length version of the binary type. The data size is between one and eight thousand bytes but the amount of storage space used is determined by the actual length of information stored.
SQL_Variant Data Type
The sql_variant data type can be used to store information of varying types in a single table column. An sql_variant can contain most other data types with the exception of large objects and user defined types.
Large Object Data Types
Large object data types are used to hold information that cannot fit in the limited size of the previously mentioned types. They permit the storage of binary data in addition to standard Unicode and non-Unicode text. The information in a large object is held separately to standard row data, potentially in a separate filegroup.
VarChar(MAX) Data Type
The varchar(MAX) data type is used to hold non-Unicode text and is similar to the standard varchar type. The MAX data size indicates that columns of this type may hold up to almost two gigabytes of information with an enforced maximum size of 2,147,483,647 bytes.
This data type was introduced in SQL Server 2005 and should be considered a replacement to the text data type. Text data is still permitted for legacy and compatibility purposes. It may be deprecated in a future release of SQL Server.
NVarChar(Max) Data Type
The nvarchar(MAX) data type is equivalent to varchar(MAX) except that it stores Unicode characters. This data type is a replacement for the ntext data type, which may be deprecated in future SQL Server versions.
Text Data Type
The text data type provides a method for storing large non-Unicode character strings of up to just less than two gigabytes. In SQL Server 2005, the text data type is provided for legacy and compatibility purposes only. The varchar(MAX) data type should be used instead for new databases.
NText Data Type
The ntext data type is the Unicode equivalent of text. For new database, use nvarchar(MAX) instead of ntext.
XML Data Type
The XML data type is used to hold XML documents and fragments. A fragment is an XML document that does not include a single top-level element. An XML schema may be associated with an XML data column, preventing information that does not confirm to the schema from being stored. This type of column is said to hold typed XML. The maximum size of XML data is 2,147,483,647 bytes.
VarBinary(MAX) Data Type
The varbinary(MAX) data type is similar to the varbinary data type. This large object version may hold up to 2,147,483,647 bytes of information in a single column. It is often used for holding graphical information or information that would normally be held in a file. The data type is a replacement for the image data type, which may be deprecated in the future.
Image Data Type
The final data type in SQL Server 2005 is the image. This is a legacy data type that is provided for compatibility with older versions of SQL Server. For new databases, the varbinary(MAX) data type should be used instead.
Nullable Columns
In addition to the type of data held in a column, all table columns can be marked as either nullable or not nullable. A column that is not nullable requires that a value is present in every row. Nullable columns can be assigned the special value of NULL. A null value in a row indicates that the value has not been set.
The null value gives an additional option to the range of values that a column can contain. This is beneficial when all of the possible values that a data type gives could potentially be used in a column but an additional, "undefined", option is required. For example, consider a database that holds the resultant data from a survey where interviewees answer yes or no to a series of questions. The answers would be stored in nullable bit columns with a 0 indicating that the user answered "no", a 1 for "false" and null to indicate that the question remains to be answered.
5 July 2008