SQL Data Types

Introduction

In this article, we will learn about Data Types in SQL with different data types.

Data Types in SQL

1. Each column, local variable, expression, and parameter in a database 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
   • Unique identifier
   • XML

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

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.

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 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 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 precisely.
• Where n is the number of bits 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 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

Monetary or Currency Types

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

   Monetary Types	Bytes
   Smallmoney	4
   Money	8

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, representing the number of milliseconds after midnight.

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.

Binary Values

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

Binary

• binary [ ( n ) ]
• Fixed-length binary data with 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.

Timestamp

• The 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 quickly 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

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

XML

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

Note

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

Summary

In this article, we learned about Data types in SQL.