SQL-DataTypes
SQL Server Data Types
Data types are used to specifies the type of data that can be stored in the database table. In a particular column of a table have the data types, if we want to store a int type of data then we will have to declare a integer data type of this column.
CREATE TABLE Employee (
Eid int,
FirstName varchar(100),
LastName varchar(100),
Emailid varchar(100),
Age int,
Salary int,
)In the above table Eid, FirstName, LastName, Emailid, Age, Salary are the colums name and every column has a datatype. such as : varchar, int,
The below figure shown the types of SQL data types.
Image1
Data types mainly divided into four categories for every database.
- String Data types
- Numeric Data types
- Date and time Data types
- Other Data types
1. String Data Types
The string is a collection of characters put to use for storing multiple characters. Most of the data present around us is a string like our name, our address, etc.
| Data type | Description |
|---|---|
| text | Variable width character string. It's max size - 2GB of text data |
| char(n) | It is fixed width character string. It's max size -8,000 characters. |
| varchar(n) | Variable width character string. It's max size - 8,000 characters. |
| varchar(max) | Variable width character string. It's max size - 1,073,741,824 characters |
| nchar | Fixed width Unicode string. It's max size - 4,000 characters |
| nvarchar | Variable width Unicode string. It's max size - 4,000 characters |
| nvarchar(max) | Variable width Unicode string. It's max size - 536,870,912 characters |
| ntext | Variable width Unicode string. It's max size - 2GB of text data |
| binary(n) | Fixed width binary string. It's max size - 8,000 bytes |
| varbinary | Variable width binary string. It's max size - 8,000 bytes |
| varbinary(max) | Variable width binary string. It's max size - 2GB |
| image | Variable width binary string. It's max size - 2GB |
2. Numeric Data Types
The numeric data type in SQL is one of the most widely used data types to store numeric-like values. Numbers in SQL can be either exact (NUMERIC, DECIMAL, INTEGER, BIGINT, and SMALL INT) or approximate (DOUBLE PRECISION, FLOAT, and REAL).
| Data type | Description |
|---|---|
| bit | It is an integer that can be 0, 1, or NULL |
| tinyint | It allows whole numbers from 0 to 255 |
| smallint | It allows whole numbers between -32,768 and 32,767 |
| int | It allows whole numbers between -2,147,483,648 and 2,147,483,647 |
| bigint | It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 |
| decimal(p,s) | It allows fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 |
| numeric(p,s) | It allows fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 |
| smallmoney | It allows monetary data range from -214,748.3648 to 214,748.3647 |
| money | It allows monetary data range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| float(n) | Floating precision number data range from -1.79E + 308 to 1.79E + 308. The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53. |
| real | Floating precision number data range from -3.40E + 38 to 3.40E + 38 |
3. Date and Time Data Types
The DATE and TIMEdata type specifies a date and time with fractional seconds.
| Data type | Description |
|---|---|
| datetime | It is used to specify date and time combination. It supports range from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds |
| datetime2 | It is used to specify date and time combination. It supports range from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds |
| smalldatetime | It is used to specify date and time combination. It supports range from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute |
| date | It is used to store a date only. It supports range from January 1, 0001 to December 31, 9999 |
| time | It Store a time only to an accuracy of 100 nanoseconds |
| timestamp | It stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable |
Other Data Types
These are the some other data types which SQL supports.
| Data type | Description |
|---|---|
| xml | It is used to stores XML formatted data. Maximum 2GB |
| cursor | It is used to store a reference to a cursor used for database operations |
| table | it is used to store a result-set for later processing |
| sql_variant | It is used for various data types except for text, timestamp, and ntext. It stores up to 8000 bytes of data. |
| uniqueidentifier | It is used to Stores a globally unique identifier (GUID) |
Next