Other SQL Server Data Types
SQL Server provides several other data types for storing Boolean values, monetary values, unique identifiers, binary data, XML documents and other specialized information.
What are Other Data Types?
Apart from numeric, character, date and time data types, SQL Server provides specialized data types for specific application requirements.
These data types are useful when an application needs to store Boolean values, currency, globally unique identifiers, binary files, XML documents or other specialized information.
SQL Server Other Data Types
BIT
Stores Boolean values such as 0 and 1.
MONEY
Stores monetary values.
UNIQUEIDENTIFIER
Stores globally unique identifiers.
BINARY / VARBINARY
Stores binary data.
XML
Stores XML formatted data.
SQL_VARIANT
Stores values of different SQL Server data types.
Common Other Data Types
| Data Type | Purpose | Example |
|---|---|---|
| BIT | Stores Boolean or yes/no type values. | 0 / 1 |
| MONEY | Stores monetary values. | 45000.50 |
| SMALLMONEY | Stores smaller-range monetary values. | 1500.25 |
| UNIQUEIDENTIFIER | Stores GUID values. | 6F9619FF-8B86-D011-B42D-00C04FC964FF |
| BINARY | Stores fixed-length binary data. | 0x123456 |
| VARBINARY | Stores variable-length binary data. | 0x1234567890 |
| XML | Stores XML documents and fragments. | <Student>...</Student> |
| SQL_VARIANT | Stores values of different supported SQL Server data types. | 100 / 'Hello' |
BIT Data Type
The BIT data type is commonly used to store Boolean values such as Yes/No, True/False or Active/Inactive.
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
IsActive BIT
);
A value of 1 can represent true or active, while 0 can represent false or inactive.
MONEY and SMALLMONEY
The MONEY data type is used to store monetary values such as salary, fees, price and transaction amounts.
SMALLMONEY is used when a smaller monetary range is sufficient.
CREATE TABLE Employees
(
EmployeeId INT,
EmployeeName VARCHAR(100),
Salary MONEY
);
UNIQUEIDENTIFIER
The UNIQUEIDENTIFIER data type stores a globally unique identifier, commonly called a GUID.
It is useful when records need identifiers that are unique across different systems or databases.
CREATE TABLE Customers
(
CustomerId UNIQUEIDENTIFIER,
CustomerName VARCHAR(100)
);
INSERT INTO Customers
(
CustomerId,
CustomerName
)
VALUES
(
NEWID(),
'Rahul'
);
BINARY and VARBINARY
BINARY and VARBINARY are used to store binary data.
They can be useful for storing binary information such as hashes or other application-specific binary values.
CREATE TABLE Files
(
FileId INT,
FileData VARBINARY(MAX)
);
XML Data Type
The XML data type is used to store XML documents and XML fragments.
CREATE TABLE StudentDetails
(
StudentId INT,
StudentData XML
);
Example XML value:
<Student>
<Name>Rahul</Name>
<City>Pune</City>
</Student>
SQL_VARIANT
The SQL_VARIANT data type can store values of several supported SQL Server data types.
It should be used carefully because it is intended for specialized scenarios rather than normal application columns.
CREATE TABLE DynamicValues
(
Id INT,
ValueData SQL_VARIANT
);
INSERT INTO DynamicValues
VALUES
(1, 100);
INSERT INTO DynamicValues
VALUES
(2, 'Hello');
Practical Example
The following example demonstrates several specialized data types in a single table.
CREATE TABLE EmployeeDetails
(
EmployeeId INT,
EmployeeName VARCHAR(100),
IsActive BIT,
Salary MONEY,
EmployeeGuid UNIQUEIDENTIFIER,
EmployeeData XML
);
Insert Data
INSERT INTO EmployeeDetails
(
EmployeeId,
EmployeeName,
IsActive,
Salary,
EmployeeGuid,
EmployeeData
)
VALUES
(
1,
'Rahul',
1,
45000.50,
NEWID(),
'<Employee>
<Department>IT</Department>
<City>Pune</City>
</Employee>'
);
Output
| EmployeeId | EmployeeName | IsActive | Salary | EmployeeGuid | EmployeeData |
|---|---|---|---|---|---|
| 1 | Rahul | 1 | 45000.50 | 6F9619FF-8B86-D011-B42D-00C04FC964FF | <Employee>...</Employee> |
The exact GUID value generated by NEWID() will be different each time.
When Should We Use These Data Types?
| Requirement | Recommended Type |
|---|---|
| True / False or Active / Inactive | BIT |
| Money or currency values | MONEY / SMALLMONEY |
| Globally unique identifier | UNIQUEIDENTIFIER |
| Binary information | VARBINARY |
| XML documents | XML |
| Specialized mixed-type values | SQL_VARIANT |
Important
Always choose a data type according to the actual requirement of the application. Specialized data types should be used only when they provide a clear benefit for the data being stored.
Summary
In this section, we learned about several specialized SQL Server data types including BIT, MONEY, SMALLMONEY, UNIQUEIDENTIFIER, BINARY, VARBINARY, XML and SQL_VARIANT. We also learned their practical uses and examples.