Getting Started with SQL Server

Microsoft SQL Server is a relational database platform that runs on Windows, Linux, and containers. It spans from the free SQL Server Express Edition (limited to 10GB databases) to the full-featured Enterprise Edition with unlimited scale and advanced security features. SQL Server Management Studio (SSMS) is the flagship management tool, but you can also use Azure Data Studio for a modern cross-platform editor.

Installation on Windows uses the SQL Server Installation Center. On Linux, use the Microsoft repository with apt-get or yum. Docker is the fastest way to get started: docker run -e ACCEPT_EULA=Y -e SA_PASSWORD=YourPassword123 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-latest. Connect with sqlcmd or SSMS using the server address and SA credentials.

-- Connect with sqlcmd
sqlcmd -S localhost -U SA -P YourPassword123

-- Create database
CREATE DATABASE ShopDB;
GO
USE ShopDB;
GO

-- Create schema and table
CREATE SCHEMA sales;
GO

CREATE TABLE sales.orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 DEFAULT GETUTCDATE(),
    TotalAmount DECIMAL(10,2),
    Status VARCHAR(20) DEFAULT 'Pending'
);
GO

INSERT INTO sales.orders (CustomerID, TotalAmount)
VALUES (1001, 299.99);

T-SQL Programming and Stored Procedures

Transact-SQL (T-SQL) is SQL Server's extension of SQL, adding procedural programming capabilities. Stored procedures are the backbone of SQL Server applications. They encapsulate business logic, improve performance through cached execution plans, and provide security by controlling direct table access. CREATE PROCEDURE defines them, and EXECUTE runs them.

Variables in T-SQL use the @ prefix. DECLARE defines them, and SET assigns values. T-SQL supports IF/ELSE, WHILE loops, TRY/CATCH error handling, and cursors for row-by-row processing. Cursors should be your last resort, not your default tool. Set-based operations with UPDATE, INSERT, and MERGE are almost always faster.

Dynamic SQL using sp_executesql allows building queries at runtime while protecting against SQL injection. Always use parameterized queries with sp_executesql instead of concatenating strings. The OUTPUT clause in INSERT, UPDATE, DELETE, or MERGE returns affected rows, which is useful for auditing and returning generated IDs.

-- Stored procedure with error handling
CREATE PROCEDURE sales.PlaceOrder
    @CustomerID INT,
    @TotalAmount DECIMAL(10,2),
    @OrderID INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;
        INSERT INTO sales.orders (CustomerID, TotalAmount)
        VALUES (@CustomerID, @TotalAmount);
        SET @OrderID = SCOPE_IDENTITY();
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        THROW;
    END CATCH
END;
GO

-- Execute
DECLARE @NewOrderID INT;
EXEC sales.PlaceOrder @CustomerID = 1001, @TotalAmount = 150.00, @OrderID = @NewOrderID OUTPUT;
PRINT 'Order created: ' + CAST(@NewOrderID AS VARCHAR);

Index Design and Query Optimization

SQL Server offers clustered and nonclustered indexes. A clustered index determines the physical order of data in a table. Each table can have only one clustered index, typically the primary key. Nonclustered indexes are separate structures that contain index key columns and pointers to the actual data rows. Covering nonclustered indexes include all columns needed by a query, eliminating the need to access the table.

Execution plans show how SQL Server executes a query. In SSMS, click Include Actual Execution Plan (Ctrl+M) before running a query. Look for expensive operators: table scans suggest missing indexes, key lookups suggest a covering index would help, and hash matches suggest missing join indexes. The Missing Index feature suggests indexes directly in the execution plan.

Index maintenance is critical. As data changes, indexes fragment. Reorganize indexes with moderate fragmentation and rebuild indexes with high fragmentation. Use sys.dm_db_index_physical_stats to check fragmentation levels. Automate index maintenance with a scheduled job during low-traffic periods.

-- Find missing indexes
SELECT migs.avg_user_impact, migs.avg_total_user_cost,
    mid.statement AS table_name,
    'CREATE INDEX IX_' + OBJECT_NAME(mid.object_id) 
    + '_' + REPLACE(REPLACE(mid.equality_columns, ', ', '_'), '[', '') 
    + CASE WHEN mid.inequality_columns IS NOT NULL 
        THEN '_' + REPLACE(REPLACE(mid.inequality_columns, ', ', '_'), '[', '') 
        ELSE '' END 
    + ' ON ' + mid.statement 
    + ' (' + ISNULL(mid.equality_columns, '') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ', ' ELSE '' END 
    + ISNULL(mid.inequality_columns, '') + ')' 
    + ISNULL(' INCLUDE (' + mid.included_columns + ')', '') AS create_index
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC;

