Microsoft® SQL Server® 2012 Bible

174

Part II: Building Databases and Working with Data

of the various data types available in SQL Server. Understanding these different types are

pivotal when it comes to building expressions and using the scalar functions in an effective

and effi cient manner.

Character Data Types

SQL Server supports several character data types, as listed in Table 8-1.

TABLE 8-1 Character Data Types

Data Type Description Size in Bytes

Char(n) Fixed-length character data up to 8,000 char-

acters long using collation character set

Defi ned length * 1 byte

Nchar(n) Unicode fi xed-length character data Defi ned length * 2 bytes

VarChar(n) Variable-length character data up to 8,000

characters long using collation character set

1 byte per character

VarChar(max) Variable-length character data up to 2GB in

length using collation character set

1 byte per character

nVarChar(n) Unicode variable-length character data up to

8,000 characters long using collation character

set

2 bytes per character

nVarChar(max) Unicode variable-length character data up to

2GB in length using collation character set

2 bytes per character

Text Variable-length character data up to

2,147,483,647 characters in length Warning:

Deprecated

1 byte per character

nText Unicode variable-length character data up to

1,073,741,823 characters in length Warning:

Deprecated

2 bytes per character

Sysname A Microsoft user-defi ned data type used for

table and column names that is the equivalent

of nvarchar(128)

2 bytes per character

Unicode data types are useful for storing multilingual data. The cost, however, is the

doubled size. Some developers use nvarchar for all their character-based columns, whereas

others avoid it at all costs. You should use Unicode data when the database might use for-

eign languages; otherwise, use char, varchar, or text.

c08.indd 174c08.indd 174 7/30/2012 4:21:11 PM7/30/2012 4:21:11 PM

http://www.it-ebooks.info