The data type in an SQL Server is an attribute that defines what type of data will be stored in a column of a table. When we create any table in a database, we must define columns with their respective data types. The SQL Server has various system data types that can store all data types, such as numbers, alphabets, special characters, etc.
The data types supported by SQL Server, can be categorized in the following categories.

Exact numeric data types like int, bigint.
Approximate numeric data types like real and float.
Date and Time data types like datetime, datetime2.
Character string data types like char, varchar.
Unicode character strings like nChar and nVarchar.
Binary string data types like varbinary.
Special data types like XML, SQL_Variant, rowversion, etc.

Note that you must choose the data type carefully because choosing the wrong one might adversely impact the application's performance. Also, keep the data type's length adequate so the application can store data properly. For example, if you want to store an employee's address in the address column, the data type length must be adequate; otherwise, the user might encounter errors.
This article will explain various data types, their range, usage, and simple examples. We will also see the data types that have been deprecated.

Exact Numeric Data Types

Exact numeric data types are used to store precise values. As the name suggests, exact numeric data types do not lose accuracy due to rounding and approximation. These data types can be used to store financial data like the cost of a product and sales amount, statistical data like the population of a country, demographic data like human age, and many more. SQL Server supports five types of exact numeric data types.

Example of exact numeric data types in SQL Server

Here is a simple example which illustrates what type of data can be stored in exact numeric data type

use BansalGroup_MainDB
go
CREATE TABLE ExactNumericDatatypes (
    ID INT PRIMARY KEY,
    BigIntColumn BIGINT,
    IntColumn INT,
    SmallIntColumn SMALLINT,
    TinyIntColumn TINYINT,
    DecimalColumn DECIMAL(10, 2),
    NumericColumn NUMERIC(8, 3)
);
INSERT INTO ExactNumericDatatypes (ID, BigIntColumn, IntColumn, SmallIntColumn, TinyIntColumn, DecimalColumn, NumericColumn)
VALUES (1, 123456789012345, 123456, 12345, 255, 123.45, 123.456);

Approximate Numeric Data Types

The approximate numeric data types are used to store the data that has floating values. In SQL Server, you can store the approximate numeric datatypes in float and REAL data types. Here are the characteristics of float and REAL data type.

Example of approximate numeric data types in SQL Server
Here is a simple example that illustrates what data type can be stored in approximate numeric data type.

CREATE TABLE ApproximateNumericExample (

    FloatDataType FLOAT,
    RealDataType REAL
); 
INSERT INTO ApproximateNumericExample (FloatDataType, RealDataType)
VALUES (123.456, 123.456);

Date and Time Data Types

The SQL Server has data types that are used to store the date and time. Here is a short description of each of them.
date
The data type is used to store only date. The data type has three part; month, year and day. The default format to store the value in data type is yyyy-MM-dd. The range of datatype is from 0001-01-01 to 9999-12-31.
time
In some applications, we might have to store the time values separately. In such use cases, can use time data type. The data type is used to store only time. By default, the time datatype has seven fraction precision. The range of datatype is from 00:00:00.0000000 to 23:59:59.9999999.
datetime
The datetime data type is used to store the date and time. The data type can store three millisecond fractions. By default, the datetime data type format is yyyy-MM-dd HH:mm:ss:fff. The range of datatype is from 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997.
datetime2
The datetime2 data type was introduced in SQL Server 2008. It is an extension of the datetime2 data type for better precision.
Datetime2 has a larger date range, and the default precision is 7 digit which is higher than datetime data type. Also, you can specify the fraction precision. E.g. datetime(5).
There are some limitations of datetime2 data type

The basic mathematical operations with dates are not supported, like calculating the difference between two dates or adding days to the existing date value.
When we compare the datetime2 value with specific date values, SQL Server performs an implicit conversion which may impact performance. The range of datatype is from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999.

smalldatetime
As the name suggests, the smalldatetime data type is used to store date and time but the data type does not store seconds or a fraction of time. The range of datatype is from 0001-01-01 00:00 to 9999-12-31 23:59.
datetimeoffset
The datetimeoffset is an extension of datetime2 data type. The data type includes the time zone based on UTC / GMT. The range of datatype is from 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 with offset (-14:00) to (14:00).

You can refer to the following table to understand the difference between all datetime data types supported by SQL Server.

Example of date and time data types in SQL Server:
Here is the simple example:

