SQL Server

Microsoft SQL Server is a mature, full-featured relational database used heavily in enterprises and Microsoft-centric organizations. It combines solid relational fundamentals — ACID transactions, a cost-based optimizer, stored procedures — with deep tooling (SQL Server Management Studio, Azure Data Studio), built-in high availability, and tight integration with .NET, Active Directory, and Azure.

It runs on Windows and Linux, in containers, and as managed services: Azure SQL Database, Azure SQL Managed Instance, and Amazon RDS for SQL Server. Its dialect, T-SQL, extends standard SQL with procedural logic, error handling, and SQL Server–specific features.

TL;DR

Quick Example

A table with a clustered primary key, a covering nonclustered index, and a parameterized stored procedure:

The INCLUDE columns make the index covering: SQL Server answers the query from the index alone, with no key lookups.

Core Concepts

Clustered and Nonclustered Indexes

A clustered index is the table: rows are stored in the leaf level in key order, so a table has at most one. A nonclustered index is a separate B-tree that points back to the clustered key. Choose a narrow, ever-increasing clustered key (like an IDENTITY or sequential GUID) to avoid page splits and keep nonclustered indexes small. Columnstore indexes store data by column for analytics and can compress 10x.

See Database Indexing for index design in general.

Isolation, Locking, and Blocking

SQL Server defaults to READ COMMITTED using locks, so long-running writes can block readers. Enabling Read Committed Snapshot Isolation makes readers see the last committed version from the version store in tempdb instead of waiting:

Azure SQL Database enables RCSI by default. See Database Transactions for isolation levels and anomalies.

The Query Optimizer and Query Store

The cost-based optimizer compiles and caches execution plans. Parameter sniffing — a plan compiled for one parameter value reused for very different values — is a classic cause of sudden slowdowns. Query Store records plans and runtime statistics over time, so you can spot regressions and force a known-good plan:

High Availability and Disaster Recovery

Azure SQL Database and Managed Instance include HA and automated backups. See Database Replication and Database Backups.

Editions and Licensing

Express (free, small databases), Developer (free, non-production, full features), Standard, and Enterprise. Enterprise unlocks advanced features such as full availability groups with multiple readable secondaries, online index operations, and higher resource limits. Licensing is typically per core, which often drives architecture decisions — consolidating workloads or moving to Azure SQL.

Best Practices

Use Parameterized Queries Everywhere

Parameterization prevents SQL injection and lets SQL Server reuse plans. In .NET, use SqlParameter or an ORM such as Entity Framework Core.

Enable RCSI for OLTP Workloads

Most applications benefit from readers not blocking on writers. Monitor tempdb since the version store lives there.

Turn On Query Store

Enable it on every production database with sensible size limits. It turns "the app got slow on Tuesday" into a concrete query and plan change.

Maintain Statistics

Outdated statistics lead to bad plans. Keep AUTO_UPDATE_STATISTICS on and schedule UPDATE STATISTICS for large tables with skewed data.

Size and Configure tempdb

Use multiple equally sized data files (commonly one per core up to eight) on fast storage. Many concurrency problems are really tempdb contention.

Test Restores, Not Just Backups

Schedule restore drills and run DBCC CHECKDB to detect corruption early.

Common Mistakes

Heaps and Random GUID Clustered Keys

Tables without a clustered index (heaps) fragment and bloat. Random NEWID() clustered keys cause constant page splits; use NEWSEQUENTIALID() or an integer key.

NOLOCK Everywhere

WITH (NOLOCK) reads uncommitted data and can return duplicated or missing rows. Fix blocking with RCSI and better indexes instead.

Functions on Indexed Columns

WHERE YEAR(CreatedAt) = 2026 can't seek an index on CreatedAt. Rewrite as a range: CreatedAt >= '2026-01-01' AND CreatedAt < '2027-01-01'.

Implicit Conversions

Comparing an NVARCHAR parameter to a VARCHAR column forces a conversion on every row and disables seeks. Match parameter types to column types.

Shrinking Databases on a Schedule

Routine DBCC SHRINKDATABASE fragments indexes and the files grow right back.

Comparison: SQL Server vs PostgreSQL

FAQ

What is SQL Server used for?

SQL Server stores and queries relational data for business applications, especially in organizations using .NET, Windows Server, and Azure. It also supports analytics with columnstore indexes, reporting with SSRS, and ETL with SSIS.

Is SQL Server free?

The Express edition is free with size and resource limits, and the Developer edition is free with all features for non-production use. Production workloads beyond Express limits need Standard or Enterprise licenses, or a managed Azure SQL service.

What is T-SQL?

Transact-SQL is Microsoft's SQL dialect. It adds variables, control flow, error handling with TRY...CATCH, temporary tables, and many built-in functions to standard SQL.

Should I choose SQL Server or PostgreSQL?

Choose SQL Server when your organization is invested in Microsoft tooling, needs its enterprise HA features, or has existing licenses. Choose PostgreSQL for open-source licensing, extensibility, and broad cloud portability.

Why is my SQL Server query suddenly slow?

Common causes are parameter sniffing, outdated statistics, missing indexes, or blocking. Query Store shows whether the plan changed; sys.dm_exec_requests and sys.dm_os_waiting_tasks show current blocking.

Related Topics

References