If you’re a database administrator (DBA) or simply wear the hat of the “database guy,” understanding how SQL stores data is critical. What’s the best way to store a string, a number, a file, or a date? You can answer these questions by understanding SQL Server data types.
In this article, you will understand what SQL Server data types are, distinguish between various data types, and choose the best type for your SQL data.
What are SQL Server Data Types?
To understand SQL in-built data types, you must first understand SQL data. Within SQL, you can store all kinds of different information from words (strings), numbers (integers and float), dates and times, and even entire files. It’s not efficient for SQL to store this data the same way.
Each type of data has its own unique characteristics and should be handled differently for efficiency and performance reasons.
Although each SQL data type behaves differently to store different types of data, they all have a few common attributes:
- Character – Characters are the “root” of all SQL data types. Characters represent a single entity of any data type. For example, a character could be a string, a number in an integer, or even a date in a DateTime data type. For example, the word “moses” has five characters and the decimal data type “123.456” has seven characters, including the decimal point.
- Size – The amount of memory used by data defined by the data type. The size is always measured in bytes, with one byte equalling one symbol or character. For example, SQL will store the letter ‘a’ as one byte when stored as an English letter, two bytes as a European or middle eastern letter, or three bytes as an Asian letter.
- Length – The number of characters in any data type. A character could be a character in a string of letters, a byte in a binary format, or the number one in an integer.
Overall Tips for Choosing a SQL Data Type
In this article, you will learn about all of the common SQL data types. You’ll learn about their unique characteristics and what kind of data you can store in each of them. But, before you get that granular, it’s important to first keep the two most important factors in the back of your mind; storage and performance.
- Storage – When you define a data type, you’re automatically setting aside a certain amount of storage. Therefore, choosing an inefficient data type could easily waste lots of disk storage. Always choose the data type with the smallest footprint based on the data you think will be stored in that data type.
- Performance – Not all data types are created the same when it comes to performance. For example, the CHAR data type operators are 50% faster than the VARCHAR data type simply because it has less work.
String Data Types and When to Use Them
One of the most common data types in SQL is the string. The string represents alphanumeric text. Think of a string as ‘a’, ‘abc123’ or ‘adam-the-automator’. A string holds alphanumeric characters. A string could also contain all numbers like 8125551212, such as a phone number.
Choosing to store data with one of several string data types is a good idea when you need to store data like a name, physical address, email address, phone number, gender, or any other text.
Character – CHAR(X)
When you need to store a string with a fixed number of characters not exceeding 255, the CHAR data type is a good choice. The CHAR string data type allocates a set amount of memory to store the data in.
For example, US-based phone numbers always have 10 digits. These phone numbers have a fixed schema and will never exceed 10 digits. Storing phone numbers in a CHAR field with a size of 10 would work well for phone numbers.
Variable Character – VARCHAR(X)
Unlike CHAR data types, VARCHAR data types are flexible. VARCHAR data types allow you to define a maximum size, not a fixed size.
For example, if you defined a VARCHAR data type with a maximum size of 10 but inserted data that only took up eight bytes, SQL would adjust the memory required to only eight bytes instead of 10.
Enumerate – ENUM
When you pick your gender from a web form, the form typically doesn’t provide you with a blank text box. Instead, it provides you with a dropdown of selections like male and female. When you know the available options ahead of time, use an ENUM SQL data type.
Using an ENUM data type allows you to control what data is inserted into a field.
TEXT Types
SQL also has various TEXT data types. These types are great for storing string information such as long-form text strings that range from about 1 byte to 4 GB. The TEXT data types are commonly used to store bodies of articles or perhaps item descriptions on e-commerce sites.
The only major difference between the TEXT data types is the size of information you can add to them.
Unlike the similar CHAR and VARCHAR data types, the various TEXT types don’t require you to specify a length.
TEXT AND TINYTEXT
The TEXT and TINYTEXT SQL data types are great for data like quotes and phone numbers. You can fit a maximum character count of 256 bytes and 64kb respectively into the TINYTEXT and TEXT data types
MEDIUMTEXT and LONGTEXT
When you need to insert a whole lot of alphanumeric characters into a field, you should use a text field, either MEDIUMTEXT or LONGTEXT. Choosing between these two data types will depend on how much text you intend to store.
If, for example, you’d like to store a memo that’s less than 16MB, the MEDIUMTEXT data type may work well but if you’d like to store an entire book that’s less than 4GB, LONGTEXT would work.
| Name | Maximum Length | Examples |
| CHAR | 256bytes | All kinds of specified texts like company abbreviations na phone numbers |
| VARCHAR | ~64kb | All kinds of unspecified texts like names, addresses, list of items |
| ENUM | Unspecified | Human gender, sizes, all form discrete data |
| MEDIUMTEXT | ~16MB | A memo or perhaps an essay |
| LONGTEXT | ~4GB | An entire book of text |
| TEXT | ~64kb | Phone numbers |
| TINYTEXT | 256bytes |
Numeric Data Types and When to Use Them
When you need to perform mathematical operations on SQL data, it’s time to look into a numeric data type. Numeric data types allow you to store numbers you know will be used for some mathematical expression later on.
A numeric data type would work well for data such as the salary of an employee where you may need to calculate bonuses, a price of an item sold in a supermarket to calculate sales tax, and more.
There are two “categories” of numeric data types; those with and without decimal points.
Integer – INT
When you need to store a number without a decimal point, use an integer. Integers store whole numbers. The count of an item may be a good example for storing as an integer. The price of a supermarket item, on the other hand, would not.
Fixed-point – DEC(P,S) and NUM(P)
When you need to store non-whole numbers with a decimal point, it’s time to look into a fixed point SQL data type. Fixed-point data types store a fixed number and are defined by a precision (P) and scale (S) value.
A precision value represents the total number of digits allowed and scale represents the total number of digits to the right of the decimal point. For example, 10.523 has a precision of five and a scale of three.
Fixed-point data types are called fixed because, like CHAR data types, they fix the amount of memory allocated to them. For example, a field defined with a fixed-point data type with a precision of five and a scale of three would allocate the same amount of memory when you store 10.1 or 10.111 because 10.1 would be stored as 10.100.
Fixed-point data types are perfect for number schemas that don’t change like currency where you know you’ll always have two digits to the right of the decimal point.
Two fixed-point data types exist in SQL called decimal (DEC) and numeric (NUM). Although these two data types are similar, NUM only allows you to provide a precision value while DEC allows you to provide a provision and scale.
DEC will act like a numeric data type when a precision is specified without giving a scale.
Floating-Point – FLOAT(P) and DOUBLE
When you need to store non-whole numbers but aren’t sure of those numbers’ exact precision and scale, it’s time to look at a floating-point data type.
Fixed-point and floating-point data types like FLOAT(P) and DOUBLE differ by the way they both represent values in the system storage. A fixed-point data type like DECIMAL will give a warning and truncate the data when its rules are not respected while a floating-point data type will not give a warning sign, as shown below.
Floating-point data types are great for storing the results of a math calculation. For example, a FLOAT data type will store the result of a math calculation by rounding up the answer given to be represented by the precision (P) defined.
Signed vs. Unsigned
Numbers can be both positive and negative. Positive and negative numbers are treated a bit differently in SQL. To store a negative number in a numeric data type, that data type must be signed. Signed data types support negative and positive numbers.
Signed numeric data types allow you to store values from -128 to 127. While unsigned numeric data types allow you to store values from 0 to 255.
| Name | Size(bytes) per character | Storage range (unsigned) | Storage range (signed) |
| TINYINT | 1 byte | 0 to 255 | -128 to 128 bytes |
| UNSIGNED TINYINT | ??? | -255 | Up to 255 bytes |
| SMALLINT | 2 bytes | 0 to 65535 | -32KB to 32KB |
| INT | 4 bytes | 0 to 4,294,967,295 | -2B to 2B |
| MEDIUMINT | 3 bytes | 0 to 16,777,215 | -8MB to 8MB |
| BIGINT | 8 bytes | 0 to 18,446,744,073,709,551,615 | -9ZB to 9ZB |
| FLOAT | 4 bytes | Null | -2B to +2B |
| DECIMAL | 1 byte + 1byte (for the sign “+ or -“) | 0 to 65,535 | -32KB to +32KB |
| DOUBLE | 8 bytes (twice the bytes of floats) | Null | -9ZB to 9ZB |
Date-Based Data Types and When to Use Them
If you need to store dates along with specific times, look into date or time-based data types. These data types are designed to store a date or date and time.
Date
When you need to store a date without a specific time, choose the Date data type. The Date type stores the year, month, and day separated by a dash in the format YYYY-MM-DD.
Datetime
If you need to store a date and a time, use the DateTime data type. The Datetime data type stores dates in the same format as the Date data type (YYYY-MM-DD) and includes the time in the format HH:MM:SS:MS down to the millisecond (MS).
Binary Large Object (BLOB) Data Types and When to Use Them
In the earlier sections, you learned about storing text of some kind. Whether that “text” is a string, a number, or a date, it was all just “text.” Sometimes, it makes sense to store binary data like files in your SQL database. When that happens, it’s time to look into a binary large object (BLOB) data type.
BLOB data types are great for storing related files such as product images, Excel invoices, audio files, video clips, or any other file type. To suppose binary data like this, SQL allows blob data types sizes of several gigabytes
If you’d like to store files in a SQL database type, you have four different BLOB data type options; Tiny BLOB, BLOB, Medium Blob, and Long BLOB. Each data type possesses the same characteristics other than the maximum amount you can store inside each type.
| Name | Size (byte) per character | Potential storage consumption |
| Tiny BLOB | 1 byte | 255bytes |
| BLOB | 2 bytes | ~65KB |
| Medium BLOB | 3 bytes | ~16MB |
| Long BLOB | 4 bytes | ~4GB |
Conclusion
You should now understand the basics of choosing a SQL data type. In addition, you have seen that SQL provides you many options when deciding how to store your important SQL data.
Now that you know some of the most common SQL data types and their characteristics, can you think of a previous project where you didn’t assign the right data type and how it affected your project in the long run?
