Developer's Catalog

Database

Microsoft SQL Server

Overview

Microsoft SQL Server is an enterprise relational database management system developed and provided by Microsoft. It features high integration with Windows environments, powerful integrated development environments, and comprehensive business intelligence capabilities, widely used from small businesses to large enterprises.

Details

SQL Server was first released in 1989 and through continuous evolution has become an integrated data platform with the following characteristics:

Key Features

• Always On Availability Groups: High availability and disaster recovery
• In-Memory Technologies: In-Memory OLTP (Hekaton) and columnstore indexes
• Intelligent Query Processing: AI-driven query optimization
• Azure Integration: Hybrid cloud capabilities and Azure SQL Database
• Stretch Database: Automatic tiering of hot and cold data
• PolyBase: Integration with external data sources
• JSON Support: Native JSON support
• R and Python Integration: Advanced analytics and ML capabilities
• Graph Database: Support for graph data models

Editions

• Express: Lightweight and free development edition (10GB limit)
• Standard: Standard feature version for small to medium enterprises
• Enterprise: Full-featured version for enterprises
• Developer: Free version for developers (with Enterprise features)
• Web: For web applications
• Azure SQL Database: Fully managed cloud version

Integrated Components

• SQL Server Reporting Services (SSRS): Report creation and distribution
• SQL Server Integration Services (SSIS): ETL/data integration
• SQL Server Analysis Services (SSAS): OLAP and data mining
• SQL Server Master Data Services (MDS): Master data management

Advantages and Disadvantages

Advantages

• Windows Integration: Active Directory authentication, Windows Services integration
• Rich Development Tools: SQL Server Management Studio (SSMS), Visual Studio integration
• Comprehensive BI Features: Complete BI solution with SSRS, SSIS, SSAS
• High Availability: Always On, failover clustering
• Azure Integration: Seamless cloud migration and hybrid configuration
• Strong Security: Row-level security, dynamic data masking
• Excellent Performance: In-Memory technology and automatic tuning
• Microsoft Support: Comprehensive technical support and documentation

Disadvantages

• Windows Dependency: Linux support available but Windows-centric design
• License Cost: Expensive core-based licensing structure
• Resource Consumption: Requires large amounts of memory and CPU
• Vendor Lock-in: Dependency on Microsoft technology ecosystem
• Learning Cost: Need to learn T-SQL specific features
• Virtualization Restrictions: Some editions have virtualization constraints

Key Links

• Official Website
• SQL Server Documentation
• SQL Server Downloads
• Azure SQL Database

Code Examples

Installation & Setup

# Download and install SQL Server 2022 Express
# https://www.microsoft.com/sql-server/sql-server-downloads

# Silent installation example
.\SQLEXPR_x64_ENU.exe /ACTION=Install /FEATURES=SQL /INSTANCENAME=SQLEXPRESS /SECURITYMODE=SQL /SAPWD="StrongPassword123!" /IAcceptSQLServerLicenseTerms

# Docker Linux container
docker pull mcr.microsoft.com/mssql/server:2022-latest
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=StrongPassword123!" \
  -p 1433:1433 --name sql-server \
  mcr.microsoft.com/mssql/server:2022-latest

# Service verification
Get-Service MSSQLSERVER
net start MSSQLSERVER

# SQL Server Configuration Manager
# Services → SQL Server (MSSQLSERVER) → Properties

Basic Operations (CRUD)

-- Create database
CREATE DATABASE CompanyDB
ON (
    NAME = 'CompanyDB',
    FILENAME = 'C:\Data\CompanyDB.mdf',
    SIZE = 100MB,
    MAXSIZE = 1GB,
    FILEGROWTH = 10MB
)
LOG ON (
    NAME = 'CompanyDB_Log',
    FILENAME = 'C:\Data\CompanyDB.ldf',
    SIZE = 10MB,
    FILEGROWTH = 10%
);

USE CompanyDB;

