What are SQL data types, why do we need them, and how to use them? Today, we’ll try to answer these questions by giving an overview of some most common data types used in SQL Server.
The Model
Before doing anything else, we’ll take one quick look at the model we’re using in this series.
You can notice that each table has columns defined by their name, but also with their type. While the name is pretty obvious, and we’re the one defining it, the thing is little different when it comes to types. We can choose a column type from a list of predefined types or our custom type (again build from predefined types).
SQL Data Types – Quick Intro
Talking about data types is not the hottest thing in programming. They are just one of these things that are here, and we expect it to work. Still, it’s necessary to understand what we have at disposal.
When we’re storing data, we need to simulate “measures & formats” used in the real world. E.g. we want to store height as a decimal number, birth date as a date, ‘this sentence’ as a text, etc. You could always go with an extreme and store everything as text or photo.
Since databases are meant to store data and work with them efficiently, we won’t do that. We’ll have several different SQL data types at our disposal. We won’t only have types to store text or number, but we’ll also go deeper, with types closely describing the nature of that text (how long is it) or number (is it integer or decimal).
The main idea behind data types is to simulate the most common types from the real-world. By doing so, the DBMS (database management system), will be able to store them efficiently (less space used, better organized), and that will also lead to improved performance.
These types vary between different DBMSs (SQL Server, MySQL, Oracle), but most of them are very similar. We’ll focus on the most important SQL Server/Transact-SQL data types.
Textual Data Types
As you could see in our model, there are 3 most common types of real-world data we need to store: texts, numbers, and dates. Therefore, most DBMS (and programming languages) will support these 3 main groups. In each of these groups, you’ll find more or less standardized SQL data types.
So, let’s quickly review textual data types first. In the table below, you’ll find Transact-SQL string/textual data types with short descriptions.
The most important are:
- char(n) – where n is replaced by the maximum number of characters we expect in any string. If that number is exceeded, we won’t be able to store the complete string. E.g. char(8) means that we’ll store 8 characters. If the string has less than that, the remaining characters shall be filled with blanks. Therefore, char(n), always has the same length, uses static memory allocation, and works 50% faster than varchar(n) (in an ideal scenario). It’s wise to use char(n) when you’re completely sure about the length of the data you’ll store, e.g. postal codes, account numbers, phone numbers, bank codes, etc.
- varchar(n) – serves the same purpose as char(n), but the difference here is that the string ends after the last character – we don’t add blanks to use the whole string. E.g. if you use varchar(255) and fill on 15 characters, then only 15 characters are stored. The problem here is that we don’t know the exact length of each string, so everything works slower (still, nothing critical). So, we’ve traded better performance in order to save some disk space
- text – well, if you want to store an immense text, you should use this one 🙂
Numeric Data Types
The next big group of SQL data types is definitely the one containing numeric data types. Once more, we have the overview given in the table below.
Still, I want to point to some of the most important types from this group:
- int – Whole numbers are the first numbers we learn. We count sheep to fall asleep – 1 sheep, 2 sheep… I want to say that everything starts with them. In most cases, you’ll use int – for primary and foreign keys these shall be unsigned. In other places, you’ll use int when you need to store whole numbers. In case, you want to spare some disc space, you could use tiny, or small int if you’re sure this will do. bigint should really be able to cover all your needs regarding whole numbers
- decimal(p,s) / float(n) / real – Are used to store decimal numbers. We can expect that most numerical values we want to store are actually decimal values – percentage, graphical coordinates, sports results etc.
- bit – Uses only 1 bit to store value 0 or 1 (NULL if not defined). It serves as a flag telling us if something stands or not. One of the most common usages of this type is to store info if the data in the related record is still valid/active/not deleted
Date & Time
The last SQL data types group we’ll analyze are date & time types. The overview is given in the table below.
I would like to emphasize the following ones:
- date – In most cases, you’ll need to store date only, e.g. birth date, registration date, etc.
- datetime – Stores date and time in one attribute
- timestamp – Is not SQL Server standard, but you’ll probably meet it in other DBMSs. It serves the same purpose as datetime, but is UTC-time related
The list of the remaining interesting MS SQL data types is given in the table below.
So, which SQL Data Type You Need to Use?
The answer to this question is simple and complex at the same time. In most cases, you’ll know exactly what you need – a whole number is a whole number. Still, there is a lot to play with – combining char and varchar, avoid using text whenever possible. It’s a game where you should guess anything end user would like to insert and have this supported. Of course, with the best possible performance and minimum disk space allocated.
Table of contents
His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby...
You can find him on LinkedIn
View all posts by Emil Drkusic
- Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
- Learn SQL: Dynamic SQL - March 3, 2021
- Learn SQL: SQL Injection - November 2, 2020