Sunday, January 30, 2011

SQL 2005 - Column Data Types to Store Date Time, Integers, Strings, Real Numbers and Images

1. Introduction

SQL server provides a different kind of data types. Sometimes, confusion may arrive while making a correct choice among them. In this article, I will just cover different data types available in SQLServer. You need to know the data types and that will help you in designing the database more efficiently.

2. To Store Characters

  1. Char data type stores maximum of 8000.
  2. Varchar(Num) data type stores a variable length of characters.  Here the "num" states how much maximum allowed. If you specify varchar(10) then the maximum size allowed is 10 characters. This data type also shrinks the allocated space based on actual data. Say if you store “Server” in the column, which has the varchar(10), then the remaining 5 are given to the pool of memory, as it is not used.
  3. Text data type is capable of storing a large number of characters which is almost 2 billion. 
  4. Varchar(Max) data type acts same as the text data type. This data type is introduced in SQL Server 2005. You may wonder why it is introduced when already Text is there? You cannot use text data type directly in the SQL statements. One should use corresponding text data type manipulation like readtext. But, with Varchar(max), you can use it as part of your query without depending on the built-in functions for text manipulation

To Support Unicode characters just append ‘n’ with the above data types. Say if you need Unicode support for the char data type, the data type of your choice is nChar.

3. Numeric datatypes - Whole numbers

  1. The bit data type is used for the Boolean stuff. It can hold either 0 or 1.
  2. TinyInt data type is capable of storing 0 to 255 numbers.
  3. SmallInt data type is capable of storing the numbers up to 32K both on a positive and negative side.
  4. Int data type is capable of storing 0 – 2 billion. To store more than 2 billion you can use Bigint data type.

4. Numeric datatypes - Real Numbers

  1. You can use Real or Float data types to store the fractional numbers. Real data type takes 4 bytes and Float takes 8 bytes.
  2. Money is the data type, which supports to very big numbers. It can store upto nine trillions.
  3. Numeric data type allows you to specify how many bytes should be allocated for the decimal portion. For example Numeric(8,2) tells that total digits allowed are eight and in which two digits are for the decimal portion.

5. Date DataTypes

  1. DateTime data type is used to store both the date and the time. 4 bytes allocated for the actual date and 4 bytes allocated for the time.
  2. SmallDateTime data type is useful for storing the date and time that spans for short range say just for example 1950 to 2200 like 100 to 200 years. Totally 4 bytes consumed by this data type.

6. Binary datatypes

These data types are useful for storing the binary information. It can be the entire crystal report file in the form of binary format or raw bits of a photo image or whatever it is.

  1. Binary data type is fixed in size, consumes 8KB of memory.
  2. VarBinary(n) stores the allocated storage for the binary data specified by n bytes. Maximum of n is 8000
  3. Image data type is specially designed to store the Images.

Note: With an addition to the above data types, SQL programming constructs can use the types like TABLE, Cursor and XML.

1 comment:

Leave your comment(s) here.

Like this site? Tell it to your Firend :)