SQL server DataTypes
Each column in a SQL Server Table can only contain one specific predefined type of data, such as characters or numbers. This declaration is called a Data Type. In this article, we’ll compare and contrast the various SQL Server 2008 Data Types. In addition, we’ll explore which Data Types are the best solutions for specific situations. There are over thirty-five different Data Types in SQL Server 2008.
Categories
Microsoft classifies the various Data Types into the following seven broader categories: Exact Numbers, Approximate Numbers, Date and Times, Character Strings, Unicode Character Strings, Binary Stings, and Other data types.
|
Data type
|
Range
|
Storage
|
|
bigint
|
-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
|
8 Bytes
|
|
int
|
-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
|
4 Bytes
|
|
smallint
|
-2^15 (-32,768) to 2^15-1 (32,767)
|
2 Bytes
|
|
tinyint
|
0 to 255
|
1 Byte
|
Bit:- The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or less bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.
The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
Decimal and numeric :-
Numeric data types that have fixed precision and scale.
- decimal [ (p[ ,s] )] and numeric[ (p[ ,s] )]
-
Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
- p (precision)
-
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 of 38. The default precision is 18.
- s (scale)
-
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. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision
Strings
This section will begin with a discussion about Character Stings: Char, VarChar, and Text. A Char (Character) Data Type can hold letters, numbers, and keyboard characters. When a Char is defined, its max length, up to 8,000, is also fixed. Think of a Char as holding anything you can type into Notepad--and just like Notepad, if you enter numbers into a Char column, math cannot be performed on them. They are treated as text characters, not numbers. Typically, Char columns are used when the numbers of characters entered into each row are approximately the same, such as a zip code or phone number. If the length of the data will vary from row to row, such as an email address, then use VarChar. A VarChar is a Char of variable (Var) length. When a VarChar is created, its max length is also defined. A main reason to use a VarChar rather than a Char is the amount of physical disk space used by each. In the following example, a Table was created with one Char column and one VarChar column. Both were set to a maximum size of 50.
Money and smallmoney
|
Data type
|
Range
|
Storage
|
|
money
|
-922,337,203,685,477.5808 to 922,337,203,685,477.5807
|
8 bytes
|
|
smallmoney
|
- 214,748.3648 to 214,748.3647
|
4 bytes
|
Unicode
Unicode is a standard method that allows applications to record characters from languages other than our own. Unicode comes in handy when you’re building multilingual applications or international web sites. The Char and VarChar data types can both be configured to allow Unicode by prefacing them with the letter ‘n’, as in nChar and nVarChar. The price of this flexibility is increased disk space usage. As a ballpark figure, plan on Unicode to be double in disk space. Full details of Unicode can be found on the Unicode Consortium home page: http://unicode.org/ .
Binary Stings
Binary stings are used for saving non-character data, such as images and audio. There are two data types used for this: Binary and VarBinary. They configure like Char and VarChar. VarBinary(Max) can be used to store files of unlimited size. There is also a legacy data type called Image, but it will be discontinued in a future version of SQL Server.
Other Data Types
There are seven Other Data Types including Cursor, HierachyID, SQL Variant, Table, TimeStamp, UniqueIdentifier, and XML. The data type TimeStamp has been replaced by RowVersion. UniqueIdentifier is a unique GUID. The data type SQL Variant can be used when you don’t know what type of data to expect. It’s basically 8,000 bytes of anything goes storage. If you’re using XML, use the actual XML data type rather than a VarChar. The XML type allows binding to collections.
Dates and Times
SQL Server 2008 includes new date and time Data Types.
Related Alrticles