You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
What is SQL Server
What is SQL Server
Microsoft SQL Server is one of the most widely used relational database management systems (RDBMS) in the world. It provides a comprehensive platform for storing, querying, and managing structured data — from small applications to massive enterprise workloads.
A Brief History
- 1989 — Microsoft and Sybase release SQL Server 1.0 for OS/2
- 1993 — Microsoft releases SQL Server 4.21 for Windows NT, the first version developed independently from Sybase
- 2000 — SQL Server 2000 introduces XML support, indexed views, and user-defined functions
- 2005 — SQL Server 2005 brings CLR integration, Service Broker, and SQL Server Management Studio (SSMS)
- 2012 — SQL Server 2012 adds AlwaysOn Availability Groups, columnstore indexes, and contained databases
- 2016 — SQL Server 2016 introduces temporal tables, row-level security, dynamic data masking, and JSON support
- 2017 — SQL Server 2017 launches on Linux and Docker for the first time
- 2019 — SQL Server 2019 brings Big Data Clusters, Intelligent Query Processing, and Accelerated Database Recovery
- 2022 — SQL Server 2022 adds deep Azure integration, ledger tables, and query store enhancements
- Today — SQL Server runs on Windows, Linux, Docker, and Kubernetes, with Azure SQL as the cloud counterpart
SQL Server evolved from a small departmental database into a full enterprise data platform supporting OLTP, analytics, machine learning, and hybrid cloud.
SQL Server Editions
Microsoft offers multiple editions to suit different workloads and budgets:
| Edition | Use Case | Key Limits |
|---|---|---|
| Enterprise | Mission-critical workloads | No limits on compute or memory; full feature set |
| Standard | Mid-tier applications | Up to 24 cores and 128 GB RAM |
| Web | Web hosting providers | Licensed through hosting partners only |
| Developer | Development and testing | Full Enterprise features, free, not for production |
| Express | Small applications and learning | Max 1 GB RAM, 10 GB database size, 4 cores |
Tip: The Developer edition has every feature of Enterprise and is completely free — perfect for learning.
Core Components
Database Engine
The heart of SQL Server — responsible for storing, processing, and securing data. It handles:
- Query execution
- Transaction management (ACID compliance)
- Locking and concurrency
- Data storage and retrieval
SQL Server Management Studio (SSMS)
SSMS is the primary GUI tool for managing SQL Server:
- Write and execute T-SQL queries
- Design databases, tables, and indexes
- Monitor performance with Activity Monitor
- Configure security and jobs
Azure Data Studio
A lightweight, cross-platform editor for SQL Server and Azure databases:
- Runs on Windows, macOS, and Linux
- Supports Jupyter notebooks for T-SQL
- Built-in charting and dashboards
- Extension marketplace
sqlcmd
The command-line utility for running T-SQL scripts:
sqlcmd -S localhost -U sa -P YourPassword -Q "SELECT @@VERSION"
SQL Server on Azure
Microsoft provides several cloud-hosted options:
| Service | Description |
|---|---|
| Azure SQL Database | Fully managed single database (PaaS) |
| Azure SQL Managed Instance | Near 100% compatibility with on-premises SQL Server |
| SQL Server on Azure VMs | Full SQL Server running on a virtual machine (IaaS) |
| Azure Synapse Analytics | Enterprise data warehouse with integrated analytics |
Azure SQL Database is ideal for new cloud-native applications, while Managed Instance is designed for migrating existing on-premises workloads with minimal changes.
Key Features at a Glance
| Feature | Description |
|---|---|
| T-SQL | Transact-SQL — Microsoft's extended SQL dialect |
| Stored Procedures | Pre-compiled T-SQL routines stored in the database |
| Triggers | Automatic actions on INSERT, UPDATE, or DELETE |
| Indexes | Clustered, nonclustered, columnstore, full-text |
| Security | Row-level security, dynamic data masking, TDE |
| High Availability | AlwaysOn Availability Groups, failover clustering |
| Replication | Transactional, merge, and snapshot replication |
| In-Memory OLTP | Memory-optimised tables for ultra-fast transactions |
| JSON and XML | Native support for semi-structured data |
| Machine Learning | R and Python integration with ML Services |
Summary
Microsoft SQL Server is a mature, feature-rich relational database platform that runs on Windows, Linux, Docker, and Azure. With editions ranging from the free Express and Developer to the full-featured Enterprise, SQL Server is accessible to learners and powerful enough for the largest enterprises. In the following lessons, we will explore SQL Server's architecture, T-SQL, indexing, security, and administration in detail.