70-461 Training Kit Chapter 2, Lesson 2: Data Type

Data Type:

Is a constraint

Encapsulates behavior

Relational Model's Physical Data Independence

Let the storage engine take care of the internal storage format

Exact Numeric:

TINYINT (1 byte)

SMALLINT (2 bytes)

INT (4 bytes)

BIGINT (8 bytes)

NUMBERIC (size varies depend on precision)
DECIMAL (size varies depend on precision)

SMALLMONEY(4 bytes)

MONEY(8 bytes)

Approximate Numeric

REAL (4 bytes)

FLOAT

Character non-Unicode (CHAR, VARCHAR)

support only one language based on collation properties

1 byte/character

Use single quotation marks, 'xyz', to indicate regular character strings (non-Unicode)

Character Unicode (NCHAR, NVARCHAR)

Unicode, support multiple language

2 bytes/character

Use N'xyz', to indicate Unicode character string.

Binary Strings:

BINARY

VARBINARY

Image

Date and Time:

DATE (3 bytes)

TIME (3 to 5 bytes)

DATETIME2 (6 to 8 bytes)

SMALLDATETIME (4 bytes)

DATETIME (8 bytes)

DATETIMEOFFSET (8 to 10 bytes)

Other Data Types

XML

Hierarchyid

Uniqueidentifier

Timestamp

Spatial Types

Geometry

Geography

Sql_variant (can be used to store variety of data type)

Table

Cursor

BOL Data Type: http://msdn.microsoft.com/en-us/library/ms187752.aspx

Microsoft 70-461 Training Kit