CREATE TABLE DateTimeDataTypes (

    DateTimeColumn DATETIME,
    SmallDateTimeColumn SMALLDATETIME,
    DateTime2Column DATETIME2,
    DateColumn DATE,
    TimeColumn TIME
);
INSERT INTO DateTimeDataTypes (DateTimeColumn, SmallDateTimeColumn, DateTime2Column, DateColumn, TimeColumn)
VALUES
(
'2024-03-18 12:30:00', -- Datetime
'2024-03-18 12:30:00', -- Small datetime
'2024-03-18 12:30:00.1234567', --Datetime2
'2024-03-18', -- Date
'12:30:00.1234567' --Time
);

Character Strings

The character string data types store the characters or strings in a database. In SQL Server, you can use char(n), varchar(n), varchar(max), and text data types to store the character strings. Here is a detailed description of all of them.

Example of character string data types in SQL Server
Here is a simple example

CREATE TABLE StringDataTypeExample (

    FixedCharColumn CHAR(10),
    VarCharColumn VARCHAR(255),
    TextColumn TEXT

);
INSERT INTO StringDataTypeExample
( FixedCharColumn, VarCharColumn, TextColumn)
VALUES ('Nisarg ', 'Nisarg Upadhyay', 'Nisarg Upadhyay is a scary DBA');

Unicode Character Strings

The Unicode character string data types are used to store the Unicode characters or strings in a database. In SQL Server, you can use nchar(n), nvarchar(n), and ntext data types to store the character strings. Here is the detailed description of all of them.

Here is the simple example:

CREATE TABLE StringDataTypeExample (

    NCharColumn NCHAR(10),
    NVarCharColumn NVARCHAR(255),
    NTextColumn NTEXT

);
INSERT INTO StringDataTypeExample
( NCharColumn, NVarCharColumn, NTextColumn)
VALUES ('निसर्ग ', 'निसर्ग उपाध्याय', 'निसर्ग उपाध्याय एक डरावना डीबीए है।');-- Hindi language Text

Note that, while inserting a Unicode string, you must specify N before the Unicode string.

Binary Strings

In SQL Server the binary data types are used to store the binary string. In SQL Server, there are three data types: binary, varbinary, and varbinary(max). The details of all of them are the following:

Here is a simple example. I am inserting “Nisarg Upadhyay” character string. To insert data, first we must convert the character string to varbinary. The code to create table and insert data is following:

CREATE TABLE BinaryDataTypeTable
(
    BinaryData VARBINARY(100)
);
INSERT INTO BinaryDataTypeTable (BinaryData)
VALUES (convert(varbinary,'Nisarg Upadhyay')); -- Convert to varbinary

To view data, run the SELECT query on BinaryDataTypeTable.

select BinaryData [Binary string], convert(varchar,BinaryData)[Original String] from BinaryDataTypeTable

Output

As you can see, the character string is converted into a a binary string.

Special and Miscellaneous Data Types

SQL Server also supports some special data types that are supported by SQL Server to handle specific data.
XML data type
The XML data type is used to store XML data. It can store XML documents up to 2GB in size. SQL Server has many XML functions that can be used to read, write, or update XML data. Here is a simple example that shows how to store data in XML data type.

