Emil Drkusic
SQL data types - the data model we'll use

Learn SQL: SQL Data Types

February 14, 2020 by

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.

SQL data types - the data model we'll use

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.

Textual sql data types

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.

Numeric sql data types

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.

Date and time sql data types

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.

SQL data types - SQL Server specific

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

Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server
Learn SQL: SQL Injection
Learn SQL: Dynamic SQL
Learn SQL: How to prevent SQL Injection attacks
Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. 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