Wednesday, May 07, 2008

Data Types of SQL 2000 and SQL Server 2005

Exact numerics

Type

From

To

bigint

-9,223,372,036,854,775,808

9,223,372,036,854,775,807

int

-2,147,483,648

2,147,483,647

smallint

-32,768

32,767

tinyint

0

255

bit

0

1

decimal

-10^38 +1

10^38 –1

numeric

-10^38 +1

10^38 –1

money

-922,337,203,685,477.5808

+922,337,203,685,477.5807

smallmoney

-214,748.3648

+214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate numerics

Type

From

To

float

-1.79E + 308

1.79E + 308

real

-3.40E + 38

3.40E + 38

 

datetime and smalldatetime

Type

From

To

datetime (3.33 milliseconds accuracy)

Jan 1, 1753

Dec 31, 9999

smalldatetime (1 minute accuracy)

Jan 1, 1900

Jun 6, 2079

 

Character Strings

Type

Description

char

Fixed-length non-Unicode character data with a maximum length of 8,000 characters.

varchar

Variable-length non-Unicode data with a maximum of 8,000 characters.

varchar(max)

Variable-length non-Unicode data with a maximum length of 231 characters (SQL Server 2005 only).

text

Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

 

Unicode Character Strings

Type

Description

nchar

Fixed-length Unicode data with a maximum length of 4,000 characters.

nvarchar

Variable-length Unicode data with a maximum length of 4,000 characters.

nvarchar(max)

Variable-length Unicode data with a maximum length of 230 characters (SQL Server 2005 only).

ntext

Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

 

Binary Strings

Type

Description

binary

Fixed-length binary data with a maximum length of 8,000 bytes.

varbinary

Variable-length binary data with a maximum length of 8,000 bytes.

varbinary(max)

Variable-length binary data with a maximum length of 231 bytes (SQL Server 2005 only).

image

Variable-length binary data with a maximum length of 2,147,483,647 bytes.

 

Other Data Types

  • sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
  • timestamp: Stores a database-wide unique number that gets updated every time a row gets updated.
  • uniqueidentifier: Stores a globally unique identifier (GUID).
  • xml: Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
  • cursor: A reference to a cursor.
  • table: Stores a result set for later processing.

 

Source: http://www.teratrax.com/sql_guide/data_types/sql_server_data_types.html

 

No comments: