SQL Server Basics

Learn the fundamentals of Microsoft SQL Server for enterprise database management

1. Introduction to SQL Server

What is SQL Server?

SQL Server is Microsoft's enterprise relational database management system. It uses T-SQL (Transact-SQL), which extends standard SQL with additional features.

SQL Server Architecture

SQL Server consists of:

  • Database Engine - Core service for storing and processing data
  • SQL OS - Underlying operating system layer
  • Relational Engine - Processes queries and commands

Types of SQL Commands in SQL Server:

  • DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
  • DML (Data Manipulation Language): INSERT, UPDATE, DELETE, MERGE
  • DQL (Data Query Language): SELECT
  • DCL (Data Control Language): GRANT, REVOKE
  • TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVE TRANSACTION

2. Setting Up SQL Server Environment

SQL Server Installation

Download SQL Server Developer (free) or Express Edition. Use SQL Server Installation Center for setup.

Azure Data Studio

Use Azure Data Studio, a cross-platform tool for managing SQL Server with modern interface.

SQL Server Management Studio

Use SSMS (SQL Server Management Studio), the comprehensive management tool for SQL Server.

3. SQL Server Database Objects

Create Tables in SQL Server:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name NVARCHAR(50) NOT NULL,
    last_name NVARCHAR(50) NOT NULL,
    hire_date DATE DEFAULT GETDATE(),
    salary DECIMAL(10,2),
    dept_id INT,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id)
);

-- SQL Server table with identity column
CREATE TABLE sales (
    sale_id INT IDENTITY(1,1) PRIMARY KEY,
    sale_date DATETIME2,
    amount DECIMAL(10,2)
);

SQL Server Data Types

  • INT, BIGINT, DECIMAL - Numeric types
  • VARCHAR(n), NVARCHAR(n) - Variable-length strings (Unicode)
  • DATE, DATETIME2 - Date and time values
  • DATETIMEOFFSET - Timezone-aware datetime
  • UNIQUEIDENTIFIER - GUID values
  • VARBINARY - Binary data

SQL Server Constraints

  • CHECK - Custom validation
  • ON DELETE CASCADE - Cascade deletes
  • IDENTITY - Auto-incrementing columns

4. Basic SQL Server Queries

SELECT Statement

-- Select with TOP clause
SELECT TOP 10 * 
FROM employees
ORDER BY hire_date DESC;

-- SQL Server system functions
SELECT GETDATE() AS current_date, 
       SYSTEM_USER AS current_user;

WHERE Clause

SELECT * FROM employees 
WHERE hire_date > CONVERT(DATE, '2020-01-01');

SQL Server-Specific Operators

Comparison:

=, >, <, !=, <>

Logical:

AND, OR, NOT

SQL Server-Specific:

TOP, OFFSET-FETCH, APPLY

SQL Server Pagination

-- Modern SQL Server syntax (2012+)
SELECT * FROM employees
ORDER BY hire_date
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

-- Traditional SQL Server syntax
SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY hire_date) AS row_num, *
    FROM employees
) AS numbered
WHERE row_num BETWEEN 11 AND 20;

5. Modifying Data in SQL Server

INSERT

-- Single record with identity
INSERT INTO employees 
VALUES ('John', 'Doe', GETDATE(), 50000, 10);

-- Insert from another table
INSERT INTO managers
SELECT * FROM employees 
WHERE job_id = 'MANAGER';

-- Output inserted values
INSERT INTO employees (first_name, last_name)
OUTPUT INSERTED.emp_id
VALUES ('Jane', 'Smith');

UPDATE

-- Update with JOIN
UPDATE e
SET e.salary = d.avg_salary
FROM employees e
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary 
    FROM employees 
    GROUP BY dept_id
) d ON e.dept_id = d.dept_id
WHERE e.dept_id = 10;

-- Update with OUTPUT
UPDATE employees
SET salary = salary * 1.1
OUTPUT INSERTED.salary
WHERE emp_id = 100;

DELETE & MERGE

-- Delete with OUTPUT
DELETE FROM employees
OUTPUT DELETED.emp_id
WHERE dept_id = 20;

-- SQL Server's MERGE statement
MERGE INTO employees_target AS t
USING employees_source AS s
ON (t.emp_id = s.emp_id)
WHEN MATCHED THEN UPDATE SET t.salary = s.salary
WHEN NOT MATCHED THEN INSERT (emp_id, name, salary)
VALUES (s.emp_id, s.name, s.salary);

