Microsoft SQL Server — Complete Guide

In this tutorial, you'll learn about Microsoft SQL Server. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Microsoft SQL Server is a relational database management system (RDBMS) by Microsoft that stores and retrieves data requested by other applications using Transact-SQL (T-SQL), with built-in security, analytics, and high-availability features.

What You'll Learn

You'll understand SQL Server architecture, write T-SQL queries for CRUD operations, design normalized tables, create stored procedures and indexes, configure security and backups, and optimize query performance.

Why SQL Server Matters

SQL Server powers 40% of enterprise databases. It integrates with .NET applications via Entity Framework, supports mission-critical workloads with Always On Availability Groups, and includes built-in Machine Learning. DodaTech stores user data, scan results, and audit logs in SQL Server for Doda Browser and Durga Antivirus Pro.

Real-World Use

A security application logs 10 million threat detection events daily. SQL Server's partitioning, indexing, and columnstore indexes enable analysts to query terabytes of data in seconds for forensics and reporting.

SQL Server Learning Path

flowchart LR
  A["Database Concepts"] --> B["SQL Server Architecture"]
  B --> C["T-SQL Queries"]
  C --> D["Indexing & Performance"]
  D --> E["Stored Procedures & Security"]
  E --> F["High Availability & Backups"]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

SQL Server Editions

Code Examples

Example 1: Creating Database and Tables

-- Create the database
CREATE DATABASE DodaTechSecurity;
GO

USE DodaTechSecurity;
GO

-- Create a table with constraints
CREATE TABLE ThreatEvents (
    EventId INT IDENTITY(1,1) PRIMARY KEY,
    EventName NVARCHAR(200) NOT NULL,
    Severity TINYINT NOT NULL CHECK (Severity BETWEEN 1 AND 10),
    SourceIP VARCHAR(45) NOT NULL,
    DestinationIP VARCHAR(45),
    EventTime DATETIME2 DEFAULT GETUTCDATE(),
    IsResolved BIT DEFAULT 0,
    Notes NVARCHAR(MAX)
);

-- Create an index for performance
CREATE INDEX IX_ThreatEvents_Severity
    ON ThreatEvents (Severity DESC)
    INCLUDE (EventName, EventTime);

Example 2: CRUD Operations

-- INSERT
INSERT INTO ThreatEvents (EventName, Severity, SourceIP, DestinationIP)
VALUES
    ('Brute force attempt detected', 8, '192.168.1.100', '10.0.0.5'),
    ('Suspicious file download', 6, '10.0.0.50', '203.0.113.42'),
    ('Unauthorized admin access', 10, '45.33.32.156', '10.0.0.1');

-- SELECT with filtering and sorting
SELECT EventId, EventName, Severity, SourceIP, EventTime
FROM ThreatEvents
WHERE Severity >= 7
  AND EventTime >= DATEADD(DAY, -7, GETUTCDATE())
ORDER BY Severity DESC, EventTime DESC;

-- UPDATE
UPDATE ThreatEvents
SET IsResolved = 1, Notes = 'Investigated and closed by SOC team'
WHERE EventId = 1;

-- DELETE (clean old resolved events)
DELETE FROM ThreatEvents
WHERE IsResolved = 1
  AND EventTime < DATEADD(MONTH, -3, GETUTCDATE());

Expected output (SELECT query):

EventId  EventName                          Severity  SourceIP         EventTime
3        Unauthorized admin access          10        45.33.32.156     2026-06-20 14:30:00
1        Brute force attempt detected       8         192.168.1.100    2026-06-18 09:15:00

Example 3: Stored Procedure for Threat Summary

CREATE PROCEDURE dbo.GenerateThreatSummary
    @DaysBack INT = 7,
    @MinSeverity TINYINT = 5
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        Severity,
        COUNT(*) AS EventCount,
        COUNT(DISTINCT SourceIP) AS UniqueSources,
        MAX(EventTime) AS MostRecent
    FROM ThreatEvents
    WHERE EventTime >= DATEADD(DAY, -@DaysBack, GETUTCDATE())
      AND Severity >= @MinSeverity
    GROUP BY Severity
    ORDER BY Severity DESC;

    -- Return total high-severity count
    SELECT @@ROWCOUNT AS AffectedRows;