-- Create table
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    HireDate DATETIME2 DEFAULT GETDATE(),
    Salary DECIMAL(10,2),
    DepartmentID INT,
    IsActive BIT DEFAULT 1
);

-- Insert data
INSERT INTO Employees (FirstName, LastName, Email, Salary, DepartmentID)
VALUES 
    ('John', 'Doe', '[email protected]', 75000.00, 1),
    ('Jane', 'Smith', '[email protected]', 65000.00, 2);

-- Select data
SELECT 
    EmployeeID,
    FirstName + ' ' + LastName AS FullName,
    Email,
    FORMAT(HireDate, 'yyyy/MM/dd') AS HireDateFormatted,
    FORMAT(Salary, 'C', 'en-US') AS SalaryFormatted
FROM Employees
WHERE IsActive = 1;

-- Update data
UPDATE Employees 
SET Salary = Salary * 1.1 
WHERE HireDate < DATEADD(YEAR, -1, GETDATE());

-- Delete data (logical deletion)
UPDATE Employees SET IsActive = 0 WHERE EmployeeID = 2;

Data Modeling

-- Table design with foreign key constraints
CREATE TABLE Departments (
    DepartmentID INT IDENTITY(1,1) PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL,
    ManagerID INT,
    Budget MONEY DEFAULT 0
);

ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Department
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

-- Using JSON columns
CREATE TABLE CustomerProfiles (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName NVARCHAR(100),
    ProfileData NVARCHAR(MAX) CHECK (ISJSON(ProfileData) = 1),
    Preferences NVARCHAR(MAX) CHECK (ISJSON(Preferences) = 1)
);

INSERT INTO CustomerProfiles (CustomerName, ProfileData, Preferences)
VALUES (
    'John Doe',
    N'{"age": 30, "city": "New York", "skills": ["C#", "SQL Server"]}',
    N'{"newsletter": true, "language": "en"}'
);

-- Computed columns and indexes
ALTER TABLE CustomerProfiles
ADD Age AS JSON_VALUE(ProfileData, '$.age');

CREATE INDEX IX_CustomerProfiles_Age ON CustomerProfiles(Age);

-- Temporal tables (history management)
CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100) NOT NULL,
    Price MONEY,
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);

Indexing & Optimization

-- Create indexes
CREATE NONCLUSTERED INDEX IX_Employees_Department 
ON Employees(DepartmentID) INCLUDE (FirstName, LastName);

CREATE NONCLUSTERED INDEX IX_Employees_Email 
ON Employees(Email) WHERE IsActive = 1;

-- Columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON Sales (ProductID, CustomerID, SaleDate, Amount);

-- Full-text index
CREATE FULLTEXT CATALOG EmployeeFullTextCatalog;

CREATE FULLTEXT INDEX ON Employees(FirstName, LastName)
KEY INDEX PK__Employee__7AD04F11A11A1A1A
ON EmployeeFullTextCatalog;

SELECT * FROM Employees 
WHERE CONTAINS((FirstName, LastName), '"John*"');

-- Execution plan analysis
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Show actual execution plan
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees WHERE DepartmentID = 1;
SET SHOWPLAN_ALL OFF;

-- Index usage statistics
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    s.user_seeks, s.user_scans, s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.tables t ON i.object_id = t.object_id;

Practical Examples

-- Stored procedure (T-SQL)
CREATE PROCEDURE sp_CalculateBonus
    @DepartmentID INT,
    @BonusRate DECIMAL(5,4) = 0.10
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @TotalBonus MONEY = 0;
    
    UPDATE Employees 
    SET Bonus = Salary * @BonusRate
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;
    
    SELECT @TotalBonus = SUM(Bonus)
    FROM Employees 
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;
    
    PRINT 'Total bonus allocated: ' + FORMAT(@TotalBonus, 'C', 'en-US');
    
    SELECT 
        EmployeeID, 
        FirstName + ' ' + LastName AS FullName,
        FORMAT(Salary, 'C', 'en-US') AS Salary,
        FORMAT(Bonus, 'C', 'en-US') AS Bonus
    FROM Employees 
    WHERE DepartmentID = @DepartmentID AND IsActive = 1;
