Pr.Pg Next Pg

SQL 2012 common data types tutorials

 

Data Types

CHARACTER DATA TYPES * * * Description

Size in Bytes

Char(n)

Fixed-length character data up to 8,000 characters long using collation character set

Defined length

* 1 byte

Nchar(n)

Unicode fixed-length character data, up to maximum of 4,000 bytes

Defined length

* 2 bytes

VarChar(n)

Variable-length character data up to 8,000 characters long 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

1 byte per

Character

 

  • Unicode data types are very useful for storing multilingual data.

  • Use Unicode data when the database might use foreign languages; otherwise, use char, varchar, or text.

 

Data Types

NUMERIC DATA TYPES * * * Description

Size in Bytes

Bit

1 or 0

1 Bit

Tinyint

Integer from 0 to 255

1 Byte

Smallint

Integer from -32768 to 32767

2 Bytes

Int

Integers from -2,147,483,648 to 2,147,483,647

4 bytes

Bigint

Integers from -2 ˆ 63 to 2 ˆ 63-1

8 bytes

Decimal or Numeric

Fixed-precision numbers up to -10 ˆ 38 + 1

Varies as per

Length

Money

Numbers from -2 ˆ 63 to 2 ˆ 63, accuracy to one

ten-thousandths (.0001)

8 bytes

 

SmallMoney

Numbers from -214,748.3648 through

+214748.3647, accuracy to ten thousandths (.0001)

4 bytes

 

Float

Floating-point numbers ranging from -1.79E + 308

through 1.79E + 308, depending on the bit precision

4 or 8 bytes

 

Real

Float with 24-bit precision

4 bytes

 

 

DATA TYPE

RANGE OF VALUES

ACCURACY

Size in Bytes

SMALLDATETIME

01/01/1900 to 06/06/2079

1 minute

4 bytes

DATETIME

01/01/1753 to 12/31/9999

0.00333 seconds

8 bytes

DATETIME2

01/01/0001 to 12/31/9999

100 nanoseconds

6 to 8 bytes

DATETIMEOFFSET

01/01/0001 to 12/31/9999

100 nanoseconds

8 to 10 bytes

DATE

01/01/0001 to 12/31/9999

1 day

3 bytes

TIME

00:00:00.0000000 to

23:59:59.9999999

100 nanoseconds

3 to 5 bytes

 

 

XML Data Type

  • The XML data type allows you to store and manipulate Extensible Markup Language (XML) documents natively.

  • When storing XML documents, you are limited to a maximum of 2 GB, as well as a maximum of 128 levels within a document.

 


 

Pr.Pg border                                              Next Pg