Datatypes in SQL Server 2005

Lets begin with easy series of understanding SQL Server 2005.

If you are begineer and want to gain Database and RDBMS knowledge in seconds this SQL Tutorial

Why choosing datatype is important or what the disadvantage of choosing wrong datatype?

• Choosing too small datatype cannot meet the system need and at some point your system may become useless. Example use of smalldatetime (2 bytes) datatype over datetime (4 bytes) datatype can make your system useless when you need to provide information based on millisecond data.


• Choosing too large datatype can waste space and increase expense. Example: let say choosing of smallint to store age data is wastage of space as same information can be stored in tinyint and serves the need. So here we can save space and money by allocating tinyint (1 byte) over smallint (2 bytes).


• Choosing incorect datatype can degrades performance, because of conversion of data. Example choosing a char datatype to store datetime information will degrades performance as it requires frequent boxing and unboxing of type.

Choosing between tinyint, smallint, int, bigint datatype in SQL Server 2005

You should use int datatype to store exact number. Now depends on chart shown below you and your maximum and minimum data range consideration you can able to decide which is better to go with.

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

Choosing between datetime and smalldatetime datatype in SQL Server 2005
datetime datatype are used for storing date and time information.

Comparision chart

Data type	Range	Storage/Accuracy
datetime	January 1, 1753, through December 31, 9999	4 Bytes/3.33 milliseconds
smalldatetime	January 1, 1900, through June 6, 2079	2 Bytes/1 minute

Difference between datetime and smalldatetime datatype in SQL Server 2005
datetime is more accurate than smalldatetime datatype. datetime datatype stores milliseconds data while smalldatetime datatype doesn't store millisecond data and thus it is less accurate. But in most cases we don't require such a accurate information, so in this case you can storing datetime in smalldatetime datatype and saves the extra 2 bytes storage space. Example: DOJ details of employee doesn't require such a accuracy level so you can use smalldatetime datatype.


Choosing between char, varchar, varchar(MAX) and text datatype in SQL Server 2005
char, varchar, varchar(MAX) and text datatype are used for storing character string. Note you cannot store multilingual data. i.e. You will not allowed to store japanes or hindi characters.

Char datatype

• Stores character (non-unicode) data.
  
• Stores data in fixed-length format.
• Choose char datatype when size of data is consistent.

Varchar datatype

• Stores Character (non-unicode) data.
• Stores data in variable-length format.
• Choose varchar datatype when size of data varies between 8000 bytes

Varchar(MAX) datatype

• Stores Character (non-unicode) data.
• Stores data in variable-length format.
• Choose varchar datatype when size of data varies and it may exceeds 8000 bytes.

Text datatype

• Text datatype is used in previous versions of SQL Server, it is recommended to use Varchar(MAX) instead of Text datatype, as ntext, text, and image data types will be removed in a future version of Microsoft SQL Server
• Stores large amount of character string
• Stores data in variable-length format.
• Choose text datatype when size of data varies may exceeds 8000 bytes.
  

Difference between char and varchar datatype
char stores fixed-length character data while varchar stores variable-length character data.

Difference between varchar and varchar(MAX) datatype
varchar stores variable-length character data whose range varies upto 8000 bytes, varchar(MAX) stores variable-length character data whose range may varies beyond 8000 bytes.

Should i choose varchar(MAX) or text datatype
They both provides same functionality, but it is better to choose varchar(MAX) over text datatype as text data types will be removed in a future version of Microsoft SQL Server.


Choosing between nchar, nvarchar, nvarchar(MAX) and ntext datatype in SQL Server 2005
nchar, nvarchar, nvarchar(MAX) and ntext datatype are used for storing unicode character string. you can store multilingual data. i.e. You can store japanes or hindi characters.

nChar datatype

• Stores unicode character data.
  
• Stores data in fixed-length format.
• Choose nchar datatype when size of data is consistent and you want to store multi-lingual data.
  

nVarchar datatype

• Stores unicode Character data.
• Stores data in variable-length format.
• Choose nvarchar datatype when size of data varies upto 4000 bytes and data can be multi-lingual.
  

nVarchar(MAX) datatype

• Stores unicode Character data.
• Stores data in variable-length format.
• Choose nvarchar datatype when size of data varies and it may exceeds 4000 bytes and data can be multi-lingual.
  

nText datatype

• nText datatype is used in previous versions of SQL Server, it is recommended to use nVarchar(MAX) instead of Text datatype, as ntext, text, and image data types will be removed in a future version of Microsoft SQL Server
• Stores large amount of character string
• Stores data in variable-length format.
• Choose ntext datatype when size of data varies may exceeds 4000 bytes and data can be multi-lingual.
  

Difference between nchar and nvarchar datatype
nchar stores fixed-length unicode character data while nvarchar stores variable-length unicode character data.

Difference between nvarchar and nvarchar(MAX) datatype
varchar stores variable-length unicode character data whose range varies upto 4000 bytes, varchar(MAX) stores variable-length unicode character data whose range may varies beyond 4000 bytes.

Should i choose varchar(MAX) or text datatype
They both provides same functionality, but it is better to choose varchar(MAX) over text datatype as text data types will be removed in a future version of Microsoft SQL Server.


Difference between

• char and nchar
• varchar and nvarchar
• varchar(MAX) and nvarchar(MAX) and
• text and ntext datatype
  

• Former can stores non-unicode character strings while latter can stores unicode character string.
• Former require less memory, while latter requires doubles as much memory as former. i.e. char can stores upto 8000 bytes while nchar can stores upto 4000 bytes, because regular data are of 8bits while unicode data require 16bits storage space.
  
• Example: Former is used to store character data which doesn't suppor multi-language support, while latter can support character multi-language character support.
• For more detail
  

Choosing between numeric and decimal datatype in SQL Server 2005
Numeric and decimal datatype both are used when the data have fixed precision and scale.
Precision - Maximum total digit that can be stored on both left and right of decimal point.
Scale - Mamimum total digit that can be stored on right of decimal point.
There is no functional difference between numeric and decimal datatype. For more detail


Choosing between money and smallmoney datatype in SQL Server 2005

Money and smallmoney datatype are used for storing monetary data.

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

Choosing between float and real datatype in SQL Server 2005
Both float and real datatype are approximate datatype. i.e. Not all values in datatype can be represented exactly.

Data type	Range	Storage
float	- 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308	Depends on the value of n
real	- 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38	4 Bytes

syntax of float [ ( n ) ]

SQL Server 2005 treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53.

n value	Precision	Storage size
1-24	7 digits	4 bytes
25-53	15 digits	8 bytes

Choosing between binary, varbinary and varbinary(MAX) datatype in SQL Server 2005

• Use binary when the sizes of the column data entries are consistent.
• Use varbinary when the sizes of the column data entries vary considerably.
• Use varbinary(max) when the column data entries exceed 8,000 bytes.

For more details


bit datatype - bit datatype can take a value of 1, 0, or NULL. It consumes 1 byte. Note: The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.


xml datatype - SQL Server 2005 introduces a native data type called XML. By using this feature, you can create a table with one or more columns of type XML besides relational columns. These XML values are stored in an internal format as large binary objects (BLOB), which enable the XML model to support document order and recursive structures. Because of the seamless integration of the XML data type with SQL Server's storage and programming models, you can query and update XML documents and even write joins between XML data and relational data in your database. SQL Server 2005 uses the same query engine and optimizer for querying XML data as it does for relational data, radically enhancing your ability to work with XML data. For more reading on xml datatype