6. Advanced SQL Server Filtering

SQL Server-Specific Functions

-- IIF (SQL Server's IF-THEN-ELSE)
SELECT emp_name, 
       IIF(dept_id = 10, 'Accounting', 
           IIF(dept_id = 20, 'Research', 'Other')) AS department
FROM employees;

-- CASE expression (ANSI standard)
SELECT emp_name,
       CASE WHEN salary > 100000 THEN 'High'
            WHEN salary > 50000 THEN 'Medium'
            ELSE 'Low' END AS salary_level
FROM employees;

Common Table Expressions

-- Recursive CTE for hierarchies
WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member
    SELECT e.emp_id, e.emp_name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.emp_id
)
SELECT * FROM EmployeeHierarchy;

Window Functions

-- RANK, DENSE_RANK, ROW_NUMBER
SELECT emp_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- Window functions with partitions
SELECT emp_name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS avg_dept_salary,
       salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg
FROM employees;

7. SQL Server Joins

SQL Server Join Syntax

-- ANSI JOIN syntax
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- CROSS APPLY (similar to lateral joins)
SELECT d.dept_name, e.emp_name
FROM departments d
CROSS APPLY (
    SELECT TOP 3 emp_name 
    FROM employees 
    WHERE dept_id = d.dept_id
    ORDER BY salary DESC
) e;
-- OUTER APPLY
SELECT d.dept_name, e.emp_name
FROM departments d
OUTER APPLY (
    SELECT TOP 1 emp_name 
    FROM employees 
    WHERE dept_id = d.dept_id
    ORDER BY hire_date
) e;

-- Temporal table query
SELECT * FROM employees
FOR SYSTEM_TIME AS OF '2023-01-01T12:00:00';

Set Operations

-- SQL Server set operations
SELECT emp_name FROM current_employees
UNION ALL
SELECT emp_name FROM former_employees
EXCEPT
SELECT emp_name FROM consultants;

8. SQL Server Aggregation

Aggregate Functions

-- SQL Server statistical functions
SELECT 
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
    STDEV(salary) AS salary_stddev,
    VAR(salary) AS salary_variance,
    STRING_AGG(emp_name, ', ') WITHIN GROUP (ORDER BY salary) AS employees
FROM employees
GROUP BY dept_id;

SQL Server GROUPING SETS

-- Multiple grouping levels
SELECT 
    dept_id, 
    job_id, 
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
    (dept_id, job_id),
    (dept_id),
    (job_id),
    ()
);

-- CUBE and ROLLUP
SELECT dept_id, job_id, COUNT(*)
FROM employees
GROUP BY CUBE(dept_id, job_id);

9. SQL Server Subqueries

SQL Server WITH Clause

-- Common Table Expression (CTE)
WITH dept_stats AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
)
SELECT e.emp_name, e.salary, d.avg_salary
FROM employees e
JOIN dept_stats d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

Inline Views

SELECT d.dept_name, e.emp_count
FROM departments d
JOIN (
    SELECT dept_id, COUNT(*) AS emp_count
    FROM employees
    GROUP BY dept_id
) e ON d.dept_id = e.dept_id;

SQL Server APPLY Operator

-- CROSS APPLY with subquery
SELECT d.dept_name, top_emp.emp_name, top_emp.salary
FROM departments d
CROSS APPLY (
    SELECT TOP 3 emp_name, salary
    FROM employees
    WHERE dept_id = d.dept_id
    ORDER BY salary DESC
) AS top_emp;

Temporal Table Queries

-- Query all versions between dates
SELECT *
FROM employees
FOR SYSTEM_TIME BETWEEN '2023-01-01' AND '2023-12-31'
WHERE emp_id = 100;

10. SQL Server Views & Indexes

SQL Server Views

-- Create a view with check option
CREATE OR ALTER VIEW active_employees AS
SELECT * FROM employees
WHERE status = 'ACTIVE'
WITH CHECK OPTION;

-- Indexed view
CREATE VIEW emp_dept_summary WITH SCHEMABINDING AS
SELECT d.dept_name, COUNT_BIG(*) AS emp_count, AVG(e.salary) AS avg_salary
FROM dbo.employees e JOIN dbo.departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

CREATE UNIQUE CLUSTERED INDEX idx_emp_dept_summary ON emp_dept_summary(dept_name);

