March 19, 2001
SQL Server 2000 Datatypes
By Sergey Vartanyan
- Introduction
- Binary datatypes
- Character datatypes
- Date and Time datatypes
- Numeric datatypes
- Integer datatypes
- Monetary datatypes
- Special datatypes
- Text and image datatypes
- Unicode Character datatypes
- User-Defined datatypes
Introduction
There are three new datatypes in SQL Server 2000 in comparison with
SQL Server 7.0.
These datatypes were added:
In this article, I want to tell you about built-in SQL Server 2000
datatypes, about user-defined datatypes and what datatype is generally
used in a particular situation.
Binary datatypes
Binary data is similar to hexadecimal data and consists of the
characters 0 through 9 and A through F, in groups of two characters
each. You should specify 0x before binary value when input it.
There are two binary datatypes:
- binary[(n)]
- varbinary[(n)]
Binary[(n)] datatype can store up to 8000 bytes of fixed-length binary
data. You can specify the maximum byte length with n.
Varbinary[(n)] datatype can store up to 8000 bytes of variable-length
binary data. You can specify the maximum byte length with n.
Variable-length means that binary data can contain less than n bytes,
and the storage size will be the actual length of the data entered.
You should use varbinary datatype instead of binary datatype, when you
expect null values or a variation in data size.
Character datatypes
Character datatypes are used to store any combination of letters,
symbols, and numbers. You should enclose character data with quotation
marks, when enter it.
There are two character datatypes:
Char[(n)] datatype can store up to 8000 bytes of fixed-length character
data. You can specify the maximum byte length with n.
Varchar[(n)] datatype can store up to 8000 bytes of variable-length
character data. You can specify the maximum byte length with n.
Variable-length means that character data can contain less than n bytes,
and the storage size will be the actual length of the data entered.
You should use varchar datatype instead of char datatype, when you
expect null values or a variation in data size.
Date and Time datatypes
There are two datetime datatypes:
Datetime is stored in 8 bytes of two 4-byte integers: 4 bytes for
the number of days before or after the base date of January 1, 1900,
and 4 bytes for the number of milliseconds after midnight.
Datetime datatype can store dates from January 1, 1753, to
December 31, 9999, with accuracy of 3.33 milliseconds.
If you will not specify date portion of the datetime value, then
January 1, 1900 is supplied; if you will not specify time portion of
datetime value, then 12:00:00:000AM is supplied.
Smalldatetime is stored in 4 bytes of two 2-byte integers: 2 bytes for
the number of days after the base date of January 1, 1900, and 2 bytes
for the number of minutes after midnight.
Smalldatetime datatype can store dates from January 1, 1900, to
June 6, 2079, with accuracy to the minute.
If you will not specify date portion of the datetime value, then
January 1, 1900 is supplied; if you will not specify time portion of
datetime value, then 12:00AM is supplied.
Numeric datatypes
There are two kinds of the numeric datatypes:
- Exact Numeric Data
- Approximate Numeric Data
The difference between Exact Numeric Data and Approximate Numeric Data
in that Exact Numeric Data can store all decimal numbers with complete
accuracy, and Approximate Numeric Data cannot.
Exact Numeric Data are:
- decimal[(p[, s])]
- numeric[(p[, s])]
The decimal and numeric datatypes are synonyms in the SQL Server 2000.
Exact Numeric Data holds values from 10^38 - 1 through - 10^38 - 1.
The storage size varies based on the specified precision, and it ranges
from a minimum of 2 bytes to a maximum of 17 bytes.
p - is a precision, that specify the maximum total number of decimal
digits that can be stored, both to the left and to the right of the
decimal point. The maximum precision is 28 digits.
s - is a scale, that specify the maximum number of decimal digits that
can be stored to the right of the decimal point, and it must be less
than or equal to the precision.
Approximate Numeric Data are:
Float[(n)] datatype is stored in 8 bytes and is used to hold positive
or negative floating-point numbers. By default, this column has a
15-digit precision. Float[(n)] datatype can store positive values from
2.23E-308 to 1.79E308 and negative values from -2.23E-308 to -1.79E308.
Real datatype is stored in 4 bytes and is used as float datatype to
hold positive or negative floating-point numbers. This column has a
7-digit precision. Real datatype can store positive values from
1.18E-38 to 3.40E38 and negative values from -1.18E-38 to -3.40E38.
Integer datatypes
There are four integer datatypes:
- tinyint
- smallint
- int
- bigint
Tinyint is stored in 1 byte and is used to hold integer values
from 0 through 255.
Smallint is stored in 2 bytes and is used to hold integer values
from -32768 through 32,767.
Int is stored in 4 bytes and is used to hold integer values
from -2147483648 through 2147483647.
Bigint is stored in 8 bytes and is used to hold integer values
from -9223372036854775808 through 9223372036854775807.
Monetary datatypes
Monetary datatypes are usually used to store monetary values.
There are two monetary datatypes:
Money datatype is stored in 8 bytes and is used to hold monetary
values from -922337203685477.5808 through 922337203685477.5807.
Smallmoney datatype is stored in 4 bytes and is used to hold monetary
values from - 214748.3648 through 214748.3647.
Special datatypes
These are the special datatypes:
- bit
- cursor
- sql_variant
- table
- timestamp
- uniqueidentifier
Bit datatype is usually used for true/false or yes/no types of data,
because it holds either 1 or 0. All integer values other than 1 or 0
are always interpreted as 1. One bit column stores in 1 byte, but
multiple bit types in a table can be collected into bytes.
Bit columns cannot be NULL and cannot have indexes on them.
Cursor datatype is used for variables or stored procedure OUTPUT
parameters that contain a reference to a cursor. The variables created
with the cursor data type are nullable.
You cannot use this datatype for a column in a CREATE TABLE statement.
sql_variant datatype is used to store values of various SQL Server
supported data types, except text, ntext, timestamp, and sql_variant.
The maximum length of sql_variant datatype is 8016 bytes.
You can store in one column of type sql_variant the rows of different
data types, for example int, char, and varchar values.
This is the example of using sql_variant datatype:
SET NOCOUNT ON
GO
if object_id('tbTest') is not null drop table tbTest
GO
CREATE TABLE tbTest (
id int primary key,
sql_v sql_variant
)
GO
INSERT INTO tbTest VALUES (1, 1)
INSERT INTO tbTest VALUES (2, 1.0)
INSERT INTO tbTest VALUES (3, '1')
INSERT INTO tbTest VALUES (4, 0x01)
GO
SELECT sql_v FROM tbTest
GO
Here is the result set:
sql_v
------------------------
1
1.0
1
0x01
Table datatype is used to store a result set for later processing.
You cannot use this datatype for a column in a CREATE TABLE statement.
You should use DECLARE @local_variable to declare variables of type
table. Table variables should be used instead of temporary tables, whenever
possible, because table variables are cleaned up automatically at the
end of the function, stored procedure, or batch in which they are
defined, and table variables require less locking and logging resources.
This is the example of using table datatype:
DECLARE @tbl table (id int)
INSERT INTO @tbl VALUES (1)
INSERT INTO @tbl VALUES (2)
SELECT * FROM @tbl
Here is the result set:
id
-----------
1
2
Timestamp datatype is stored in 8 bytes as binary(8) datatype.
The timestamp value is automatically updated every time a row
containing a timestamp column is inserted or updated.
Timestamp value is a monotonically increasing counter whose
values will always be unique within a database and can be selected
by queried global variable @@DBTS.
Uniqueidentifier is a GUID (globally unique identifier).
A GUID is a 16-byte binary number that is guaranteed to be unique
in the world. This datatype is usually used in replication or as
primary key to unique identify rows in a table.
You can get the new uniqueidentifier value by calling the NEWID
function.
Note You should use IDENTITY property instead of uniqueidentifier,
if global uniqueness is not necessary, because the uniqueidentifier
values are long and more slowly generated.
Text and image datatypes
Text and image data are stored on the Text/Image pages, not on the
Data pages as other SQL Server 2000 data.
There are three datatypes in this category:
Text datatype is a variable-length datatype that can hold up to
2147483647 characters. This datatype is used when you want to store
the character values with the total length more than 8000 bytes.
ntext datatype is a variable-length unicode datatype that can
hold up to 1073741823 characters. This datatype is used when you want
to store the variable-length unicode data with the total length more
than 4000 bytes.
Image datatype is a variable-length datatype that can hold up to
2147483647 bytes of binary data. This datatype is used when you want
to store the binary values with the total length more than 8000 bytes.
This datatype is also used to store pictures.
Unicode Character datatypes
A column with unicode character datatype can store all of the characters
that are defined in the various character sets, not only the characters
from the particular character set, which was chosen during SQL Server
Setup. Unicode datatypes take twice as much storage space as non-Unicode
datatypes.
The unicode character data, as well as character data, can be used
to store any combination of letters, symbols, and numbers. You should
enclose unicode character data with quotation marks, when enter it.
There are two unicode character datatypes:
nchar[(n)] datatype can store up to 4000 bytes of fixed-length unicode
character data. You can specify the maximum byte length with n.
nvarchar[(n)] datatype can store up to 4000 bytes of variable-length
unicode character data. You can specify the maximum byte length with n.
Variable-length means that character data can contain less than n bytes,
and the storage size will be the actual length of the data entered.
You should use nvarchar datatype instead of nchar datatype, when you
expect null values or a variation in data size.
User-Defined datatypes
You can create your own User-Defined datatypes by executing sp_addtype
system stored procedure. Once a User-Defined datatype is created, you
can use it in the CREATE TABLE or ALTER TABLE statements, as built-in
SQL Server 2000 datatypes.
This is the syntax of sp_addtype stored procedure:
sp_addtype [ @typename = ] type,
[ @phystype = ] system_data_type
[ , [ @nulltype = ] 'null_type' ]
[ , [ @owner = ] 'owner_name' ]
where
typename - is the User-Defined datatype name.
phystype - is the SQL Server 2000 datatype, on which the User-Defined
datatype is based.
nulltype - is the NULL or NOT NULL.
owner - is the owner of the User-Defined datatype.
IE 7
Firefox 2.0
