SQL Server Documentation

Miscellaneous Data Types in SQL Server

This section covers data types in SQL Server that do not fit neatly into the standard numeric, string, date/time, or binary categories. These types offer specialized functionality for various use cases.

UNIQUEIDENTIFIER

The UNIQUEIDENTIFIER data type stores a globally unique identifier (GUID). GUIDs are 128-bit values that are unique across all computers and all time. They are commonly used for primary keys, row identifiers, and for tracking data changes.

Syntax

DECLARE @myGuid UNIQUEIDENTIFIER;
SET @myGuid = NEWID();

Properties

• Storage size: 16 bytes.
• Format: 8-4-4-4-12 hexadecimal digits, enclosed in curly braces (e.g., '6F96197D-8B96-11D4-8468-00C04FD918D0').
• Functions: NEWID() generates a new GUID, NEWSEQUENTIALID() generates a GUID that is guaranteed to be greater than any previously generated GUID on the same computer.

HIERARCHYID

The HIERARCHYID data type is used to represent positions in a hierarchy. It is optimized for storing and querying hierarchical data, such as organizational charts or file system structures.

Syntax

CREATE TABLE OrganizationalChart
(
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    EmployeeLevel HIERARCHYID
);

Properties

• Efficient storage and querying of tree-like structures.
• Supports operations like finding ancestors, descendants, and siblings.

SQL_VARIANT

The SQL_VARIANT data type can store values of various other SQL Server data types, except for those that cannot be compared or sorted (e.g., TEXT, NTEXT, IMAGE, XML, HIERARCHYID, GEOMETRY, GEOGRAPHY, and user-defined types). It stores the value and information about its original data type.

Use Cases

• Storing heterogeneous data in a single column.
• Implementing flexible schemas.

TABLE

The TABLE data type is a special type used to store a result set for temporary processing. It is primarily used for table-valued parameters (TVPs) and within functions. It is not a physical table and does not have constraints or indexes unless defined as part of a TVP or function.

Example (Table-Valued Parameter)

-- Define a table type
CREATE TYPE EmployeeList AS TABLE
(
    EmployeeID INT,
    EmployeeName VARCHAR(100)
);
GO

-- Stored procedure that accepts a TVP
CREATE PROCEDURE ProcessEmployees
    @Employees EmployeeList READONLY
AS
BEGIN
    SELECT EmployeeID, EmployeeName FROM @Employees;
END;
GO

XML

The XML data type allows you to store XML data in a native XML format. This enables efficient querying and manipulation of XML content using XQuery and XML-based functions.

Properties

• Supports both well-formed and valid XML.
• Can be indexed for improved query performance.

GEOMETRY and GEOGRAPHY

These are spatial data types used to store data representing geometric shapes (GEOMETRY) and geographical locations on the Earth's surface (GEOGRAPHY). They are crucial for applications involving mapping, location services, and spatial analysis.

GEOMETRY

• Represents data in a flat, Euclidean (planar) coordinate system.
• Commonly used for data within a defined geographic area where Earth's curvature is negligible.

GEOGRAPHY

• Represents data on a spherical or ellipsoidal model of the Earth.
• Uses latitude and longitude coordinates.
• Ideal for global or large-scale regional applications.

VARBINARY(MAX) and VARCHAR(MAX) (Large Object Types)

While VARBINARY and VARCHAR are standard types, their MAX versions (VARBINARY(MAX) and VARCHAR(MAX), formerly TEXT and IMAGE) are important for storing large amounts of binary or character data, respectively. They can store up to 2^31-1 bytes (2 GB) of data.

DATETIME2 and SMALLDATETIME2 (Temporal Precision)

These are more precise versions of DATETIME and SMALLDATETIME, offering a wider date range and higher precision for fractional seconds. They are generally recommended over the older date/time types.