Microsoft’s SQL Server sets out to make data management simple and easy. But if you’re new to the system, it can feel overwhelming and confusing.
The good news is it’s not as complicated as it may seem. Here, you can learn all about the different SQL Server data types, so you can get up and running in no time.
The Numeric Data Types
To start, the SQL Server numeric data type is broken into two categories: exact and approximate.
The difference between the two categories is straightforward. The exact numeric data type stores exact numbers, while the approximate numeric data type stores approximate figures with floating-point data. In other words, the exact data type has fixed precision and scale, while the approximate data type has values that can’t be represented precisely in the data range.
Falling underneath the exact numeric data type are the following:
- int, bigint, smallint, and tinyint
- decimal and numeric
- money and smallmoney
Int, bigint, smallint, and tinyint store integers. You should start with tinyint to save space and work your way up to bigint to fit in larger ranges of integers.
The SQL Server decimal data type is functionally the same as the numeric data type. You use them for maximum precision.
The SQL Server money data type stores monetary values. You can associate currency symbols with them, but the system will only store the numeric value.
The bit data type stores three values: 0, 1, and NULL.
Falling underneath the approximate numeric data type are:
These data types are defined by the value of n, a number of bits ranging between 1 and 53.
The Date and Time Data Types
The date and time data types store exactly what you would expect: date and time data, as well as a date time offset.
Within this category, you have:
- datetime, datetime2, and smalldatetime
In short, these data types are based on a 24-hour clock. The level of precision you are looking for will dictate which data type you choose.
For example, you can specify down to the minute with smalldatetime and down to the nanosecond with time, datetimeoffset, and datetime2.
If you want to specify the time zone, you should use datetimeoffset. You can use datetime2 for larger date ranges.
The Binary, Character, and Unicode Character Strings
These data types are also straightforward: Binary string data types store binary data; character string data types store character data; Unicode character string data types store Unicode character data.
Within each, you can store fixed-length data or variable-length data. For example, binary stores fixed data while varbinary stores variable data. The same is true of char and nchar, which store fixed data, and varchar and nvarchar, which store variable data.
In addition to these data types, there are text, ntext, and the SQL Server image data type. You can use all three to store larger data and extend string length.
“Other” Data Types
There are a variety of other data types used in SQL Server, including:
- Spatial Geometry and Geography Types
For more information on these types, click here. You should also consider how the latest features of SQL Server affect how data types work.
Understanding the Different SQL Server Data Types
These basic descriptions will help you understand the SQL Server data types and how they differ from one another. But there’s a lot more to navigating the SQL Server system. By brushing up on new updates, you can take advantage of all the functionality available to you.
Looking for more technology tips and tricks? Check out our blog.