SQL Server Indexes

-- Create various index types
CREATE INDEX idx_emp_name ON employees(last_name, first_name);
CREATE CLUSTERED INDEX idx_emp_id ON employees(emp_id);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);

-- Filtered index
CREATE INDEX idx_active_emp ON employees(dept_id)
WHERE status = 'ACTIVE';

-- Included columns
CREATE INDEX idx_emp_dept_inc ON employees(dept_id)
INCLUDE (salary, hire_date);

11. SQL Server Functions

String Functions

SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name,
    CHARINDEX('a', 'SQL Server') AS position,
    SUBSTRING('SQL Server', 5, 6) AS substring,
    FORMAT(salary, 'C') AS formatted_salary,
    STRING_AGG(emp_name, ', ') WITHIN GROUP (ORDER BY salary) AS employees
FROM employees;

Date Functions

SELECT 
    GETDATE() AS current_date,
    SYSDATETIME() AS current_timestamp,
    EOMONTH(hire_date) AS month_end,
    DATEADD(MONTH, 6, hire_date) AS review_date,
    DATEDIFF(MONTH, hire_date, GETDATE()) AS months_employed,
    FORMAT(hire_date, 'yyyy-MM-dd HH:mm:ss') AS formatted_date,
    TRY_CONVERT(DATE, '2023-12-31') AS converted_date
FROM employees;

Numeric Functions

SELECT 
    ROUND(123.4567, 2) AS rounded,
    FLOOR(123.4567) AS floored,
    CEILING(123.4567) AS ceiling,
    ABS(-10) AS absolute,
    POWER(2, 8) AS power,
    SQRT(64) AS square_root,
    RAND() AS random_number
FROM employees;

12. SQL Server Transactions

SQL Server Transactions

-- Explicit transaction
BEGIN TRANSACTION;
BEGIN TRY
    UPDATE accounts SET balance = balance - 100 
    WHERE account_id = 123;
    
    UPDATE accounts SET balance = balance + 100 
    WHERE account_id = 456;
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    THROW;
END CATCH

-- Savepoints
BEGIN TRANSACTION;
SAVE TRANSACTION before_update;

UPDATE employees SET salary = salary * 1.1;

-- Rollback to savepoint
ROLLBACK TRANSACTION before_update;

COMMIT TRANSACTION;

SQL Server Isolation Levels

READ COMMITTED - Default isolation level

READ UNCOMMITTED - Dirty reads allowed

REPEATABLE READ - Prevents non-repeatable reads

SERIALIZABLE - Highest isolation, prevents phantoms

SNAPSHOT - Version-based isolation

Temporal Tables

-- Query historical data
SELECT * FROM employees
FOR SYSTEM_TIME AS OF '2023-01-01T12:00:00';

-- Restore from history
INSERT INTO employees
SELECT * FROM employees
FOR SYSTEM_TIME AS OF '2023-01-01T12:00:00'
WHERE emp_id = 100;

13. T-SQL Basics

T-SQL Batches

-- T-SQL batch with variables
DECLARE @emp_name NVARCHAR(100);
DECLARE @salary DECIMAL(10,2);

SELECT @emp_name = emp_name, @salary = salary
FROM employees
WHERE emp_id = 100;

PRINT @emp_name + ' earns ' + CAST(@salary AS NVARCHAR(20));
    
-- Error handling
BEGIN TRY
    -- Code that might fail
    SELECT 1/0;
END TRY
BEGIN CATCH
    PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH

Stored Procedures & Functions

-- Create a stored procedure
CREATE OR ALTER PROCEDURE adjust_salary
    @emp_id INT,
    @percent DECIMAL(5,2)
AS
BEGIN
    DECLARE @new_salary DECIMAL(10,2);
    
    UPDATE employees
    SET salary = salary * (1 + @percent/100)
    WHERE emp_id = @emp_id;
    
    SELECT @new_salary = salary FROM employees WHERE emp_id = @emp_id;
    
    PRINT 'New salary: ' + CAST(@new_salary AS NVARCHAR(20));
END;

-- Create a function
CREATE OR ALTER FUNCTION get_dept_avg(@dept_id INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @avg_salary DECIMAL(10,2);
    
    SELECT @avg_salary = AVG(salary)
    FROM employees
    WHERE dept_id = @dept_id;
    
    RETURN @avg_salary;
END;