END;
GO

-- Execute the procedure
EXEC dbo.GenerateThreatSummary @DaysBack = 30, @MinSeverity = 8;

Example 4: Querying from C# with Parameterization

using Microsoft.Data.SqlClient;

var connectionString = "Server=.;Database=DodaTechSecurity;Trusted_Connection=true;TrustServerCertificate=true;";

using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

using var command = new SqlCommand(@"
    SELECT EventName, Severity, SourceIP, EventTime
    FROM ThreatEvents
    WHERE Severity >= @MinSeverity
      AND EventTime >= @Since
    ORDER BY Severity DESC", connection);

command.Parameters.AddWithValue("@MinSeverity", 7);
command.Parameters.AddWithValue("@Since", DateTime.UtcNow.AddDays(-7));

using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine($"{reader["Severity"]} | {reader["EventName"]} | {reader["SourceIP"]}");
}

Why parameterization matters: This prevents SQL Injection attacks. Never concatenate user input into SQL strings. Always use SqlParameter.

Indexing Strategy

Security Best Practices

Common Errors

1. Deadlock detected: Two transactions hold locks the other needs. Use SET Deadlock_PRIORITY LOW to make one transaction the victim, or ensure consistent access order.

2. Arithmetic overflow: Inserting a value too large for the column type (e.g., 999999 into TINYINT which maxes at 255). Use appropriate data types.

3. Violation of PRIMARY KEY constraint: Inserting a duplicate value into a primary key or unique column. Use IDENTITY for auto-generated keys or check before insert.

4. Could not allocate space for object: Database or log file is full. Monitor with EXEC sp_spaceused and configure AUTOGROW with reasonable increments.

5. Login failed for user: Incorrect credentials or the user doesn't have access to the database. Check the login's user mapping in SSMS.

6. Time-out expired: Query runs too long. Optimize the query (missing index?), increase CommandTimeout, or tune the workload.

7. EXECUTE permission denied: The user lacks permissions on the stored procedure. GRANT EXECUTE ON dbo.GenerateThreatSummary TO app_user;

Practice Questions

1. What is the difference between DELETE and TRUNCATE?
2. What does INDEX do and why is it important?
3. How do you prevent SQL injection in C#?
4. What is a stored procedure, and why use one?
5. What is the difference between clustered and nonclustered index?

Answers:

1. DELETE removes rows one by one (can be rolled back, fires triggers); TRUNCATE deallocates data pages (faster, no triggers, minimal logging).
2. An index speeds up data retrieval by creating a sorted structure for faster lookups, similar to a book index.
3. Use parameterized queries with SqlParameter or an ORM like Entity Framework. Never concatenate strings.
4. A stored procedure is a saved T-SQL batch with input/output parameters. Benefits: security (GRANT EXECUTE), performance (compiled plans), reusability.
5. A clustered index determines the physical order of data (one per table); a nonclustered index is a separate structure pointing to data rows (many per table).

Challenge

Design a database schema for a URL shortening service (like bit.ly). Tables: Users, Urls, ClickLogs. Create a stored procedure that inserts a click log entry and returns the original URL in one transaction. Add appropriate indexes and a query to find top 10 most-clicked URLs in the last 24 hours.

Real-World Task

Create a SQL Server Agent job that runs nightly: (1) generates a threat summary report from the ThreatEvents table, (2) exports it as a CSV file, (3) sends an email to the security team with the report attached. Use sp_send_dbmail for email.

Featured Snippet

What is Microsoft SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS) that stores and retrieves data using Transact-SQL, with built-in support for high availability, security, business intelligence, and machine learning.

FAQ

Try It Yourself

What's Next