Data Types in SQL

Like every other programming language, SQL also has data types. Every piece of data that is entered into a database must comply with the data types and their formats. This implies that any data that you store is either a number, a character, or some other data type. Those are the basic data types. There are some special data types as well.

For instance, "00:43 on Monday, 1 April 2019" is a combination of letters, numbers, and punctuation. However, when we see something like this, we immediately start thinking of the day. A data type is the type of value that can be stored in a system. Some examples of data types are INTEGER, FLOATING POINT, CHARACTER, STRING, and combinations of these such as DATETIME.

Since there's a large amount of data types, most languages classify data types. Here, we will go through some of the most common ones. The idea here is to get you acquainted with the data types, not to give you a complete rundown of them as this would overwhelm you with hardly any significant returns. Moreover, once the concept is clear, you will be able to adapt to the rest of the data types with little effort.

In the interest of better data integrity and modeling, it is critical to select the right data type for the situation. It may seem trivial when the database is small, but with a larger database, it becomes difficult to manage. As a programmer, it is your responsibility to model your data in the right way.

In order to keep this simple, let's broadly classify the data types into five categories:

• Numeric data types: Numeric data types include everything that involves numbers, such as integers (small/big), floating- and fixed-point decimal numbers, and real numbers. Here are some of the most common ones:

Figure 1.9: Numeric data types

• Fixed and varying length characters and text: Performance is key when selecting either fixed- or variable-length characters. When you know that a certain piece of data will be of a fixed number of characters, use the fixed width. For example, if you know that the employee code will always be of 4 characters, you can use CHAR. When you are unsure of the number of characters, use variable width. If a certain column holds only six characters, you are better off specifying it so that space used will be limited. By doing this, you will get better performance by not using up more resources than required. If you are unsure of the width, you don't want to be limited by the total width. Therefore, you should ideally use character types of varying lengths. An example of this can be a person's first name, where the length of the name is not fixed.

  Note

  You can use CHAR with varying lengths of characters (VARCHAR) as well. For instance, in a field that accepts up to six characters, you can enter data that is three characters long. However, you would be leaving the other three-character spaces unused, which will be right-padded, meaning that the remaining spaces will be reserved as actual spaces. When the data is retrieved, these trailing spaces will be trimmed. If you don't want them to be trimmed, you can set a flag in SQL that tells SQL to reserve the spaces and not trim them during retrieval. There are situations where you would need to do this using the TRIM string function, for example, to enhance data security.

  Unicode characters and string data types are different. They are prefixed with N, such as NCHAR, NVARCHAR, and NTEXT. Also, note that not all SQL implementations support Unicode data types.

  Note

  Unicode character data types consume twice the storage space compared to non-Unicode character data types.

  The other character-based data type is TEXT. This can store textual data up to a certain limit, which may vary with the system. For instance, MS SQL supports text up to 2 GB in size.

• Binary data types: Binary forms of data are also allowed in SQL. For instance, an IMAGE would be an object of binary form. Similarly, you have BINARY and VARBINARY data types.
• Miscellaneous data types: Miscellaneous data types include most of the now-popular data types, such as Binary Large Object (BLOB), Character Large Object (CLOB), XML, and JSON. We have included DATE, TIME, and DATETIME as well in this class.

  Character and binary large objects include types such as files. For instance, a film stored on Netflix is a binary large object. So would be an application package such as an EXE or an MSI, or other types of files such as PDFs.

  Note

  SQL Server 2016 supports JSON. JSON Unicode character representation uses NVARCHAR/NCHAR or ANSI VARCHAR/CHAR for non-Unicode strings.

  MySQL version 5.7.8 supports a native JSON data type.

• Proprietary types: In the real world, there is hardly a pure SQL implementation that is favored by enterprises. Different businesses have different requirements, and to cater to these requirements, SQL implementations have created their own data types. For instance, Microsoft SQL has MONEY as a data type.

  Not all data types are supported by all vendors. For instance, Oracle's implementation of SQL does not support DATETIME, while MySQL does not support CLOB. Therefore, the flavor of SQL is an important consideration when designing your database schema.

As we mentioned previously, this is not an exhaustive list of all data types. Your flavor of SQL will have its own supporting set of data types. Read the documentation that comes with the product kit to find out what it supports—as a programmer or a SQL administrator, it is you who decides what is necessary. This book will empower you to do that.

The size limits illustrated in Figure 1.9 are only indicative. Just as different flavors of databases may have different data types, they may have different limits as well. The documentation that accompanies the product you plan to use will have this information.