Transactions, Locking, and Isolation Levels

SQL Server transactions follow the standard ACID properties with some SQL Server-specific behaviors. The default isolation level is READ COMMITTED, which uses shared locks to prevent dirty reads but allows non-repeatable reads and phantom reads. Each isolation level balances consistency against concurrency.

Snapshot isolation, available in SQL Server 2005+, provides statement-level read consistency without shared locks. When enabled with ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON, readers see the last committed version of data at the start of the statement. Writers are not blocked by readers, and readers are not blocked by writers. The tempdb database stores version information in the version store.

Deadlocks occur when two transactions hold locks that the other needs. SQL Server detects deadlocks and chooses a victim by terminating the transaction with the least cost to roll back. Use SET DEADLOCK_PRIORITY to control which session is chosen as the victim. Capture deadlock graphs with system_health session or trace flag 1222 for analysis.

-- Check current blocking
SELECT session_id, blocking_session_id, wait_type, wait_time,
    command, DB_NAME(database_id) AS database_name
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;

-- Enable snapshot isolation
ALTER DATABASE ShopDB SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE ShopDB SET READ_COMMITTED_SNAPSHOT ON;

-- Check transaction info
SELECT transaction_id, transaction_begin_time, transaction_type,
    transaction_state, transaction_status
FROM sys.dm_tran_active_transactions;

High Availability: Always On and Replication

SQL Server Always On Availability Groups provide high availability and disaster recovery at the database level. An availability group contains one or more databases that fail over together. You set up a primary replica and up to eight secondary replicas. Secondary replicas can be readable, allowing you to offload reporting queries. Synchronous commit ensures zero data loss between primary and secondary.

Failover Cluster Instances (FCI) provide instance-level high availability using Windows Server Failover Clustering. Unlike availability groups, FCI protects the entire SQL Server instance, including system databases. All nodes share a single storage array. If the active node fails, another node takes over the storage and starts SQL Server.

Transaction replication is ideal for distributing data to reporting servers or consolidating data from multiple sources. The publisher maintains the source data, the distributor stores and forwards changes, and the subscriber receives them. Merge replication allows both publisher and subscriber to make changes, with a conflict resolver for collisions.

-- Check availability group state
SELECT ag.name AS ag_name,
    ar.replica_server_name,
    ars.role_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ar.replica_id = ars.replica_id;

-- Check database state in AG
SELECT db_name(database_id) AS db_name,
    synchronization_state_desc,
    is_primary_replica
FROM sys.dm_hadr_database_replica_states;

Security, Auditing, and Compliance

SQL Server security starts with authentication. Windows Authentication uses Active Directory credentials and is more secure because it does not store passwords in connection strings. SQL Server Authentication uses database-stored passwords. For production, prefer Windows Authentication or Azure AD Authentication. Always use least-privilege principles: grant only the minimum permissions needed.

Row-Level Security (RLS) filters rows based on user predicates, similar to Oracle's VPD. Create a security policy with a predicate function that checks the current user against the data. This is invaluable for multi-tenant applications where each tenant should only see their own data. RLS works regardless of the client application, making it a last line of defense.

SQL Server Audit provides comprehensive auditing for compliance with regulations like HIPAA, PCI-DSS, and SOX. Define audit specifications that capture server-level events (logins, DDL changes) or database-level events (SELECT on sensitive columns). Audit logs can be written to files, the Windows Application log, or the security log. Review audit failures regularly to detect intrusion attempts.

-- Row-Level Security
CREATE FUNCTION security.TenantPredicate(@TenantID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS access_result
WHERE @TenantID = CONVERT(INT, SESSION_CONTEXT(N'TenantID'));
GO

CREATE SECURITY POLICY TenantAccessPolicy
ADD FILTER PREDICATE security.TenantPredicate(TenantID)
ON dbo.orders
WITH (STATE = ON);

-- Server audit
CREATE SERVER AUDIT ProductionAudit
TO FILE (FILEPATH = 'C:\AuditLogs\')
WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT ProductionAudit WITH (STATE = ON);

CREATE DATABASE AUDIT SPECIFICATION SchemaChanges
FOR SERVER AUDIT ProductionAudit
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON);