## SQLWays - Microsoft SQL Server Data Types

This chapter describes the data types supported by Microsoft SQL Server.

- char, nchar, varchar and nvarchar
- bigint, int, smallint and tinyint
- datetime and smalldatetime
- money and smallmoney
- decimal and numeric
- float and real
- text and ntext
- binary, varbinary and image
- bit, uniqueidentifier and timestamp

### char, nchar, varchar and nvarchar

char[(n)]

Fixed-length non-Unicode character data with length of n characters. n must be a value from 1 through 8,000. Storage size is n bytes.

nchar[(n)]

Fixed-length Unicode character data with length of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes.

varchar[(n)]

Variable-length non-Unicode character data with length of n characters. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered, not n bytes. The data entered can be 0 characters in length.

nvarchar[(n)]

Variable-length Unicode character data with length of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length.

Remarks

When n is not specified in a data definition, the default length is 1.

### bigint, int, smallint and tinyint

bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

The bigint data type is available in SQL Server 2000 or later.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes.

smallint

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

### datetime and smalldatetime

Date and time data types for representing date and time of day.

datetime

Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.

smalldatetime

Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.

### money and smallmoney

Monetary data types for representing monetary or currency values.

money

Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.

smallmoney

Monetary data values from - 214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes.

### decimal and numeric

Numeric data types with fixed precision and scale.

decimal[(p[,s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1.

p (precision) specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38.

s (scale) specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

numeric[(p[,s])]

A synonym for decimal.

### float and real

Approximate number data types for use with floating point numeric data. Floating point data is approximate; not all values in the data type range can be precisely represented.

float[(n)]

Floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.

real

Floating point number data from -3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).

### text and ntext

Variable-length character strings.

text

Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31 - 1 (2,147,483,647) characters. Storage size is the actual length in bytes of the data entered.

ntext

Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered.

### binary, varbinary and image

Binary data types.

binary[(n)]

Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes. When n is not specified in a data definition, the default length is 1.

varbinary[(n)]

Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. When n is not specified in a data definition, the default length is 1.

image

Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.

### bit, uniqueidentifier and timestamp

bit

Integer data type that can be 1, 0, or NULL.

uniqueidentifier

A globally unique identifier (GUID). A column of uniqueidentifier data type can be initialized using the NEWID function or converting from a string constant in the following form: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or A-F.

timestamp

A database-wide unique number that gets updated every time a row gets updated. The value of a timestamp column is unique within a database. The storage size is 8 bytes.

[Main] [Table of Contents]
Copyright © 1999-2003 Ispirer Systems. All Rights Reserved.
SQLWays™ www.ispirer.com.