SQL Data Types

Data Types

1. In a database, each column, local variable, expression, and parameter has a related data type.
2. A data type is an attribute that specifies the type of data that the object can hold.
3. The following are the various data types:
   
   
   • Integer Types
   • Boolean Type
   • Decimal Types
   • Monetary or Currency Types
   • Date and Time Values
   • String Values
   • Binary Values
   • Timestamp
   • Uniqueidentifier
   • XML

1. Integer Types

1. Integer Types hold integer values.
2. The Integer Types are tinyint, smallint, int and bigint data types with sizes 1, 2, 4 and 8 bytes respectively.
    

   Integer Types	Bytes
   Tinyint	1
   Smallint	2
   Int	4
   Bigint	8

2. Boolean Type

1. A Boolean Type can hold boolean values
2. It can provide a "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.

3. Decimal Types 

1. Decimal Types hold decimal values.
2. They provide the following types:
   
   • decimal and numeric
   • float and real

Decimal and numeric

• 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. The scale must be a value from 0 through p. The scale can be specified only if the precision is specified. The default scale is 0.
• The storage sizes of Decimal and Numeric types vary, based on the precision, as shown in the following:
   

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

  Note: numeric is functionally equivalent to decimal.

Float and real

• 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 then it must be a value between 1 and 53.
• The default value of n is 53.
   

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

4. Monetary or Currency Types

1. Monetary or Currency Types hold currency values.
2. It provides the smallmoney and money data types with sizes 4 and 8 bytes respectively.
    

   Monetary Types	Bytes
   Smallmoney	4
   Money	8

5. Date and Time Values

1. Date and Time Values hold the Date and Time values of a day.
2. It provides the following types:
   
   • smalldatetime
   • datetime
    

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

Smalldatetime

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

Datetime

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

6. String Values

1. String Values hold the string values
2. String Values types are:
   
   • Char
   • Varchar
   • Text
   • Nchar
   • Nvarchar
   • Ntext

Char

• 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

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

Nchar

• 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

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

7. Binary Values

1. Binary Values hold the binary values like's images, audio clips and video clips.
2. Binary Values types are:
   
   • binary
   • varbinary
   • Image

Binary

• 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

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

8. Timestamp

1. Timestamp is a data type that exposes automatically generated, unique binary numbers within a database.
2. The storage size is 8 bytes.
3. 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.
4. If any change is made to the row, the timestamp value is updated.
5. If no change is made to the row, the timestamp value is the same as when it was previously read.

9. Uniqueidentifier

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

10. XML

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

Note

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