Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 12, 2001

SQL Server 7.0 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 many new features in SQL Server 7.0, which not present in the previous version. There are: autogrow features, new storage engine, complete row level locking and so on. Some changes were also made in the datatypes: five new datatypes were added, and four datatypes were changed.

These datatypes were added:

  • nchar
  • nvarchar
  • ntext
  • cursor
  • uniqueidentifier

These datatypes were changed:

  • char
  • varchar
  • binary
  • varbinary

In this article, I want to tell you about built-in SQL Server 7.0 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)]
  • varchar[(n)]

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
  • smalldatetime

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 7.0. 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)]
  • real

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 three integer datatypes:

  • tinyint
  • smallint
  • int

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.

Monetary datatypes

Monetary datatypes are usually used to store monetary values. There are two monetary datatypes:

  • money
  • smallmoney

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
  • 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.

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 7.0 data.

There are three datatypes in this category:

  • text
  • ntext
  • image

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)]
  • nvarchar[(n)]

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 7.0 datatypes.

This is the syntax of sp_addtype stored procedure:

sp_addtype typename, phystype [, nulltype]

where

typename
is the User-Defined datatype name.
phystype
is the SQL Server 7.0 datatype, on which the User-Defined datatype is based.
nulltype
is the NULL or NOT NULL.


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date