SQL Server.

SQL Server is Microsoft's relational database management system (RDBMS), queried with T-SQL, Microsoft's dialect of SQL. It runs on Windows and Linux, and Azure SQL Database runs the same engine in the cloud. Its main components are:

• The Database Engine, the core service for storing, processing and securing data.
• SQL Server Agent, for job scheduling and automation.
• SQL Server Integration Services (SSIS), for data integration and transformation.
• SQL Server Analysis Services (SSAS), for analytical models. Its data mining feature was discontinued in SQL Server 2022.
• SQL Server Reporting Services (SSRS), for reports. From SQL Server 2025, on-premises reporting moves to Power BI Report Server.

SQL Server Management Studio (SSMS) is the main management and development tool. It is a separate client tool that also manages Azure SQL Database.

SQL Server groups its data types by what they hold, mainly numbers, strings and dates. The type decides how much space a column takes and which values it accepts.

• Exact numbers: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, NUMERIC, MONEY
• Approximate numbers: FLOAT, REAL
• Strings: CHAR, VARCHAR, NCHAR, NVARCHAR
• Dates and times: DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME, DATETIMEOFFSET
• Binary: BINARY, VARBINARY
• Special: BIT, UNIQUEIDENTIFIER, XML

TEXT, NTEXT and IMAGE still exist, but they are deprecated. New code uses VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) instead.

SQL Server 2016 added JSON functions for JSON stored in NVARCHAR strings. A native JSON data type arrived only in SQL Server 2025 and Azure SQL Database.

Example:

CREATE TABLE Employee (
    ID INT,
    Name NVARCHAR(50),
    Salary DECIMAL(10,2),
    HireDate DATETIME
);

VARCHAR stores non-Unicode text in the code page of the column's collation, the character set that collation supports. NVARCHAR stores Unicode text, so it can hold any language.

• VARCHAR(n): n is a size in bytes, up to 8,000. With a single-byte collation such as a non-UTF-8 Latin one, each character takes 1 byte. It suits text that fits one code page, such as English.
• NVARCHAR(n): n is a size in byte-pairs, up to 4,000. Most characters take 2 bytes. Supplementary characters, those outside the basic Unicode range such as many emoji, take 4.

NVARCHAR needs more storage but supports international text. Unicode literals need the N prefix, as in N'Zürich', or they are converted to the database code page and can lose characters. From SQL Server 2019, a UTF-8 collation lets VARCHAR store Unicode too.

A join combines rows from two tables based on a related column, and the join type decides what happens to rows that have no match.

• INNER JOIN returns only the rows that match in both tables.
• LEFT JOIN returns all rows from the left table and the matching rows from the right.
• RIGHT JOIN returns all rows from the right table and the matching rows from the left.
• FULL OUTER JOIN returns all rows from both tables, with NULL where a side has no match.
• CROSS JOIN returns the Cartesian product, every row paired with every row.

Rows with a NULL join key never match, so an inner join drops them.

T-SQL adds CROSS APPLY and OUTER APPLY, which run a table-valued function or subquery once for each left row. CROSS APPLY keeps only left rows that get a result, and OUTER APPLY keeps every left row, with NULL when nothing comes back.

Example:

-- INNER JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.ID;

A PRIMARY KEY uniquely identifies each row in a table, and a FOREIGN KEY makes a column refer to another table's primary key. A primary key can't be NULL, it must be unique, and a table has only one. SQL Server enforces it with a unique index, clustered by default when the table has no clustered index yet.

A foreign key rejects a value with no matching primary key in the parent table, though NULL is allowed. By default, deleting a parent row that still has child rows fails with an error. With ON DELETE CASCADE, the child rows are deleted too, and with ON DELETE SET NULL, their foreign key becomes NULL.

Example:

CREATE TABLE Departments (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50)
);

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name NVARCHAR(50),
    DeptID INT FOREIGN KEY REFERENCES Departments(ID)
);