END;

EXEC sp_CalculateBonus @DepartmentID = 1, @BonusRate = 0.15;

-- User-defined function
CREATE FUNCTION dbo.GetEmployeeAge(@BirthDate DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @BirthDate, GETDATE()) - 
           CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @BirthDate, GETDATE()), @BirthDate) > GETDATE()
                THEN 1 ELSE 0 END;
END;

-- Trigger
CREATE TRIGGER tr_EmployeeAudit
ON Employees
AFTER UPDATE
AS
BEGIN
    INSERT INTO EmployeeAudit (
        EmployeeID, ChangeDate, OldSalary, NewSalary, ChangedBy
    )
    SELECT 
        i.EmployeeID, 
        GETDATE(), 
        d.Salary, 
        i.Salary, 
        SUSER_SNAME()
    FROM inserted i
    INNER JOIN deleted d ON i.EmployeeID = d.EmployeeID
    WHERE i.Salary <> d.Salary;
END;

-- Common Table Expression (CTE)
WITH DepartmentStats AS (
    SELECT 
        d.DepartmentName,
        COUNT(e.EmployeeID) AS EmployeeCount,
        AVG(e.Salary) AS AvgSalary,
        MAX(e.Salary) AS MaxSalary
    FROM Departments d
    LEFT JOIN Employees e ON d.DepartmentID = e.DepartmentID
    WHERE e.IsActive = 1
    GROUP BY d.DepartmentID, d.DepartmentName
)
SELECT * FROM DepartmentStats
WHERE EmployeeCount > 0
ORDER BY AvgSalary DESC;

Best Practices

-- Security settings
-- Row-level security
CREATE FUNCTION dbo.fn_securitypredicate(@DepartmentID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @DepartmentID = CAST(SESSION_CONTEXT(N'DepartmentID') AS INT);

CREATE SECURITY POLICY DepartmentSecurityPolicy
ADD FILTER PREDICATE dbo.fn_securitypredicate(DepartmentID) ON dbo.Employees
WITH (STATE = ON);

-- Dynamic data masking
ALTER TABLE Employees
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

-- Transparent Data Encryption (TDE)
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

ALTER DATABASE CompanyDB SET ENCRYPTION ON;

-- Backup and restore
-- Full backup
BACKUP DATABASE CompanyDB 
TO DISK = 'C:\Backups\CompanyDB_Full.bak'
WITH FORMAT, 
     NAME = 'CompanyDB Full Backup',
     DESCRIPTION = 'Full backup of CompanyDB';

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

-- Restore
RESTORE DATABASE CompanyDB_Test
FROM DISK = 'C:\Backups\CompanyDB_Full.bak'
WITH MOVE 'CompanyDB' TO 'C:\Data\CompanyDB_Test.mdf',
     MOVE 'CompanyDB_Log' TO 'C:\Data\CompanyDB_Test.ldf',
     REPLACE;

-- Always On Availability Groups
-- (Requires Windows Server Failover Clustering setup)
CREATE AVAILABILITY GROUP AG_CompanyDB
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE CompanyDB
REPLICA ON 
N'SQL-NODE1' WITH (ENDPOINT_URL = N'TCP://SQL-NODE1:5022',
                   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                   FAILOVER_MODE = AUTOMATIC),
N'SQL-NODE2' WITH (ENDPOINT_URL = N'TCP://SQL-NODE2:5022',
                   AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
                   FAILOVER_MODE = AUTOMATIC);

-- Monitoring and maintenance
-- Index fragmentation check
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30;

-- Update statistics
UPDATE STATISTICS Employees WITH FULLSCAN;