12th Aug 2021 20 minutes read An Overview of MS SQL Server Data Types Martyna Sławińska sql learn sql MS SQL Server Data Engineering SQL Server data types define what can be stored in a column, local variable, expression, or parameter. It is essential to pick the right data type. Ultimately. your choice of data types affects the whole database. Read on to learn about all of the data types available in MS SQL Server. In this article, we’ll cover numerical, text, and date and time data type categories in detail. We’ll go through their syntax, storage size, and typical use cases. We’ll also touch upon the less common data types available in MS SQL Server. Our article on Data Types in SQL also provides a general overview of the Standard SQL data types. Let’s get started! Numerical Data Types in SQL Server In mathematics, all numbers are divided into sets, such as natural, integer, rational, real, and imaginary. SQL Server’s data types were created based on those sets of numbers. They allow you to restrict a column to a specific numerical data type. You might deduce from the above graphic that the FLOAT and REAL data types encapsulate all the other ones. And you’re not wrong! It’s possible to store an integer or a rational number in a FLOAT or REAL data type (but not the other way round). However, remember the principle of least privilege; for our specific example, it could mean that if you want to store only integers in a column, you should use an integer data type. You don’t want to use rational or real number sets to store simpler numbers like integers. Note that the BIT data type is assigned to the set of natural numbers. This data type can store only two natural numbers, i.e. 0 and 1, and NULL values. We’ll get to that later. Now let’s look at some of the most common numerical data types. Integer Data Types Integer data types allow you to store whole numbers, such as -17, -5, 0, 3, 56, 278, and so on. If you try to store 123.57 in an integer data type, the numbers after the decimal point will be cut off; only the number 123 would be saved. INT The INT data type can store only numbers without decimal digits. But it’s limited as to the values that it can store: only values from -2,147,483,648 to 2,147,483,647, or from -231 to 231-1. The storage size of the INT data type is 4 bytes (32 bits). This data type is typically used to store counts, quantities, or IDs. For example, you can use INT to store the number of products in stock: CREATE TABLE Stock ( ProductId INT, ProductName VARCHAR, ProductCount INT ); ProductIdProductNameProductCount 1Pen200 2Ruler150 3Crayon350 In the example above, we used INT to store quantity data (the ProductCount column) and ID data (the ProductId column). BIGINT The BIGINT data type is very like INT, but it allows a greater range of values: from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, or between -263 and 263-1. Hence, its storage size is 8 bytes (64 bits). You may wonder why anybody would need such large numbers. The 4-billion range offered by INT seems sufficient for most use cases. However, as data grows day by day, we need increasingly larger storage spaces, log files, and identifiers. That’s where the BIGINT data type comes in. To give a unique identifier to each log file entry, we might require numbers larger than 2 billion. Thus, we’d use the BIGINT data type. Also, INT’s range might not be sufficient when performing calculations on large numbers – e.g. to find out how much 240 is, we need a BIGINT. CREATE TABLE LogFileEntries ( EntryId BIGINT, EntryDateTime DATETIME ); EntryIdEntryDateTime 52375517892021-07-09 06:19:30.045 52375517902021-07-09 06:19:30.088 52375517912021-07-09 06:19:31.007 SMALLINT The SMALLINT data type is similar to INT, but it stores a smaller range of values: from -32,768 to 32,767, or from -215 to 215-1. Hence, its storage size is 2 bytes (16 bits). Why would you go for a smaller range if the INT range is optimal for any use case? If you know your column values won’t exceed 32,767, SMALLINT will save some storage space. For example, the number of students enrolled in one class will certainly never hit 32,767! So, you can use the SMALLINT data type for this column: CREATE TABLE Classes ( ClassId INT, ClassProfile VARCHAR, NumberOfStudents SMALLINT ); ClassIdClassProfileNumberOfStudents 34Biology55 35Mathematics43 36Physics54 We have used the INT data type for class IDs and the SMALLINT data type for the number of students. Decimal Data Types The decimal data types (DECIMAL and NUMERIC) store rational numbers. If you want a column that can store numbers with decimal points, such as -12.56, or 768.4564, this is the way to go. DECIMAL The DECIMAL data type allows you to store values with digits after the decimal point. Its storage size depends on the precision you define. What is precision? It’s the total number of digits on both sides of the decimal point. For example, the precision of the number 64876.3428798 is 12 (5 digits to the left of the decimal point + 7 digits to the right of the decimal point). The scale is the number of digits after the decimal point, which in this example is 7. The syntax of the DECIMAL data type is: decimal(precision [, scale]) The precision and scale arguments are optional. For example, decimal(5, 2) can store numbers like -653.87 or 143.55. However, numbers like 2.34 or 23.4 can also be stored in decimal(5, 2) because they are not over the limits set on precision and scale. When defining a DECIMAL, you can define its precision and scale. However, this is not mandatory. The default values for precision and scale are 18 and 0, respectively. As mentioned before, the storage size of the DECIMAL data type depends on its precision. Possible values include: PrecisionStorage size 1 – 95 bytes 10 – 199 bytes 20 – 2813 bytes 29 – 3817 bytes The maximum precision of 38 allows for the storage of values from -1038+1 to 1038-1. We use the DECIMAL data type to store values of dimensions like length, width, or depth. It can also store monetary values, although there is a special data type to store these that we’ll discuss later. CREATE TABLE Boxes ( BoxId INT, BoxSize CHAR(1), BoxLengthCm DECIMAL(4, 1), BoxWidthCm DECIMAL(4, 1), BoxDepthCm DECIMAL(4, 1) ); BoxIdBoxSizeBoxLengthCmBoxWidthCmBoxDepthCm 1S50.560.075.5 2M58.666.880.9 3L71.477.590.5 Box dimensions are rarely integers; hence, the DECIMAL data type is used for dimensions and the INT data type is used for IDs. NUMERIC The NUMERIC data type can be used interchangeably with the DECIMAL data type. It has the same arguments (precision and scale), the same storage size (which depends on the precision), and the same functionality as DECIMAL. Let’s look at an example that uses NUMERIC: CREATE TABLE Marathons ( MarathonId INT, Country VARCHAR, DistanceKm NUMERIC(3, 1) ); MarathonIdCountryDistanceKm 1USA45.5 2France56.7 3Canada33.5 Here, the NUMERIC(3, 1) data type restricts the values of the DistanceKm column to a precision of 3 and a scale of 1. Floating-Point Data Types Floating-point data types are used to represent approximate values. Like decimal data types, they allow digits after the decimal point. FLOAT The FLOAT data type allows storing approximate values with the specified mantissa of the floating-point number. A floating-point’s mantissa represents the number of significant digits; for example, the number 0.05678 has 4 significant digits (leading or trailing zeros are not significant). The mantissa is used to determine storage size. Mantissa valueStorage size 1 – 244 bytes 25 – 538 bytes The syntax for this data type is ... float[(n)] … where n is the optional mantissa argument (the default value is 53). You can use the FLOAT data type when you can allow the stored values to be approximated; for example, a value of 59.475134 could be approximated to 59.475 according to your definition of FLOAT. CREATE TABLE SquareRoots ( Id INT, Number INT, SquareRootOfNumber FLOAT(3), MorePreciseSquareRoot FLOAT(7) ); IdNumberSquareRootOfNumberMorePreciseSquareRoot 1103.163.162278 2113.323.316625 3123.463.464102 Here, the FLOAT data types show different mantissa values in representing the square roots of given numbers. REAL The REAL data type is a synonym for FLOAT(24). It has the same properties and functionality as the FLOAT data type. The difference between them is that REAL is a single-precision floating-point format, and FLOAT is a double-precision floating-point format. If you want to save storage space, you could use REAL instead of FLOAT(24 or less). For example, you’d probably use REAL instead of FLOAT(10). The FLOAT data type occupies 8 bytes of memory, which is twice what the REAL data type uses. These numerical data types are very essential because they store salary, price, amount, quantity, and similar kinds of data. They also are used for computations. To get a better understanding of the numerical data types, check out our article on Understanding Numerical Data Types in SQL. Text Data Types in SQL Server SQL Server text data types are used to store letters, words, complex strings, sentences, etc. Remember, the first normal form (1NF) of database normalization states that fields should store atomic values. For example, instead of storing the name as Richard Black, we should store it as the first name Richard and the last name Black. But we could store longer strings that are still atomic. For example, a book title can be quite long, but it is an atomic value; it cannot be broken down into smaller parts. There are various data types for storing letters, words, or longer strings available in SQL Server: The TEXT and NTEXT data types store non-Unicode and Unicode data, respectively. The CHAR and VARCHAR data types store only characters encoded with UTF-8 (which encodes all common characters and numbers using 8 bits). The NCHAR and NVARCHAR data types store only characters encoded with UTF-16 (which encodes characters and numbers using 16 bits). In general, the NCHAR, NVARCHAR, and NTEXT types store Unicode data, while the CHAR, VARCHAR, and TEXT types store non-Unicode data. Let’s look at all the data types that you can use to store your text data. VARCHAR The VARCHAR data type stores variable-size string data. Its storage requirements depend on the number of characters. However, you can define the upper limit of the string size using the optional argument, as shown below: varchar[(n|max)] The value of n defines the string size in bytes and can range from 1 to 8000. You could also use max instead of the n value; varchar(max) indicates the maximum storage size of 2 GB. The storage size of the VARCHAR data type is n bytes + 2 bytes. We can use the VARCHAR data type to store names, addresses, descriptions, etc. In general, any value that does not exceed 8000 bytes (when defining n) or 2 GB (when defining max) can be stored in the VARCHAR data type. Let’s consider an example that uses VARCHAR data type: CREATE TABLE WebshopCustomers ( CustomerId INT, FirstName VARCHAR(10), LastName VARCHAR(20), Email VARCHAR(30) ); CustomerIdFirstNameLastNameEmail 44AliceJohnsa.johns@email.com 45BobAndersonbobanderson@email.com Above, we use three examples of VARCHAR: The FirstName column uses VARCHAR(10) and can store values up to 10 characters; it wouldn’t be possible to store values like Constantine as a first name because that name is over the limit of 10 characters. The LastName column uses VARCHAR(20) and can store values up to 20 characters. The Email column uses VARCHAR(30) and can store up to 30 characters; it wouldn’t be possible to store the value “constantine.langston1990@email.com” because it’s over 30 characters. CHAR The CHAR data type stores fixed-size data. As with the VARCHAR data type: Its storage size depends on the number of characters being stored. The string size is defined in bytes. The syntax CHAR is ... char[(n)] … where the optional n argument is the string size in bytes. It can range between 1 and 8000. The difference between the VARCHAR and CHAR data types is that VARCHAR’s size is variable and CHAR’s size is always the same. If you define a column as CHAR(1000) but enter a value of 10 characters, the database will pad the remaining characters with blanks to reach the 1000 limit. On the other hand, a VARCHAR field stores only the inserted characters; it doesn’t add anything to reach its defined size. Thus, VARCHAR can save storage space. CHAR use cases are similar to those for VARCHAR. The only thing you should consider is the fixed size. If the size of the strings stored in the column will vary, it's better to use VARCHAR. TEXT The TEXT data type stores variable-length non-Unicode data. It can store variable-width character strings up to 2,147,483,647 characters. Its maximum size is 2 GB. Its storage size is 4 bytes + the number of characters being stored. The syntax is simply TEXT; it does not take any arguments. We can use this data type to store any text data; however, CHAR and VARCHAR are often preferred, as they allow for greater customization of the data type. NVARCHAR The NVARCHAR data type stores variable-size string data. Its storage size depends on the number of characters being stored. The syntax is: nvarchar[(n|max)] The argument is optional and defines the string size in byte-pairs (n). You could use max instead of an n value; this sets the storage size to its max of 2 GB. The storage size is 2 * n bytes + 2 bytes. We can use the NVARCHAR data type to store names, addresses, descriptions, or other string data encoded with UTF-16. Let’s summarize the differences between VARCHAR and NVARCHAR data types: VARCHARNVARCHAR Stores non-Unicode data; suitable for standard characters in the English alphabet Stores Unicode data; suitable for multilingual data (e.g. Japanese or Chinese characters). Uses 1 byte per character. Can represent up to 28 (256) characters Can’t store non-Latin characters (e.g. Chinese or Arabic) unless they are supported by your database settings. Uses 2 bytes per character. Consumes more storage space. Can represent up to 216(65536) characters. Allows multilingual data. Max value is VARCHAR(8000). Max value is NVARCHAR(4000). Uses UTF-8 encoding by default. Uses UTF-16 encoding by default. Stores ASCII data: the input data is always encoded first and then stored. Stores Unicode data and does not need conversion. NCHAR The NCHAR and NVARCHAR data types are analogous to the CHAR and VARCHAR pair of data types. The difference between NCHAR and NVARCHAR is that NCHAR is fixed in size like CHAR, while NVARCHAR is variable. The N in the names of these data types stands for the string length in byte-pairs, not the number of characters. NTEXT The NTEXT data type stores variable-length Unicode strings up to 1,073,741,823 characters. Its storage size is twice the length of the string being stored, and its maximum capacity is 2 GB. The syntax is simply NTEXT; it doesn't take any arguments. We can use it to store any text data. Summary of Text Data Types in MS SQL Server Let’s summarize all the text data types discussed in this section. The NCHAR, NVARCHAR, and NTEXT data types store Unicode data; the CHAR, VARCHAR, and TEXT types store non-Unicode data. In SQL Server, CHAR and VARCHAR use UTF-8 encoding, while NCHAR and NVARCHAR use UTF-16 encoding. The takeaway for this section is that Unicode allows the storage of languages like Japanese or Chinese, while non-Unicode can store Latin alphabets. Nowadays, most operating systems use Unicode data to ensure multilingual features across alphabets (or writing systems). However, if you know you’ll only need to store Latin-alphabet languages like English, you can use non-Unicode data, which takes less storage space. Date and Time Data Types in SQL Server There are a number of data types that allow you to store the date, time, or date and time as an atomic value. The diagram above presents a simple division of the date and time data types available in SQL Server. Let’s look at each one. DATE The DATE data type stores date values only. It has a fixed storage size of 3 bytes. The syntax is simply DATE, with no arguments. The default format is yyyy-mm-dd. Let’s look at an example of DATE: CREATE TABLE PublicHolidays2021 ( Name VARCHAR(100), Date DATE ); NameDate New Year's Day2021-01-01 Thanksgiving2021-11-25 Christmas Day2021-12-24 You can use the FORMAT() function to define a different date format: SELECT FORMAT(getdate(), 'MMM dd, yyyy') as MyDate GO The GETDATE() function returns current date and time. The output of the query above would be “Jul 10, 2021”. Use DATE wherever you want to store only the date, e.g. for storing your country’s public holidays. TIME The TIME data type can store only time values. It has a fixed storage size of 5 bytes and a fractional precision of up to 100 nanoseconds. Its syntax is: time[(fractional second scale)] The optional argument specifies the fractional second scale. You can use a value between 0 and 7 that determines different fractional second scales. The default is time(7), which stores the maximum precision of 100 nanoseconds (hh:mm:ss.nnnnnnn). When do you need fractional seconds? Remember the LogFileEntries table in the BIGINT section? (We’ve slightly modified it below.) To log all the data precisely, we need to use fractional seconds: CREATE TABLE LogFileEntries ( EntryId BIGINT, EntryDate DATE, EntryTime TIME(4) ); EntryIdEntryDateEntryTime 52375517892021-07-0906:19:30.0455 52375517902021-07-0906:19:30.0887 52375517912021-07-0906:19:31.0075 The default format of the TIME data type is hh:mm:ss. You can use the FORMAT function to set your preferred format. SELECT FORMAT(getdate(), 'hh:mm') as MyTime GO The GETDATE() function returns the current date and time. So, the output of the query above could be “18:35”. Use the TIME data type wherever you want to store time values only, e.g. for storing class schedules. DATETIME The DATETIME data type stores dates and times based on the 24-hour clock. Its storage size is 8 bytes, and its syntax is simply DATETIME. This data type stores only three fractional seconds, i.e. yyyy-mm-dd hh:mm:ss.nnn. The default format of the datetime data type is yyyy-mm-dd hh:mm:ss. Let’s look at an example of the DATETIME data type: CREATE TABLE UltramarathonRunners ( RunnerId INT, Start DATETIME, End DATETIME ); RunnerIdStartEnd 23762021-07-10 10:00:01.0232021-07-12 00:44:25.093 23772021-07-10 10:00:02.1042021-07-11 23:59:11.123 23782021-07-10 10:00:01.0762021-07-12 01:14:34.006 Once again, you can use the FORMAT() function to set a custom format: SELECT FORMAT(getdate(), 'MMM dd, yyyy hh:ss') as MyDateTime GO The output of this query would be “Jul 10, 2021 19:15”. Use DATETIME wherever you want to store date and time values together, e.g. for event schedules. DATETIME2 DATETIME2 is an extension of the DATETIME data type. They are pretty much the same, except that DATETIME2: Has a larger date range: DATETIME’s date range is January 1, 1753 CE through December 31, 9999. DATETIME2’s date range is January 1,1 CE through December 31, 9999. Can store up to 7 fractional seconds: yyyy-mm-dd hh:mm:ss.nnnnnnn. Takes one argument, which sets the precision of the fractional seconds. Has the syntax DATETIME2[(fractional seconds precision)], Has a storage size that varies between 6 and 8 bytes, according to the precision set by the user. It’s advisable to use DATETIME2 for data logging, as it allows for greater precision. DATETIMEOFFSET DATETIMEOFFSET is similar to the DATETIME2 data type; its additional feature is that it includes time zone information (as the difference from Coordinated Universal Time (UTC) time). Its storage size is 10 bytes. The syntax is: DATETIMEOFFSET[(fractional seconds precision)] The optional argument obeys the same rules as DATETIME2. The default format of DATETIMEOFFSET is: YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm] And the format can be modified using FORMAT(). Nowadays, many events take place online, with people from all over the world joining in. So, it’s important to provide the correct date and time of the event for each time zone. This is when DATETIMEOFFSET comes in handy. SMALLDATETIME SMALLDATETIME is similar to DATETIME. The difference is that the accuracy is one minute; it does not store seconds or fractional seconds. The storage size is 4 bytes. Its syntax is SMALLDATETIME – there are no arguments. The default format is yyyy-mm-dd hh:mm:ss, which can be changed using FORMAT(). Let’s look at an example of the SMALLDATETIME data type: CREATE TABLE Events ( EventId INT, Name VARCHAR(100), Start SMALLDATETIME, End SMALLDATETIME ); EventIdNameStartEnd 1001Earth Conference2021-08-15 16:30:002021-08-15 21:30:00 1002SQL Course2021-09-20 17:00:002021-09-20 22:30:00 1003Fair for Modern Art2021-09-30 10:45:002021-09-30 20:00:00 SMALLDATETIME is used here to save storage space; we don’t care about the second and fractional second parts of the starting and ending time of these events. All that needs to be known is just the date (yyyy-mm-dd) and time (hh:mm). Hence, SMALLDATETIME fulfills the requirements without unnecessarily using up storage space. Summary of Date and Time Data Types in MS SQL Server Let’s summarize the date and time data types discussed here. We’ll use the date of 20-12-2020 and the time of 11:15:20.0000586 as examples. Data typeDefault value formatExample DATEyyyy-mm-dd2020-12-20 TIMEhh:mm:ss.nnnnnnn11:15:20.0000586 DATETIMEyyyy-mm-dd hh:mm:ss.nnn2020-12-20 11:15:20.000 DATETIME2yyyy-mm-dd hh:mm:ss.nnnnnnn2020-12-20 11:15:20.0000586 DATETIMEOFFSETYYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]2020-12-20 11:15:20.0000586 -10:00 SMALLDATETIMEyyyy-mm-dd hh:mm:ss2020-12-20 11:15:20 The BIT Data Type in SQL Server In SQL Server, BIT is equivalent to the BOOLEAN data type. It can store A value of 1, 0, or NULL. Hence, its storage size is 1 byte. A value of 1 corresponds to TRUE; 0 corresponds to FALSE. The string values TRUE and FALSE can be easily converted to the BIT data type. In fact, any non-zero value is converted to the BIT value of 1, while a value of 0 is converted to the BIT value of 0. The typical example of BIT usage is when a column needs to have a value of either TRUE or FALSE. Creating Your Own Data Type with T-SQL You can also create your own data type in SQL Server; these are sometimes called alias types. Any custom type is based on an existing SQL Server data types, but you can customize the type to a specific use. To customize a data type, you use the CREATE TYPE statement: CREATE TYPE id FROM INT NOT NULL UNIQUE; The above statement creates the ID data type (alias); it is an INT type with the additional limitations of being NOT NULL and UNIQUE. Here’s another example: CREATE TYPE zipCode FROM VARCHAR(50); This one creates the zipCode data type (alias), which is a VARCHAR type that has a storage size of 52 bytes. Other SQL Server Data Types We’ve discussed the most commonly used data types available in SQL Server. However, there are many other types that you should know about. It’s best to understand all the tools you have at hand. Let’s briefly look at other data types offered in SQL Server. The Exact Numerics data type category: MONEY – Used to achieve the best precision with monetary values. SMALLMONEY – Like MONEY, but requires less storage space. TINYINT – An INT that only allows values between 0 and 255. The Binary Strings data type category: BINARY – Stores fixed-length binary data. VARBINARY – Stores variable-length binary data. IMAGE – Stores variable-length binary data. Other data types: CURSOR – Used for variables or stored procedure output parameters that contain a reference to a cursor. ROWVERSION – Stores table row versions. HIERARCHYID – Represents positions within a given hierarchy. UNIQUEIDENTIFIER – Uniquely identifies a column or local variable. SQL_VARIANT – When used in a column, this type allows the column to store different data types. It is also used in parameters, variables, and the return values of some user-defined functions and stored procedures. XML – Stores XML data. It can be used with a column or a variable. TABLE – Temporarily stores a set of rows returned by an GEOMETRY – Stores geometrical data. It is one of SQL Server’s spatial data types. GEOGRAPHY – Stores geographical data. Also one of SQL Server’s spatial types. For even more on T-SQL data types, check out Microsoft’s documentation. All the MS SQL Server Data Types in a Nutshell That’s all there is! We’ve gone through all the data types available in SQL Server, comparing them and getting an idea of where to apply each one. The basic data type categories – such as numerical, text, and date and time – are used on a daily basis. You should have them at your fingertips. However, the less-common data types are also important; they may not be used as often, but they can be very helpful in certain cases! When you’re creating a database, it’s crucial to choose the right data types. Don’t miss our track on Creating Database Structure if you want to learn more about building databases and selecting data types. To get a sneak peek, check out an article about this track. To deepen your knowledge of MS SQL Server data types, you might also want to try our course on Data Types in SQL. Good luck! Tags: sql learn sql MS SQL Server Data Engineering