SQL Data type defines the values that a column can accept, for example if a column of the table has an int data type, then it can only accept integer values. You can specify the data type of the columns while creating the table.
SQL Data Types classification
Data Types in SQL are classified in six different categories as follows:
- Numeric Data Types
- Approximate Numeric Data Types
- Date and Time Data Types
- Strings Data Types
- Unicode Character Strings Data Types
- Binary Data Types
1. Numeric Data Types
| DATA TYPE | Range | Description |
|---|---|---|
| int | From -2,147,483,648 to 2,147,483,647 | integer numbers |
| bigint | From -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | integer numbers just like int data type but with large range. |
| smallint | From -32,768 to 32,767 | integer numbers but with smaller range than int data type. |
| tinyint | From 0 to 255 | intger numbers but with smaller range than the smallint data type. |
| bit | 0 or 1 | True is converted to 1 and False is converted to 0 |
| money | From -922,337,203,685,477.5808 to +922,337,203,685,477.5807 | This data type is used for monetary units. |
2. Approximate Numeric Data Types
| DATA TYPE | Range | Description |
|---|---|---|
| float | From -1.79E + 308 to 1.79E + 308 | Used for floating numbers, number that contain decimal points and fraction values. |
| real | From -3.40E + 38 to 3.40E + 38 | Similar to float but with bigger range than float data type. |
3. Date and Time Data Type
| DATA TYPE | Range | Description |
|---|---|---|
| date | From January 1, 0001 to December 31, 9999 | Stores a date like July 12, 2022 |
| time | Stores a time like 1:30 PM | Stores time with an accuracy of 100 nanoseconds. |
| datetime | From Jan 1, 1753 to Dec 31, 9999 | Used for storing data and time data. It has wider range than smalldatetime data type |
| smalldatetime | From Jan 1, 1900 to Jun 6, 2079 | Similar to datetime but with smaller range. |
4. Strings Data Types
| DATA TYPE | Range | Description |
|---|---|---|
| char | Max size 8000 characters. | It is used to specify a fixed length string that can contain numbers, letters, and special characters. |
| varchar | Max size 8000 characters. | It is used to specify a variable length string that can contain numbers, letters, and special characters. |
| varchar(max) | Max size 1,073,741,824 characters | Similar to varchar, this is also used to specify a variable length string that can contain numbers, letters, and special characters. |
| text | Max size 2GB of text data. | It specifies variable width character string. |
5. Unicode Character Strings Data Types
| DATA TYPE | Range | Description |
|---|---|---|
| nchar | Max size 4000 characters. | It is used to specify a fixed length unicode string. |
| nvarchar | Max size 4000 characters. | It is used to specify a variable length unicode string. |
| nvarchar(max) | Max size 536,870,912 characters | Similar to varchar, this is also used to specify a variable length unicode string. |
| ntext | Max size 2GB of text data. | It specifies variable width unicode character string. |
6. Binary Data Types
| DATA TYPE | Range | Description |
|---|---|---|
| binary | Max size 8000 bytes. | It is used to specify a fixed length binary string. |
| varbinary | Max size 800 bytes. | It is used to specify a variable length binary string. |
| varbinary(max) | Max size 2GB | Similar to varchar, this is also used to specify a variable length binary string. |
| image | Max size 2GB. | It specifies variable width binary string. |
Leave a Reply