There are many types of data compatible with SQL Server, and it’s important to understand what they are to avoid issues with non-compatible data types. Understanding the compatible data types is also fundamental to understanding the data type precedence, which determines what type of data will result when working with objects of two different types.
In this guide, we’ll go through all of the data types SQL Server supports, along with the process of defining custom data types using Transact-SQL or the Microsoft .NET Framework.
Contents
- What Is SQL Server?
- Why Are Data Types Important?
- What Are the Different Data Categories?
- Defining Custom Data Types
- Choosing the Right Data Type
What Is SQL Server?
Before we dive into the many data types SQL Server supports, here’s a quick recap on what SQL Server is. Microsoft developed SQL Server to serve as a relational database management system (RDBMS). SQL Server utilizes SQL, the standard language for using relational databases of all kinds.
Microsoft’s SQL Server is no longer exclusive to the Windows environment and is now available on Linux, which is great news for those interested in using SQL Server. Additionally, Microsoft’s cloud platform, known as Azure, supports SQL Server. So, if you need a place to host it, there’s no better place to look than the native solution.
Microsoft markets it as “the cloud that knows SQL Server best,” and Azure SQL Server indeed benefits from seamless integration, simplicity, and reliability since both the server and cloud infrastructure are developed and maintained by the same company.
Regardless of where you host SQL Server, though, it’s critical to note SQL Server uses a slightly different SQL language. Microsoft has developed Transact-SQL (T-SQL), which is very similar to standard SQL but defines a set of proprietary concepts necessary to program SQL Server.
If you are familiar with SQL, using Transact-SQL will not be difficult, and you’ll be able to utilize SQL Server with ease. But another critical aspect of effectively using SQL Server is understanding all of the data types it supports.
Why Are Data Types Important?
The wrong data type can lead to issues with database performance, query optimization, and data truncation. These problems are often first realized by the development team as they are the ones tracking speed and performance. Still, problems can trickle down to the entire organization, causing data integrity issues and other serious challenges.
If you’re new to SQL Server, the sheer number of data types may be overwhelming. However, they are neatly organized and well-documented, making it a bit easier to find what you need as long as you understand what type of data you are planning to store. Of course, while you can reference as you go along, gaining knowledge of SQL Server data types is paramount to efficiency and optimization in the long haul. Once you dive in, you’ll see that there is some overlap, and knowing when to choose float point over decimal or opt for variable-length over fixed is only possible if you fully understand all of your options.
What Are the Different Data Categories?
Below is a look at each category of data within SQL Server, along with all of the data types that fit within each one.
Exact Numerics
When using an exact numeric data type, it’s important to understand your options so that you can select the smallest data type suitable for your use case. It’s also necessary to choose the data type corresponding to the kind of numeral you’re storing—like money or smallmoney for currencies.
- tinyint: The smallest integer storage type, capable of storing numbers between 0 and 255.
- smallint: Integer storage type with twice the size, up to 2 bytes.
- int: Integer storage type with up to 4 bytes of storage.
- bigint: The largest integer storage type capable of holding up to 8 bytes of data.
- decimal and numeric: These synonymous terms refer to the same data type, which is characterized by its fixed scale and precision.
- bit: This data type always has a value of 1, 0, or NULL. You can convert true/false data to a bit, where 1 equals True, and 0 equals False.
- smallmoney: This data type represents monetary values and allows up to two decimals.
- money: This is another monetary data type but allows up to four decimals.
Unicode Character Strings
If you’re unfamiliar with Unicode, it is a universal standard that assigns a unique number to every character, allowing written text to be encoded and represented consistently. For example, “hey” in Unicode would be broken down as follows: U+0048 (“H”), U+0065 (“E”), and U+0059 (“Y”).
SQL Server supports the full range of Unicode character data using these character strings. They can be fixed or variable.
- nchar: Fixed in size, recommended for use when the data sizes in a column are consistent.
- nvarchar: Variable in size, recommended for use when data sizes in a column vary considerably.
- ntext: Scheduled for removal in future versions of SQL Server, designed as a variable-length data type for Unicode. Microsoft recommends nvarchar(max) instead.
Approximate Numerics
When numeric data cannot be represented exactly, it is referred to as “floating point” numeric data, and you would use approximate-number data types to store it. With floating-point data types, the number is written using scientific notation, so 825,000 would be stored as 8.5 x 105.
Floating-point numbers can be incredibly large or incredibly small. Both floating-point and decimal data types can store a number with a decimal—the difference is that floats require less storage space while decimals are more precise. SQL Server supports two kinds of approximate numerics with the float and real data types.
- float: Double precision floating number, which equates to 8 bytes or 64 bits.
real: Single precision floating number, which equates to 4 bytes or 32 bits.
Character Strings
Character strings have a self-explanatory name: These data types are used to store characters. They can be fixed or variable in size.
- char: Fixed-size string data that uses a static memory location. Ideal when you know the length of the string and all strings in a column will be the same
- varchar: Variable-sized string data that uses a dynamic memory location. Use if you are unsure of the length of the string or when the length of strings in a column will vary considerably.
- text: Scheduled for removal in future versions of SQL Server, designed as a variable-length data type for non-Unicode data. Microsoft recommends replacing it with varchar (max).
Binary Strings
Binary data types support either fixed or variable strings of data. The difference between character strings and binary strings is the data they contain: Character strings typically store text but could also store numbers or symbols. Binary strings typically store non-traditional data in the form of bytes, such as pictures.
- binary: Fixed length, ideal to use when the data sizes in a column are consistent.
- varbinary: Variable length is ideal when data sizes in a column vary considerably.
- image: Scheduled for removal in future versions of SQL Server, designed to store variable-length binary data. Microsoft recommends replacing it with varbinary (max).
Date and Time
These data types are explicitly designed for storing dates and times. Some support timezone awareness and others do not. When dealing with dates and times, it’s crucial to choose a data type that keeps entries consistent in format and select a data type that’s flexible enough to support the level of detail you need (i.e., time of day, timezone, etc.).
- date: Defines the date. The default format is YYYY-MM-DD but can be formatted in over 20 different ways, including DMY, DYM, and YMD.
- datetimeoffset: Defines a date and time of day. This data type is timezone aware.
- datetime2: Extension of the above data type with an optional fractional seconds precision.
- datetime: Similar to datetime2 but with less fractional seconds precision.
- smalldatetime: Defines a date and time of day, but seconds are always zero.
- time: Defines a time of day but without timezone awareness.
Other Data Types
Additional data types exist in SQL Server, but they don’t quite fit into any of the above categories. For that reason, these data types simply exist under “other.” The other data types include the following:
- rowversion: Used for version stamping rows within a table. A simple incrementing number that does not preserve date or time.
- hierarchyid: Variable length system data type used to represent the position in a hierarchy.
- uniqueidentifier: A globally unique identifier (GUID) capable of storing up to 16 bytes.
- sql_variant: Stores various data types supported by SQL. The most important part of sql_variant is that it is variable. For example, one sql_variant column could contain an int in one row and a binary value in another. In order for arithmetic operations, like SUM or PRODUCT to be applied, the type must be first cast to something that works with that operation.
- xml: Stores XML data.
- Spatial Geometry Types: Represents data in a flat coordinate system.
- Spatial Geography Types: Represents data in a round-earth coordinate system.
- table: Special data type used to store a result for processing at some point later.
Defining Custom Data Types
In the event that you have a custom data type that is not specific in any of the above categories, you can still bring it onto SQL Server, so long as you set it up in advance. Using either Transact-SQL or the Microsoft .NET Framework, developers can define custom data types for their projects.
While creating a custom data type, SQL Server’s interface has some tools to help you generate Transact-SQL. The fields you’ll need to specify are schema, name, underlying data type, length, allowing nulls, the size in bytes, optional fields in a default value, and any rules the data type must follow. For example, an email field would likely use a varchar underlying type and must contain an @ and a ., alongside a list of disallowed characters. You’d then set a max length that suits your needs, and the number of bytes would be automatically populated in the interface.
Microsoft offers more information if you need to create your own data type.
Choosing the Right Data Type
When working with SQL Server, it’s crucial that you choose the correct data type for any data you are working with. Failing to do so could lead to data quality issues or data loss, like in instances where you use a data type to store date and time when it is not intended to do so. The wrong data type can also negatively impact querying and performance.
For instance, if you need to store integers, you might assume you can’t go wrong by simply choosing an exact number data type. However, unnecessarily using the bigint type to store a small, simple number like age will lead to wasted resources.
Most would tell you to choose from smallint, integer, bigint, or decimal any time you’re working with numeric data. If you’re dealing with very big numbers, they might suggest decfloat or float. But, this advice is too simplistic and general when you’re dealing with specific use cases.
For instance, you might be working with an inventory system that requires four-digit values and leading zeros. Or, you might be storing Social Security numbers, which need to be properly formatted with hyphens as XXX-XX-XXXX. As you can imagine, there are many complex applications for SQL Server where general guidelines aren’t good enough for choosing the right data type. For that reason, knowing all of the data types available is the first step in choosing the best data type for any information you need to store. Fortunately, you don’t have to memorize all of the data types and their range of information. Microsoft has excellent documentation that takes you through every data type discussed here if you ever need more detailed information in the future.