Skip to content

SQL Server: start here

Microsoft SQL Server is a relational database management system: it stores data in tables, enforces rules on that data, and answers queries written in Transact-SQL (T-SQL), Microsoft’s dialect of SQL. The same database engine also powers Azure SQL Database, Azure SQL Managed Instance and SQL database in Microsoft Fabric, so most of what you learn here carries over to the cloud.

This page is the entry point for the SQL Server section. It explains the product and its editions, the tools you need, the order in which to read the T-SQL guides, the key terms you will meet, and the two operational basics every developer should understand: backups and security.

Topic What it covers
What SQL Server is Versions, editions and where SQL Server runs.
Tools and installation A free edition, SSMS, VS Code and sqlcmd.
Reading path The guides in this section, in learning order.
Key terms ACID, indexes, execution plans, tempdb, isolation levels and more.
Backup and restore Recovery models and the backup types you need.
Security basics Authentication, least privilege, encryption and auditing.

The current release is SQL Server 2025 (version 17.x), generally available since November 2025; SQL Server 2022 (16.x) and 2019 (15.x) are still common in production. It runs on Windows, on Linux, and in containers. Typical uses are line-of-business applications, web back ends, reporting and data warehousing; the wider product family adds SQL Server Integration Services (SSIS) for ETL and Analysis Services for OLAP models, and from SQL Server 2025 on-premises reporting is consolidated under Power BI Report Server.

SQL Server 2025 editions, from Microsoft Learn:

Edition Use
Enterprise Mission-critical production workloads; all features.
Standard Production for small and mid-size workloads; in 2025 limited to the lesser of 4 sockets or 32 cores and 256 GB buffer pool memory.
Express Free, for learning and small applications; 50 GB maximum database size in 2025. Includes LocalDB.
Enterprise Developer and Standard Developer Free, full features of Enterprise or Standard, licensed for development and test only. New in 2025 as two separate editions.
Evaluation All Enterprise features for 180 days.

The Web edition is discontinued in SQL Server 2025. Feature availability differs by edition (for example, online metadata-only ADD COLUMN … NOT NULL DEFAULT is an Enterprise optimization), so test on the developer edition that matches your production edition.

Pitfalls:

  • Developing on Enterprise Developer, deploying to Standard. Some features and limits differ; use Standard Developer when production is Standard.
  • Assuming the cloud is identical. Azure SQL Database has no SQL Server Agent and uses READ_COMMITTED_SNAPSHOT by default; check the feature comparison before migrating.

To learn T-SQL locally, install SQL Server 2025 Express or a Developer edition from the Microsoft download page, or run the SQL Server container image on Linux or macOS. During setup choose the authentication mode: Windows authentication only (recommended) or mixed mode, which also enables SQL Server logins.

Then install a client:

  • SQL Server Management Studio (SSMS) 22, the Windows tool for querying and administration. It is updated monthly; release 22.12.0 shipped on November 10, 2026. Recent releases include GitHub Copilot integration, Database DevOps (SQL projects) and Schema Compare.
  • Visual Studio Code with the MSSQL extension, cross-platform, good for developers who already live in VS Code.
  • sqlcmd, the command-line utility, useful for scripts and CI pipelines.

Create a scratch database and run the sample data script from the querying guide; every guide in this section uses it.

CREATE DATABASE OrdersPortal;
GO
USE OrdersPortal;
GO
SELECT @@VERSION AS ServerVersion, DB_NAME() AS CurrentDatabase;

Not run here; the first column shows the build and edition of your server, the second OrdersPortal.

Read these in order; each builds on the sample data introduced in the first.

Step Page What you learn
1 Querying data SELECT, WHERE, sorting and paging, NULL, aggregates, GROUP BY.
2 Joins, subqueries and CTEs Combining tables and queries, recursion, UNION/INTERSECT/EXCEPT.
3 Functions and stored procedures String, date and conversion functions; UDFs and procedures.
4 Changing data INSERT, UPDATE, DELETE, OUTPUT and transactions.
5 Tables, constraints and indexes Data types, DDL, constraints and index design.
6 Temporal tables Automatic row history with system-versioned tables.
7 SQL Server Integration Services ETL packages, from fundamentals to deployment and modernization.
8 SQL Server interview questions Check yourself before an interview.

