Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Datatypes in SQL Server 2005

Posted by Ravi Varma Thumati on May 18, 2009

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 incorrect 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 data type 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.

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 accurate information, so in this case you can store Datetime in smalldatetime datatype and saves the extra 2 bytes storage space. Example: A DOJ detail of employee doesn’t require such an accuracy level so you can use smalldatetime datatype.

Choosing between char, varchar, varchar(MAX) and text datatype in SQL Server

char, varchar, varchar(MAX) and text datatype are used for storing character string. Note you cannot store multilingual data. I.e. you will not allow storing 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, varchar and varchar(MAX) datatype

Char stores fixed-length character data while varchar stores variable-length character data.
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

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, nvarchar and nvarchar(MAX) datatype

nchar stores fixed-length unicode character data while nvarchar stores variable-length unicode character data.

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.

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.

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

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

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

 

Advertisements

3 Responses to “Datatypes in SQL Server 2005”

  1. We really appreciate your blog post. You will discover a lot of techniques we could put it to very good use by means of little or no effort in time and hard earned money. Thank you so much for helping have the post respond to many queries we have encountered before now.

  2. Thanks for every other great post. Where else may anyone get that kind of information in such a perfect approach of writing? I’ve a presentation next week, and I’m at the look for such info.

  3. I got what you intend,bookmarked , very decent site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: