SQL SERVER 2008 – Data Types in SQL SERVER 2008

Data Types

In a Database, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, data and time data, binary strings, and so on.

Integer Types: To hold the Integer values it provides with tinyint, smallint, int and bigint data types with sizes 1, 2, 4 and 8 bytes respectively.

Boolean Type: To hold the Boolean values it provides with bit data type that can take a value of 1, 0, or NULL.

Note: The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Decimal Types: To hold the decimal values it provides with the following types:

decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

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.

Storage sizes of Decimal and Numeric types vary, based on the precision.

Precision Storage bytes
1 – 9 5
10-19 9
20-28 13
29-38 17

Note: numeric is functionally equivalent to decimal.

-float [ ( n ) ] and real

-Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

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

Monetary or Currency Types: To hold the Currency values it provides with the following types which takes a scale of 4 by default:

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

Date and Time Values: To hold the Date and Time values of a day it provides with the following types:

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

Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. The Database Engine stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight.

String Values: To hold the string values it provides with the following types:

char [ ( n ) ]

Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes.

varchar [ ( n | max ) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes.

text

It was equal to varchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varchar(max) instead.

Unicode Data types for storing Multilingual Characters are nchar, nvarchar and ntext where n stands for national.

nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes.

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes.

ntext

It was equal to nvarchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use nvarchar(max) instead.

Binary Values: To hold the binary values likes images, audio clips and video clips we use the following types.

binary [ ( n ) ]

Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

varbinary [ ( n | max) ]

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes.

Image

It was equal to varbinary(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varbinary(max) instead.

  • Use char, nchar, binary when the sizes of the column data entries are consistent.
  • Use varchar, nvarchar, varbinary when the sizes of the column data entries vary considerably.
  • Use varchar(max), nvarchar(max), varbinary(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

Other Types: Apart from the above it provides some additional types like –

timestamp: Is a data type that exposes automatically generated, unique binary numbers within a database. The storage size is 8 bytes. You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.

Uniqueidentifier: Is a 16-byte GUID which is initialized by using the newid() function or converting a string constant in the form of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx which is used to guarantee that rows are uniquely identified across multiple copies of the table.

Xml: Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type. The stored representation of xml data type instances cannot exceed 2 gigabytes (GB) in size.

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

7 thoughts on “SQL SERVER 2008 – Data Types in SQL SERVER 2008

  1. I have a question dilip Kumar…. what is the maximum size of nvarhar(max) can support? because I have a requirement that I want push a file data into a variable and the file is of around 700mb….

    • Hello Satish,

      As your requirement is for storing file size of around 700 MB then you can go for nvarchar(max) because it supports a maximum of 2GB in terms of storage Please refer this
      “nvarchar [ ( n | max ) ]
      Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.”

      reference : http://msdn.microsoft.com/en-us/library/ms186939.aspx

      Thanks,
      Dilip Kumar Jena

  2. Hi Dilip Kumar Jena,

    i have a question for you. I created a table and i inserted values into it. But i get the following errors.

    Table Product
    create table Product(
    Id_Product nvarchar(10) primary key,
    Product_Name nvarchar(30),
    Measuring_Unit nvarchar(10),
    Unit_Price decimal(3,2),
    Quantity int)

    insert into Product values (‘PROD001′,’Red Bull’,’250g’,10.00,100)

    On execution of the insert statement i get the following error.
    Msg 8115, Level 16, State 8, Line 1
    Arithmetic overflow error converting numeric to data type numeric.
    The statement has been terminated.

    • Hello Sunil,

      Please refer http://msdn.microsoft.com/en-us/library/aa258832(SQL.80).aspx you will understand about the fixed precision scale

      try modifying your create table statement as
      create table Product(
      Id_Product nvarchar(10) primary key,
      Product_Name nvarchar(30),
      Measuring_Unit nvarchar(10),
      Unit_Price decimal(10,2),
      Quantity int)

      and then try and insert it will be successful.

      Thanks,
      Dilip

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