SQL Data Types

This section provides an overview of data types in SQL.

In a database or programming language, a data type is a categorisation that indicates the kind of data that can be stored in a variable or column. It establishes what kinds of values can be saved and what actions can be taken with those data. Determining the structure of data, guaranteeing data integrity, and maximising storage and performance all depend on data types.

Here is a summary of the main data types in Microsoft SQL Server (MS SQL):

Numeric Data Types

  • INT: Integer data from -2,147,483,648 to 2,147,483,647 (4 bytes).
  • BIGINT: Larger integer data from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (8 bytes).
  • SMALLINT: Smaller integer data from -32,768 to 32,767 (2 bytes).
  • TINYINT: Very small integer data from 0 to 255 (1 byte).
  • DECIMAL(p, s) or NUMERIC(p, s): Fixed precision and scale numbers. p is precision (total number of digits), and s is scale (number of digits to the right of the decimal point).
  • FLOAT(n): Floating-point number, n is the number of bits used to store the mantissa. Default is 53 (double precision, 8 bytes); 24 is single precision (4 bytes).
  • REAL: Floating-point number (single precision, 4 bytes).

Character Data Types

  • CHAR(n): Fixed-length non-Unicode characters. n specifies the string length (1 to 8,000 characters).
  • VARCHAR(n): Variable-length non-Unicode characters. n specifies the maximum length (1 to 8,000 characters). VARCHAR(MAX) can store up to 2 GB.
  • NCHAR(n): Fixed-length Unicode characters. n specifies the string length (1 to 4,000 characters).
  • NVARCHAR(n): Variable-length Unicode characters. n specifies the maximum length (1 to 4,000 characters). NVARCHAR(MAX) can store up to 2 GB.

Date and Time Data Types

  • DATE: Stores date only (YYYY-MM-DD), from 0001-01-01 to 9999-12-31.
  • TIME: Stores time only (hh:mm:ss[.nnnnnnn]), from 00:00:00.0000000 to 23:59:59.9999999.
  • DATETIME: Stores both date and time, from 1753-01-01 to 9999-12-31, with an accuracy of 3.33 milliseconds.
  • SMALLDATETIME: Stores both date and time, from 1900-01-01 to 2079-06-06, with an accuracy of 1 minute.
  • DATETIME2: Stores both date and time, from 0001-01-01 to 9999-12-31, with a user-defined precision from 0 to 7 digits for fractional seconds.
  • DATETIMEOFFSET: Stores both date and time with a time zone offset, similar to DATETIME2 but with an additional time zone offset.

Binary Data Types

  • BINARY(n): Fixed-length binary data. n specifies the number of bytes (1 to 8,000).
  • VARBINARY(n): Variable-length binary data. n specifies the maximum number of bytes (1 to 8,000). VARBINARY(MAX) can store up to 2 GB.

Other Data Types

  • BIT: Integer data type that can take a value of 0, 1, or NULL.
  • UNIQUEIDENTIFIER: Stores a globally unique identifier (GUID).
  • XML: Stores XML data.
  • JSON: SQL Server supports parsing and querying JSON, but it does not have a specific JSON data type; it uses NVARCHAR.
  • GEOGRAPHY and GEOMETRY: Spatial data types for storing geographical and geometric data.
  • HIERARCHYID: Represents a position in a hierarchical structure (such as an organizational chart).

Special Data Types

  • SQL_VARIANT: Stores values of various SQL Server-supported data types, except TEXT, NTEXT, IMAGE, TIMESTAMP, and XML.
  • CURSOR: Used to store a reference to a cursor for database operations.
  • TABLE: Stores a result set for later processing, often used in table-valued parameters or functions.

Conclusion

A data type is a classification that specifies the type of data a variable or column can hold in a database or a programming language.