Deep dive into SQL data types. Learn about their categorization (Numeric, Character, Date, and Time, and Logical), the specific types within each category, and how to properly select the fitting data type for your table columns.
Table of Content
Data Types in SQL
You’ve always wondered whether your database queries are slow or whether your storage costs are skyrocketing? The answer may be hidden within your SQL data types. As a database administrator who once overlooked the current crucial aspect, I can tell you that understanding the SQL data type would be preferable to unlock a secret world power in the world of database administration.
Data type in SQL basically defines the kind of data that will go into a particular column. All entries of one particular column will be of the same data type.
These data types contain constraints and integrity. Constraints are the limitations on data. They can either be implemented to a column or to the table. When there is a violation between the data action and the constraint, then the action is aborted. There are different types of constraints like primary key, unique key, etc.
Why SQL Data Types Matter in SSMS ?
Choosing right SQL Data Type is crucial for:
Data Integrity: Prevents invalid data from going into the database (e.g., saving text in a numeric column).
Memory Efficient: only the amount of space required for the data is allocated thus reducing the cost of storage.
Query Efficiency: This enables the usage of certain clauses in sql which allows to speed up the indexing/search operations and as a result, makes the whole query significantly faster compared to without any indexes.
Application Compatibility: By enforcing how the data is abstracted, it enables the database and applications to communicate without friction.
Different Data Types in SQL
SQL Data types are divided into three major categories, namely, numeric, character, and date and time.
Numeric Data Types in SQL
Numeric data types store all numerical values or integer values
| Data Type |
Description |
Range/Size |
| INT |
Stores whole numbers. Commonly Used |
-2,147,483,648 to 2,147,483,647 |
| BIGINT |
Stores larger whole numbers |
-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
| SMALLINT |
Stores smaller whole numbers |
-32,768 to 32,767 |
| TINYINT |
Stores very small whole numbers |
0 to 255 |
| DECIMAL/NUMERIC |
Stores fixed-precision decimal numbers |
Up to 38 digits |
- Bigint data type helps store ‘really big’ values.
- Int data type is used to store reasonably big values.
- Smallint data type is used to store values that range from −32,768 to 32,767.
- Tinyint data type stores values from 0 to 255. It’s for relatively small numbers.
- Decimal data type is used to store fractional values in two arguments. First part is the size of the value that is the total number of digits, and the second part specifies the number of digits (d) after the decimal point. Let’s say, we want to store the decimal value 12.50. Here, the size would be 4 because the total number of digits is 4, and the value of ‘d’ would be 2 because there are 2 digits after the decimal point.
Any data type can be used based on the requirement. Int is the most commonly used data type.
Character Data Types in SQL
Character data types store all alphabetic values and special characters.
| Data Type |
Description |
Range/Size |
| CHAR |
Stores fixed-length text of n character |
Up to 8,000 characters |
| VARCHAR |
Stores variable-length text of up to n characters |
Up to 8,000 characters or MAX |
| TEXT |
Stores large text data.(use VARCHAR(MAX) instead) |
Up to 2GB |
| NCHAR |
Stores fixed-length Unicode text of n characters |
Up to 4,000 charcaters |
| NVARCHAR |
Stores variable-length. Unicode text of upto n characters |
Up to 4,000 characters |
| NTEXT |
Stores large Unicode text data(use NVARCHAR(MAX) instead) |
Up to 2 GB |
- Char data type takes in one argument and has fixed length. For example, consider the size of the value to be 20. This would mean that you cannot give any value having more than 20 characters. Keeping in mind the fact that char has fixed length, i.e., if the value size is to be 30 characters, but information assigned to it is of 3 characters, then the memory consumed is of 30 characters.
- Varchar data type also takes in size as the argument. But here, it is a variable length data type, unlike char. So here if the value size is to be 30 characters, and you give only 3 characters, the memory consumed would be only of 3 characters.
- The text data type can take in a string with a maximum length of 65,535 characters.
Date and Time Data Types in SQL
| Data Type |
Description |
Range/Size |
| Date |
Stores date only(no time) |
0001-01-01 to 9999-12-31 |
| Time |
Stores time only(no date) |
00:00:00.0000000 to 23:59:59:9999999 |
| DateTime |
Stores date and time |
1753-01-01 to 9999-12-31 |
| Timestamp |
Stores date and time which is specific to time zones |
0001-01-01 00:00:00 to 9999-12-31 23:59:59.9999999 |
- A date or a date/time value is stored in the Date and Time data types.
- We can specify the date with a format of ‘YYYY-MM-DD’ . For example, if we need to record the date 2 January 2019, we would first enter the year (2 0 1 9), then the month (0 1), and lastly the day (0 2) in the format of ‘2019-01-02’.
- We can specify the time represented within a format of HH:MM:SS. For example, let’s say we wish to save 8:30:23 a.m. The hour, which would be 0-8, the minutes, which would be 3 0, and the seconds, which would be 2 3, will be specified first.
Get 100% Hike!
Master Most in Demand Skills Now!