SQL Data Types and Constraints

SQL data types define the kind of data that can be stored in a column, table variable, or parameter. They enforce data integrity by restricting the format, range, and precision of values. In Microsoft SQL Server (the engine behind Azure SQL Database), data types are grouped into categories: exact numeric, approximate numeric, date and time, character strings, Unicode character strings, binary strings, and others.

Exact numeric types store numbers with precise precision and scale. The main types are: - INT: 4-byte integer, range -2,147,483,648 to 2,147,483,647. - BIGINT: 8-byte integer, range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. - SMALLINT: 2-byte integer, range -32,768 to 32,767. - TINYINT: 1-byte integer, range 0 to 255. - DECIMAL(p, s) and NUMERIC(p, s): Fixed precision and scale. p (precision) is the total number of digits, s (scale) is the number of digits after the decimal point. Maximum precision is 38. Storage depends on precision: up to 9 digits use 5 bytes, up to 19 use 9 bytes, up to 28 use 13 bytes, up to 38 use 17 bytes. - MONEY and SMALLMONEY: Store currency values with fixed precision. MONEY uses 8 bytes, range -922,337,203,685,477.5808 to 922,337,203,685,477.5807. SMALLMONEY uses 4 bytes, range -214,748.3648 to 214,748.3647. These are exact numeric types but not commonly used; DECIMAL is preferred.

- FLOAT(n): Approximate number with n bits of mantissa. n can be 1-53. Default is 53 (8 bytes). Range: -1.79E+308 to 1.79E+308. - REAL: Equivalent to FLOAT(24), 4 bytes. Range: -3.40E+38 to 3.40E+38. Approximate types should not be used for financial calculations because they can introduce rounding errors. Use DECIMAL instead.

For Unicode data (e.g., storing multiple languages), use NCHAR and NVARCHAR. NVARCHAR(MAX) stores up to 2 GB of Unicode text.

Constraints are rules enforced on data columns to ensure data integrity. They are defined at table creation or added later with ALTER TABLE. The main types are:

A PRIMARY KEY uniquely identifies each row in a table. It enforces both UNIQUE and NOT NULL. A table can have only one PRIMARY KEY, but it can consist of multiple columns (composite key). The database automatically creates a unique clustered index on the primary key columns by default (unless specified otherwise).

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100)
);

A FOREIGN KEY links two tables. It ensures that values in a column (or columns) match values in the primary key or unique constraint of another table. This enforces referential integrity. Foreign keys can also reference the same table (self-referencing).

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT FOREIGN KEY REFERENCES Customers(CustomerID)
);

A UNIQUE constraint ensures that all values in a column are distinct. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and they allow one NULL value (in SQL Server). UNIQUE constraints create a unique nonclustered index by default.

A CHECK constraint validates data based on a logical expression. It can reference multiple columns and can include subqueries in some cases.

Example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Price DECIMAL(10,2) CHECK (Price >= 0),
    Quantity INT CHECK (Quantity >= 0)
);

A DEFAULT constraint provides a default value for a column when no value is specified in an INSERT. It can be a constant, a system function (e.g., GETDATE()), or a scalar expression.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    HireDate DATETIME DEFAULT GETDATE()
);

When a DML statement (INSERT, UPDATE, DELETE) is executed, the SQL Server engine checks each constraint in a specific order: 1. DEFAULT constraints are applied first (for INSERT). 2. NULL/NOT NULL checks. 3. CHECK constraints are validated. 4. UNIQUE and PRIMARY KEY constraints check for duplicates. 5. FOREIGN KEY constraints check referential integrity.

If any constraint is violated, the entire statement is rolled back. For bulk operations, constraints can be disabled (WITH NOCHECK) but this is not recommended for production.

PRIMARY KEY and UNIQUE constraints automatically create indexes (clustered for PK, nonclustered for UNIQUE by default). Foreign keys do not create indexes, but it is best practice to index foreign key columns to improve join performance.

When comparing values of different data types, SQL Server uses data type precedence. The type with higher precedence is converted to. For example, INT has higher precedence than SMALLINT. Implicit conversions are allowed between compatible types (e.g., INT to DECIMAL). Explicit conversion requires CAST or CONVERT.

Use system views:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
SELECT * FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS;
SELECT * FROM sys.check_constraints;

Data types and constraints are the backbone of relational database schema design. They enforce data integrity at the database level, reducing application complexity. On the DP-900 exam, focus on understanding the purpose of each constraint and the characteristics of common data types, especially exact vs. approximate numeric, fixed vs. variable-length strings, and date/time types.

In a production e-commerce system using Azure SQL Database, data types and constraints are critical for data integrity. For example, the Products table uses DECIMAL(10,2) for Price to ensure exact monetary calculations. A CHECK constraint ensures Price >= 0. The table has a PRIMARY KEY on ProductID (INT) and a UNIQUE constraint on ProductName to prevent duplicates. The Orders table has a FOREIGN KEY referencing Customers, with ON DELETE CASCADE to automatically remove orders when a customer is deleted (though this is often avoided in practice). A common misconfiguration is using FLOAT for Price, leading to rounding errors in financial reports. Another scenario is a logging table that stores timestamps. Using DATETIME2(3) instead of DATETIME provides better precision and avoids the 1753 year limit. For large text fields like product descriptions, NVARCHAR(MAX) is used to support Unicode. However, indexing such columns is problematic; a full-text index might be used instead. In a data warehouse scenario, dimension tables use surrogate keys (INT IDENTITY) as PRIMARY KEY, while fact tables use composite foreign keys referencing dimensions. Constraints ensure that no fact row references a non-existent dimension. When loading data with Azure Data Factory, constraints may be temporarily disabled for bulk inserts to improve performance, but this risks data quality. A real-world issue: forgetting to add a UNIQUE constraint on email columns in a Customer table can lead to duplicate customer records, causing confusion in CRM systems. Azure SQL Database provides tools like SQL Server Management Studio or Azure Data Studio to visually manage constraints. Performance-wise, too many constraints (especially CHECK constraints with complex expressions) can slow down DML operations. Best practice is to use constraints for critical business rules and handle complex validation in application logic. Lastly, when migrating on-premises SQL Server databases to Azure SQL, data type compatibility must be checked; for example, the deprecated TEXT type must be converted to VARCHAR(MAX).