Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 202

Table 8-2: Data Types (continued)

Group Type name Range Storage Comments

Timestamp A non-nullable

timestamp
column is
equivalent to a
binary(8) column.
A nullable
timestamp
column is
equivalent to a
varbinary(8)
column.

8 bytes Each database has a

counter for each insert or
update performed on a
table. Only one per table is
allowed. Synonymous
with ROWVERSION.

Uniqueidentifier A GUID 16 bytes See ''Using

Uniqueidentifier
datatypes.''

Non-persisted
types

table — — Available only for T-SQL

variables. It cannot be
saved or used as a data
type when defining a
column. Can be used
instead of a temporary
table. Holds result sets for
later processing.

Cursor — — Available only for T-SQL

variables. It cannot be
saved or used as a data
type when defining a
column.

Character notes: When using CAST or CONVERT if n is not specified then the default length is 30. Use char and nchar when the
size of data entries for a column are relatively uniform. Use varchar and nvarchar when the size of data entries for a column widely
vary and the column needs to be fairly wide. Personally, I recommend using the CHAR or NCHAR data type for lengths of 20 or
less. Use varchar(max) and nvarchar(max) when the sizes of data entries for a column vary considerably,andthesizeismorethan
8000 bytes. Also note that Microsoft recommends migrating away from the text and ntext data types, because they plan to remove
them from future versions of SQL Server.

Unicode notes: Unicode was created to address the need to accommodate characters from different languages, such as German and
Russian. Unicode uses 2 bytes per character, which is why the limits for these data types is half what it is for their non-unicode coun-
terparts. So, unless requirements mandate international support use the non-unicode types. They take up less storage to store the
same amount of data.

Binary notes: When using CAST or CONVERT if n is not specified then the default length is 30. Use binary when the size of data
entries for a column are relatively uniform. Use varbinary when the size of data entries for a column widely vary and the column
needs to be fairly wide. Personally, I recommend using the binary data type for lengths of 20 or less. Use varbinary(max) when the
sizes of data entries for a column vary considerably, and the size is more than 8000 bytes. Also note that Microsoft recommends
migrating away from the image data type, because they plan to remove from future versions of SQL Server.