Transact-SQL Data Types

Overview of Data Types

Transact-SQL supports a rich set of data types for storing various kinds of information, including numeric, character, date and time, binary, and spatial data. Choosing the correct data type is crucial for data integrity, storage efficiency, and query performance.

Data types can be broadly categorized into:

• Exact Numeric: Integers and exact decimal values.
• Approximate Numeric: Floating-point numbers.
• Date and Time: Values representing dates and times.
• Character Strings: Unicode and non-Unicode characters.
• Binary Strings: Raw byte data.
• Other Data Types: Including XML, spatial types, JSON, GUIDs, etc.

Common Transact-SQL Data Types

Here is a summary of some of the most frequently used data types:

Numeric Data Types

Exact Numeric Types

These types store exact numerical values. They are suitable for financial calculations and situations where precision is paramount.

• BIT: Stores 0, 1, or NULL. Useful for flag values.
• TINYINT: Integers from 0 to 255.
• SMALLINT: Integers from -32,768 to 32,767.
• INT: Standard integers. Range: -2,147,483,648 to 2,147,483,647.
• BIGINT: Large integers. Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• DECIMAL(p,s) and NUMERIC(p,s): Fixed-point numbers where p (precision) is the maximum total number of digits that can be stored (both to the left and right of the decimal point), and s (scale) is the number of digits to the right of the decimal point.
• MONEY and SMALLMONEY: Currency values, maintaining precision at 4 decimal places.

Approximate Numeric Types

These types store approximate floating-point values. They are generally used for scientific calculations where exact precision is not required.

• FLOAT(n): Floating-point numbers where n specifies the number of bits for the mantissa and determines precision. Range of accuracy is approximately 15 decimal digits for 53 bits and 7 decimal digits for 24 bits.
• REAL: Single-precision floating-point number, equivalent to FLOAT(24).

Date and Time Data Types

SQL Server offers several data types for storing date and time information, with varying precision and ranges.

• DATE: Stores a date value.
• TIME(n): Stores time of day only. n specifies fractional seconds precision.
• SMALLDATETIME: Stores date and time from January 1, 1753, through June 6, 1972. Accuracy is to the minute.
• DATETIME: Stores date and time from January 1, 1753, through December 31, 9999. Accuracy is to .000, .003, or .007 seconds.
• DATETIME2(n): Date and time with user-defined fractional seconds precision. Offers a wider range and higher precision than DATETIME.
• DATETIMEOFFSET(n): Date and time with time zone offset.

Character String Data Types

These types store textual data. The choice between Unicode (NVARCHAR, NCHAR) and non-Unicode (VARCHAR, CHAR) depends on whether you need to store international characters.

• CHAR(n): Fixed-length non-Unicode character data. Padded with spaces.
• VARCHAR(n): Variable-length non-Unicode character data.
• VARCHAR(MAX): Variable-length non-Unicode character data, can store up to 2GB.
• NCHAR(n): Fixed-length Unicode character data. Padded with spaces.
• NVARCHAR(n): Variable-length Unicode character data.
• NVARCHAR(MAX): Variable-length Unicode character data, can store up to 2GB.
• TEXT: Deprecated. Use VARCHAR(MAX) instead.
• NTEXT: Deprecated. Use NVARCHAR(MAX) instead.

Binary String Data Types

These types store raw binary data, such as images, files, or other byte streams.

• BINARY(n): Fixed-length binary data. Padded with 0x00 bytes.
• VARBINARY(n): Variable-length binary data.
• VARBINARY(MAX): Variable-length binary data, can store up to 2GB.
• IMAGE: Deprecated. Use VARBINARY(MAX) instead.

Other Important Data Types

• UNIQUEIDENTIFIER: Stores a 128-bit globally unique identifier (GUID).
• XML: Stores XML data.
• JSON: Stores JSON data (introduced in SQL Server 2016).
• CURSOR: A special data type used to store a cursor reference.
• ROWVERSION: A binary number that is guaranteed to be unique within a database. Often used for concurrency control.
• Spatial Data Types: GEOMETRY and GEOGRAPHY for storing geographical and geometric data.

Selecting the Right Data Type

When defining tables or variables, consider the following:

• Data Integrity: Use the most specific type possible to prevent invalid data (e.g., use INT for whole numbers, not FLOAT).
• Storage Space: Choose the smallest data type that can accommodate the required range of values to optimize storage.
• Performance: Smaller data types generally lead to better performance for I/O and indexing.
• Compatibility: Consider the needs of applications and other systems that will interact with the data.

Syntax Example

Here's an example of how data types are used when creating a table:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(100) NOT NULL,
    Category VARCHAR(50),
    Price DECIMAL(10, 2) CHECK (Price >= 0),
    StockQuantity INT DEFAULT 0,
    LastUpdated DATETIME2(3)
);
                    

In this example, ProductID is an auto-incrementing integer, ProductName is a required variable-length string, Price is a decimal with 10 total digits and 2 after the decimal, and LastUpdated stores date and time with millisecond precision.