BaseNow

 

  Search
   

  Help

Screen shots

Compare editions

Online help

Quick-start videos

System requirements

License agreement

Known issues

  Support

 Forums

Technical support

Feedback

 About the author

 Company

Contact

Privacy policy

  Features

BaseNow architecture

Database tools

BaseNow Agent

Tables and views

SQL Query Builder

Import/export data

  Subscribe

Stay informed about any major changes. Click here to subscribe

 

Data types in Microsoft SQL Server

Binary Data

Binary data consists of hexadecimal numbers. For example, the decimal number 245 is hexadecimal F5. Binary data is stored using the binary, varbinary, and image data types in Microsoft® SQL Server™ 2000. A column assigned the binary data type must have the same fixed length (up to 8 KB) for each row. In a column assigned the varbinary data type, entries can vary in the number of hexadecimal digits (up to 8 KB) they contain. Columns of image data can be used to store variable-length binary data exceeding 8 KB, such as Microsoft Word documents, Microsoft Excel spreadsheets, and images that include bitmaps, Graphics Interchange Format (GIF), and Joint Photographic Experts Group (JPEG) files.

In general, use varbinary for storing binary data, unless the length of the data exceeds 8 KB, in which case you should use image. It is recommended that the defined length of a binary column be no larger than the expected maximum length of the binary data to be stored.

Character Data

Character data consists of any combination of letters, symbols, and numeric characters. For example, valid character data includes "928", "Johnson", and "(0*&(%B99nh  jkJ". In Microsoft® SQL Server™ 2000, character data is stored using the char, varchar, and text data types. Use varchar when the entries in a column vary in the number of characters they contain, but the length of any entry does not exceeds 8 kilobytes (KB). Use char when every entry for a column has the same fixed length (up to 8 KB). Columns of text data can be used to store ASCII characters longer than 8 KB. For example, because HTML documents are all ASCII characters and usually longer than 8 KB, they can be stored in text columns in SQL Server prior to being viewed in a browser.

It is recommended that the defined length of a character column be no larger than the maximum expected length of the character data to be stored.

To store international character data in SQL Server, use the nchar, nvarchar, and ntext data types.

Unicode Data

Traditional non-Unicode data types in Microsoft® SQL Server™ 2000 allow the use of characters that are defined by a particular character set. A character set is chosen during SQL Server Setup and cannot be changed. Using Unicode data types, a column can store any character defined by the Unicode Standard, which includes all of the characters defined in the various character sets. Unicode data types take twice as much storage space as non-Unicode data types.

Unicode data is stored using the nchar, nvarchar, and ntext data types in SQL Server. Use these data types for columns that store characters from more than one character set. Use nvarchar when a column's entries vary in the number of Unicode characters (up to 4,000) they contain. Use nchar when every entry for a column has the same fixed length (up to 4,000 Unicode characters). Use ntext when any entry for a column is longer than 4,000 Unicode characters.

Date and Time Data

Date and time data consists of valid date or time combinations. For example, valid date and time data includes both "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01 AM 8/17/98". Date and time data is stored using the datetime and smalldatetime data types in Microsoft® SQL Server™ 2000. Use datetime to store dates in the range from January 1, 1753 through December 31, 9999 (requires 8 bytes of storage per value). Use smalldatetime to store dates in the range from January 1, 1900 through June 6, 2079 (requires 4 bytes of storage per value).

Numeric Data

Numeric data consists of numbers only. Numeric data includes positive and negative numbers, decimal and fractional numbers, and whole numbers (integers).

Integer Data

Integer data consists of negative or positive whole numbers, such as -15, 0, 5, and 2509. Integer data is stored using the bigint, int, smallint, and tinyint data types in Microsoft® SQL Server™ 2000. The bigint data type can store a larger range of numbers than the int data type. The int data type can store a larger range of integers than smallint, which can store a larger range of numbers than tinyint.

Use the bigint data type to store numbers in the range from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

Use the int data type to store numbers in the range from -2,147,483,648 through 2,147,483,647 only (requires 4 bytes of storage per value).

Use the smallint data type to store numbers in the range from -32,768 through 32,767 only (requires 2 bytes of storage per value), and the tinyint data type to store numbers in the range from 0 through 255 only (requires 1 byte of storage per value).

Decimal Data

Decimal data consists of data that is stored to the least significant digit. Decimal data is stored using decimal or numeric data types in SQL Server. The number of bytes required to store a decimal or numeric value depends on the total number of digits for the data and the number of decimal digits to the right of the decimal point. For example, more bytes are required to store the value 19283.29383 than to store the value 1.1.

In SQL Server, the numeric data type is equivalent to the decimal data type.

Monetary Data

Monetary data represents positive or negative amounts of money. In Microsoft® SQL Server™ 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead.

Special Data

Special data consists of data that does not fit any of the categories of data such as binary data, character data, Unicode data, date and time data, numeric data and monetary data. Microsoft® SQL Server™ 2000 includes four types of special data:

timestamp

Is used to indicate the sequence of SQL Server activity on a row, represented as an increasing number in a binary format. As a row is modified in a table, the timestamp is updated with the current database timestamp value obtained from the @@DBTS function. timestamp data is not related to the date and time of an insert or change to data. To automatically record times that data modifications take place in a table, use either a datetime or smalldatetime data type to record the events and triggers.

Note  In SQL Server, rowversion is a synonym for timestamp.

bit

Consists of either a 1 or a 0. Use the bit data type when representing TRUE or FALSE, or YES or NO. For example, a client questionnaire that asks if this is the client's first visit can be stored in a bit column.

uniqueidentifier

Consists of a 16-byte hexadecimal number indicating a globally unique identifier (GUID). The GUID is useful when a row must be unique among many other rows. For example, use the uniqueidentifier data type for a customer identification number column to compile a master company customer list from multiple countries.

sql_variant

A data type that stores values of various SQL Server–supported data types, except text, ntext, timestamp, image, and sql_variant.

table

A special data type used to store a result set for later processing. The table data type can be used only to define local variables of type table or the return value of a user-defined function.

user-defined

Allows a user-defined data type, product_code, for example, that is based on the char data type and defined as two uppercase letters followed by a five-digit supplier number.


Related Topics:

How to change columns in an existing table

Autonumbering and identifier columns

Column Data Types

About tables

Indexes and primary keys

Relationships

[Home] [Download] [Screen Shots] [Details] [Support] [Purchase]

Copyright © 1997-2005 Gate Comm Software. All Rights Reserved.