share post
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.
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.
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.
Below is a look at each category of data within SQL Server, along with all of the data types that fit within each one.
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.
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.
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.
real: Single precision floating number, which equates to 4 bytes or 32 bits.
Character strings have a self-explanatory name: These data types are used to store characters. They can be fixed or variable in size.
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.
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.).
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:
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.
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.
There are a few Agile certifications available to choose from, and in this article, we’ll discuss the best agile certifications currently available for IT professionals.
Join LogicMonitor Wednesday June 1st for Dinner @ Frankie & Johnnie's Steakhouse
Join LogicMonitor for a CiscoLive Dinner @ SushiSamba - June 14th, 2022