CREATE TABLE XMLExample (
    ID INT PRIMARY KEY,
    XMLColumn XML
);
INSERT INTO XMLExample (ID, XMLColumn)
VALUES (1, '<bookstore>
  <book category="Database">
    <title lang="en">How to tune database</title>
    <author>Nisarg Upadhyay</author>
    <year>2005</year>
    <price>30.00</price>
  </book>
  <book category="IT Programming">
    <title lang="en">.Net Pro</title>
    <author>Pritesh Patel</author>
    <year>2005</year>
    <price>29.99</price>
  </book>
</bookstore>');

Geospatial and Spatial data types
SQL Server supports data types to store geospatial data like GEOMETRY and GEOGRAPHY.
Geometry
The GEOMETRY datatype represent data in flat (Euclidean) coordinates system. Here is a simple example. I have created a table named GeometryDataTypeExample which is used to store the geometry coordinates of the city. Following is the code to create table:

CREATE TABLE GeometryDataTypeExample (
    CityID INT PRIMARY KEY,
    CityName NVARCHAR(100),
    Coordinates GEOMETRY
);

Following is the code to insert geometry data.

INSERT INTO GeometryDataTypeExample (CityID, CityName, Coordinates)
VALUES
    (1, 'New York City', geometry::STGeomFromText('POINT(-74.0059 40.7128)', 4326)),
    (2, 'Los Angeles', geometry::STGeomFromText('POINT(-118.2437 34.0522)', 4326)),
    (3, 'London', geometry::STGeomFromText('POINT(-0.1276 51.5074)', 4326));

Geography
GEOGRAPHY datatype represents data in round-earth (ellipsoidal) co-ordination system like latitude and longitude of GPS system. The geography system is CLR data type in SQL Server. Here is a simple example. I have created a table named geographydatatypeexample which is used to store the geographical data of the city. Here is the code to create a table:

CREATE TABLE GeographyDataTypeExample (
    CityID INT PRIMARY KEY,
    CityName NVARCHAR(100),
    GeoCoordinates GEOGRAPHY
);

Following is the code to Insert data in table.

INSERT INTO GeographyDataTypeExample (CityID, CityName, GeoCoordinates)
VALUES
    (1, 'New York City', geography::STGeomFromText('POINT(-74.0059 40.7128)', 4326)),
    (2, 'Los Angeles', geography::STGeomFromText('POINT(-118.2437 34.0522)', 4326)),
    (3, 'London',  geography::STGeomFromText('POINT(-0.1276 51.5074)', 4326));

You can read this article to learn more about the GEOMETRY and GEOGRAPHY datatypes.
Rowversion (Timestamp)
The rowversion is a special datatype which is like a timestamp, but it is used internally to manage the concurrency of database. When any record is changed or updated, the SQL Server automatically generates a unique binary number which is used to track or detect a change in record.
Hierarchyid
The hierarchy datatype is a special datatype used to store and query the hierarchical data in SQL Server. The datatype is optimized for representing the tree-like structure like employee hierarchy in company or a family tree. You can read this article to learn more about the hierarchy datatype.
Rowversion (Timestamp)
The rowversion is a special data type that is like a timestamp, but it is used internally to manage the concurrency of database. When any record is changed or updated, the SQL Server automatically generates a unique binary number which is used to track or detect a change in record.
SQL_VARIANT
The SQL_VARIANT data type can store values of various SQL Server data types. This data type is useful when you want to store different types of data in a column. For example, you want to store int and binary values in the same column. Such requirements can be fulfilled by SQL_Variant data type. The maximum length of the SQL_Variant data type is 8016 bytes. You can read this article to learn more about the SQL_Variant data type. Here is a small example.

CREATE TABLE SQLVariantDataType(SQLVariantColumn sql_variant) 

INSERT INTO SQLVariantDataType values ( CAST(46279.1 as decimal(8,2)))
INSERT INTO SQLVariantDataType values(CAST('Nisarg Upadhyay' as varchar(5)))

As you can see in above example, The table SQLVariantDataType has a column named SQLVariantColumn with SQL_Variant data type. Now, I inserted two records in a table. The first record is decimal and second record is character string. Now, execute select statement to view the data.

SELECT  
             SQLVariantColumn,
             SQL_VARIANT_PROPERTY(SQLVariantColumn,'BaseType') AS 'Base Type', 
        SQL_VARIANT_PROPERTY(SQLVariantColumn,'Precision') AS 'Precision', 
        SQL_VARIANT_PROPERTY(SQLVariantColumn,'Scale') AS 'Scale' 

FROM      SQLVariantDataType

Output

As you can see, the column has decimal and varchar data stored in same column.

Deprecated Data Types

After every release of SQL Server, some old features, syntax and data types get deprecated. So far, SQL Server has deprecated three data types. The data types can be used but it won’t be supported in future versions of SQL Server. These data types will be replaced by other data types. Here is a list.

Timestamp is replaced by rowversion.
Text is replaced by Varchar(max).
NText is replaced by nvarchar(max).
Image is replaced by varbinary(max).

While designing a new application, avoid using the above data types. You should start changing the code with the new data type if you already use it in your application.

Conclusion

In any database management system, the data type selection is very important. When we design any database, we must select data types carefully to get optimum performance of the database and application.

In this article, I have explained about data types, their categories, and how to use them to create tables. I have used SQL Server Management Studio and dbForge Studio for SQL Server to write the script that is used to create table.

Data Types in SQL Server: In-depth Overview