Appendix C: Data Types in SQL Server 2005


Table C-1 contains a list of all SQL Server 2005 native data types along with their range, size, and sample constants.

Table C-1: SQL Server 2005 Native Data Types

Data Type and Synonym

Description

Range or Length

Storage Size

Sample Constant

Character Strings

char (character)

Character string

1 to 8,000 chrs

1 to 8,000 bytes

‘D12-D13A36’

varchar (character varying)

Variable-length character string

1 to 8,000 chrs

1 to 8,000 bytes

‘Toronto’

varchar (max)

New large value variable-length character string

Ito231-l chrs

16 bytes + 0 to 2GB

‘SQL Server’

text

Large value variable-length character string from earlier versions

Ito231-lchrs

1 6 bytes + 0 to 2GB

‘SQL Server’

Unicode Character Strings

Nchar (national character or national char)

Unicode character string

1 to 4,000 chrs

2 to 8,000 bytes

N ‘Sunder id’

Nvarchar (national character varying or national char varying)

Variable-length Unicode character string

1 to 4,000 chrs

2 to 8,000 bytes

N ‘Q’

Nvarchar (max)

New large value variable-length Unicode character string

Ito230-lchrs

16bytes + OtolGB

N ‘HWKO ara’

Ntext (national text)

Large value variable-length Unicode character string from earlier versions

Ito230-lchrs

16bytes + OtolGB

 

Date and Time

datetime

Date and time

1-Jan-I/Sato 31-Dec-9999; precision: 3 ms

8 bytes

‘6/27/1998 10: 20: 17 .31’

smalldatetime

Small date and time

l-Jan-1900to6-Jun-2079; precision: 1 min

4 bytes

‘Oct 30, 1993 14: 30’

Integer Numbers

tinyint

Tiny integer

0 to 255

Ibyte

17

smallint

Small integer

-32,768 to 32,767 (-215 to 21S-1)

2 bytes

23017

int

Integer

-2,147,483,648 to 2,147,483,647 (-231 to 231-1)

4 bytes

343013

bigint

Big integer

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (-263 to 263-1)

8 bytes

3222121343013

bit

Logical

0,1, or Null

1 byte (up to 8 bits per byte)

1

Exact Numbers

numeric (decimal or dec)

Numeric or decimal

-1038 to 1038-1 (depends on precision and scale)

5 to 17 bytes

-352.4512

Approximate Numbers

real

Real (single-precision) number

-3.40 1038 to 3.40 1038

4 bytes

-232 .212E6

float

Float (double-precision) number

-1.7910308 tol.79103ll8

8 bytes

34 .2131343E-64

Monetary

smallmoney

Small monetary data type

-214,768.3648to 214,748.3647

4 bytes

$120.34

money

Monetary data type

-922,337,203,685,477.5808 to922,337,203,685,477.5807

8 bytes

$120000000

Binary

binary

Fixed-length binary string

1 to 8,000 bytes

1 to 8,000 bytes

OxaSdl

varbinary

Variable-length binary string

1 to 8,000 bytes

1 to 8,000 bytes

OxA5F2

varbinary (max)

New large value variable-length binary string

1 to 231-1 bytes

16 + 0 to 2GB

OxA5F22362F282

image

Large value variable-length binary string from earlier versions

1 to 231-1 bytes

16 + 0 to 2GB

OxA5F22362F282

time stamp

Database-wide unique number

n/a

8 bytes

n/a

unique identifier

Globally unique identifier (GUID)

n/a

16 bytes

Special

cursor

Cursor reference

n/a

n/a

n/a

sql_variant

Variant

n/a

n/a

n/a

table

Table

n/a

n/a

n/a

xml

XML string

1 to 231 - 1 chrs

1 6 bytes + 0 to 2GB

<PartID>23151-123123123</PartID>




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net