Where to go next: compare dialects in the PostgreSQL, MySQL and Oracle sections.

  • ACID: atomicity, consistency, isolation and durability, the guarantees a transaction gives. SQL Server provides durability through write-ahead logging to the transaction log.
  • Transaction log: the file that records every change before it is written to data files; it is used for crash recovery, log backups and features such as availability groups.
  • Clustered index: the B+ tree whose leaf level holds the table’s rows in key order; one per table. A table without one is a heap.
  • Nonclustered index: a separate B+ tree of key columns plus a pointer to the row; up to 999 per table. See Indexes.
  • Execution plan: the operators the query optimizer chose to run a query (seeks, scans, joins, sorts). The estimated plan is compiled without running; the actual plan adds runtime row counts. Query Store keeps plan and runtime history per database.
  • Statistics: histograms of column value distributions that the optimizer uses to estimate row counts.
  • Sargable: a predicate that can use an index seek, typically a bare column compared with a value.
  • tempdb: the system database for temporary tables, table variables, sorts and hash spills, and the row-version store. It is recreated on every restart and is shared by all databases on the instance.
  • Isolation levels: READ UNCOMMITTED, READ COMMITTED (the SQL Server default), REPEATABLE READ, SERIALIZABLE and SNAPSHOT. With the READ_COMMITTED_SNAPSHOT database option on (the default in Azure SQL Database), READ COMMITTED reads row versions instead of taking shared locks.
  • Blocking and deadlock: blocking is one session waiting for another’s lock; a deadlock is two sessions waiting for each other, which SQL Server resolves by rolling one back with error 1205.
  • Schema: a namespace that owns objects, such as dbo or sales, and a unit for granting permissions.

A backup strategy starts with the database recovery model. Under the simple model the log is truncated automatically and you can only restore to the time of a full or differential backup. Under the full model every change stays in the log until a log backup copies it, which lets you restore to a point in time, but log backups are then mandatory or the log keeps growing. A typical schedule combines periodic full backups, differential backups (changes since the last full) and frequent log backups.

Add CHECKSUM to detect corruption while backing up, use COMPRESSION to save space and time, and prove that backups work by restoring them regularly to another server; RESTORE VERIFYONLY checks readability, not that the database is consistent. A restore sequence applies the full backup WITH NORECOVERY, then the latest differential, then each log backup in order, and finishes WITH RECOVERY.

ALTER DATABASE OrdersPortal SET RECOVERY FULL;
BACKUP DATABASE OrdersPortal
TO DISK = N'D:\Backups\OrdersPortal_full.bak'
WITH COMPRESSION, CHECKSUM, INIT;
BACKUP LOG OrdersPortal
TO DISK = N'D:\Backups\OrdersPortal_log_0915.trn'
WITH COMPRESSION, CHECKSUM;
-- Restore to a point in time (on a test server, or after a tail-log backup)
RESTORE DATABASE OrdersPortal FROM DISK = N'D:\Backups\OrdersPortal_full.bak' WITH NORECOVERY, REPLACE;
RESTORE LOG OrdersPortal FROM DISK = N'D:\Backups\OrdersPortal_log_0915.trn'
WITH STOPAT = '2026-11-18T09:10:00', RECOVERY;

Not run here. The paths are examples; Express edition has no SQL Server Agent, so schedule backups with Windows Task Scheduler and sqlcmd, or use SQL Server Agent jobs or maintenance plans on Standard and Enterprise.

Pitfalls:

  • Full recovery without log backups. The log grows until the disk fills. Either schedule log backups or use the simple model.
  • Backups on the same disk as the data. Copy backups off the server, ideally to immutable storage such as backup to URL.
  • Never testing a restore. A backup is only proven when it has been restored and checked with DBCC CHECKDB.

Prefer Windows authentication or Microsoft Entra ID authentication over SQL logins, because passwords and policies are managed centrally. Grant the least privilege an application needs: create a database user for each application, put permissions on roles or schemas, and give EXECUTE on stored procedures rather than broad table rights; never run an application as sa or a sysadmin member. Always use parameterized queries or sp_executesql with parameters to prevent SQL injection.

For data protection, connections should be encrypted with TLS. Transparent Data Encryption (TDE) encrypts data and log files at rest; Always Encrypted keeps selected columns encrypted even from database administrators, because keys stay with the client; dynamic data masking and row-level security limit what users see. SQL Server Audit records who did what for compliance, and the ledger feature (SQL Server 2022 and later) makes tampering with table history detectable.

CREATE ROLE OrdersApp;
GRANT EXECUTE ON SCHEMA::dbo TO OrdersApp; -- procedures only, no direct table access
CREATE USER OrdersPortalWeb WITHOUT LOGIN; -- demo user; in production map a login or Entra identity
ALTER ROLE OrdersApp ADD MEMBER OrdersPortalWeb;

Not run here.

Pitfalls:

  • Shared admin accounts. Everyone using sa makes auditing meaningless; disable or rename sa and use individual identities.
  • Ownership chaining surprises. EXECUTE on a procedure works without table permissions only when the procedure and tables share an owner; dynamic SQL inside the procedure breaks the chain.
  • Secrets in scripts. Keep passwords and connection strings out of source control; use a secret store.