Microsoft SQL Server

Introduction

Microsoft SQL Server is a relational database management system developed by Microsoft. It's particularly well-integrated with the Microsoft ecosystem and offers robust enterprise features.

Editions:

  • Enterprise
  • Standard
  • Express
  • Developer
  • Web

Key Features

Always On Availability Groups

High availability and disaster recovery solution that provides enterprise-level data protection.

In-Memory OLTP

-- Create memory-optimized table
CREATE TABLE dbo.InMemTable
(
    Id INT IDENTITY PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(100)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Columnstore Indexes

-- Create columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales
ON Sales.OrderDetails;

T-SQL Programming

Stored Procedures

CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentId INT
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        e.EmployeeId,
        e.FirstName,
        e.LastName,
        d.DepartmentName
    FROM 
        Employees e
        INNER JOIN Departments d ON e.DepartmentId = d.DepartmentId
    WHERE 
        e.DepartmentId = @DepartmentId;
END;

Functions

CREATE FUNCTION CalculateAge
(
    @BirthDate DATE
)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE()) -
        CASE
            WHEN (MONTH(@BirthDate) > MONTH(GETDATE())) OR
                 (MONTH(@BirthDate) = MONTH(GETDATE()) AND 
                  DAY(@BirthDate) > DAY(GETDATE()))
            THEN 1
            ELSE 0
        END;
END;

Administration

Backup and Recovery

-- Full backup
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.bak'
WITH FORMAT, INIT, NAME = 'AdventureWorks-Full';

-- Transaction log backup
BACKUP LOG AdventureWorks
TO DISK = 'C:\Backups\AdventureWorks.trn';

Maintenance Plans

Common Tasks:

  • Index reorganization
  • Statistics updates
  • Integrity checks
  • Backup operations
  • Log file maintenance

Security

Authentication Modes

  • Windows Authentication
  • SQL Server Authentication
  • Mixed Mode

Permissions Management

-- Create login
CREATE LOGIN ApplicationUser 
WITH PASSWORD = 'StrongP@ssw0rd';

-- Create database user
CREATE USER AppUser FOR LOGIN ApplicationUser;

-- Grant permissions
GRANT SELECT, INSERT, UPDATE ON Schema.TableName
TO AppUser;

-- Create role
CREATE ROLE DataAnalyst;
GRANT SELECT ON Schema.ReportView TO DataAnalyst;
ALTER ROLE DataAnalyst ADD MEMBER AppUser;

Performance Tuning

Query Optimization

-- Analyze query execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Create efficient index
CREATE NONCLUSTERED INDEX IX_OrderDate
ON Sales.Orders(OrderDate)
INCLUDE (CustomerId, TotalAmount);

Monitoring Tools

  • SQL Server Profiler
  • Dynamic Management Views (DMVs)
  • Extended Events
  • Performance Monitor

Tools and Resources

Management Tools

  • SQL Server Management Studio (SSMS)
  • Azure Data Studio
  • SQL Server Configuration Manager
  • Database Engine Tuning Advisor

Learning Resources:

  • Microsoft Documentation
  • SQL Server Books Online
  • Microsoft Learn Platform
  • Community Forums