SQL Server.
Interview cheat sheet.

Microsoft SQL Server is a relational database management system (RDBMS) designed for enterprise environments with comprehensive features including advanced T-SQL support, built-in business intelligence, high availability solutions, and robust security. Key features include stored procedures, triggers, views, CTEs, window functions, temporal tables, JSON support, partitioning, and Always On availability groups. Essential for enterprise applications requiring scalability, performance, security, and integration with Microsoft ecosystem technologies.

DDL (Data Definition Language)

-- CREATE
CREATE TABLE Employee (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2)
);

-- ALTER
ALTER TABLE Employee ADD Email VARCHAR(100);
ALTER TABLE Employee DROP COLUMN Email;
ALTER TABLE Employee ALTER COLUMN Name VARCHAR(100);

-- DROP
DROP TABLE Employee;

-- TRUNCATE (removes all rows, keeps structure)
TRUNCATE TABLE Employee;

DML (Data Manipulation Language)

-- INSERT
INSERT INTO Employee VALUES (1, 'John', 50000);
INSERT INTO Employee (ID, Name) VALUES (2, 'Jane');

-- UPDATE
UPDATE Employee SET Salary = 55000 WHERE ID = 1;

-- DELETE
DELETE FROM Employee WHERE ID = 1;

-- SELECT
SELECT * FROM Employee;
SELECT Name, Salary FROM Employee WHERE Salary > 50000;

DCL (Data Control Language)

-- GRANT
GRANT SELECT, INSERT ON Employee TO User1;

-- REVOKE
REVOKE INSERT ON Employee FROM User1;

TCL (Transaction Control Language)

BEGIN TRANSACTION;
-- SQL statements
COMMIT; -- or ROLLBACK;

Numeric Types

• INT: -2^31 to 2^31-1
• BIGINT: -2^63 to 2^63-1
• DECIMAL(p,s): Fixed precision
• FLOAT: Approximate numeric
• BIT: 0, 1, or NULL

String Types

• CHAR(n): Fixed length
• VARCHAR(n): Variable length
• VARCHAR(MAX): Up to 2GB
• NVARCHAR(n): Unicode variable length
• TEXT: Deprecated, use VARCHAR(MAX)

Date/Time Types

• DATE: Date only (YYYY-MM-DD)
• TIME: Time only
• DATETIME: Date and time (3.33ms accuracy)
• DATETIME2: More precise (100ns accuracy)
• DATETIMEOFFSET: With timezone

Other Types

• BINARY(n): Fixed binary
• VARBINARY(n): Variable binary
• UNIQUEIDENTIFIER: GUID
• XML: XML data
• JSON: Stored as NVARCHAR

-- PRIMARY KEY
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    -- or
    OrderID INT,
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID)
);

-- FOREIGN KEY
CREATE TABLE OrderDetails (
    DetailID INT PRIMARY KEY,
    OrderID INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

-- UNIQUE
Email VARCHAR(100) UNIQUE

-- CHECK
Age INT CHECK (Age >= 18)

-- DEFAULT
CreatedDate DATETIME DEFAULT GETDATE()

-- NOT NULL
Name VARCHAR(50) NOT NULL

-- INNER JOIN (matching records)
SELECT e.Name, d.DeptName
FROM Employee e
INNER JOIN Department d ON e.DeptID = d.DeptID;

-- LEFT JOIN (all from left table)
SELECT e.Name, d.DeptName
FROM Employee e
LEFT JOIN Department d ON e.DeptID = d.DeptID;

-- RIGHT JOIN (all from right table)
SELECT e.Name, d.DeptName
FROM Employee e
RIGHT JOIN Department d ON e.DeptID = d.DeptID;

-- FULL OUTER JOIN (all from both)
SELECT e.Name, d.DeptName
FROM Employee e
FULL OUTER JOIN Department d ON e.DeptID = d.DeptID;

-- CROSS JOIN (Cartesian product)
SELECT e.Name, d.DeptName
FROM Employee e
CROSS JOIN Department d;

-- SELF JOIN
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employee e1
LEFT JOIN Employee e2 ON e1.ManagerID = e2.EmployeeID;

-- Clustered Index (1 per table, defines physical order)
CREATE CLUSTERED INDEX IX_Employee_ID ON Employee(ID);

-- Non-Clustered Index (multiple allowed)
CREATE NONCLUSTERED INDEX IX_Employee_Name ON Employee(Name);

-- Composite Index
CREATE INDEX IX_Employee_Name_Salary ON Employee(Name, Salary);

-- Unique Index
CREATE UNIQUE INDEX IX_Employee_Email ON Employee(Email);

-- Filtered Index
CREATE INDEX IX_ActiveEmployees ON Employee(Name)
WHERE IsActive = 1;

-- Covering Index (INCLUDE)
CREATE INDEX IX_Employee_Dept 
ON Employee(DeptID) 
INCLUDE (Name, Salary);

-- Drop Index
DROP INDEX IX_Employee_Name ON Employee;

Index Guidelines

• Primary key automatically creates clustered index
• Foreign keys should be indexed
• Columns in WHERE, JOIN, ORDER BY benefit from indexes
• Too many indexes slow down DML operations

Stored Procedures

-- Create Procedure
CREATE PROCEDURE GetEmployeeByDept
    @DeptID INT,
    @Count INT OUTPUT
AS
BEGIN
    SELECT * FROM Employee WHERE DepartmentID = @DeptID;
    SET @Count = @@ROWCOUNT;
END;

-- Execute Procedure
DECLARE @EmpCount INT;
EXEC GetEmployeeByDept @DeptID = 1, @Count = @EmpCount OUTPUT;
SELECT @EmpCount;

User-Defined Functions

Scalar Function

CREATE FUNCTION CalculateBonus(@Salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN @Salary * 0.1;
END;

-- Usage
SELECT Name, dbo.CalculateBonus(Salary) AS Bonus FROM Employee;

Table-Valued Function

-- Inline TVF
CREATE FUNCTION GetEmployeesByDept(@DeptID INT)
RETURNS TABLE
AS
RETURN (
    SELECT * FROM Employee WHERE DepartmentID = @DeptID
);

-- Multi-statement TVF
CREATE FUNCTION GetEmployeeSummary()
RETURNS @Summary TABLE (DeptID INT, TotalSalary DECIMAL(10,2))
AS
BEGIN
    INSERT INTO @Summary
    SELECT DepartmentID, SUM(Salary)
    FROM Employee
    GROUP BY DepartmentID;
    RETURN;
END;

-- Create View
CREATE VIEW vw_EmployeeDetails
AS
SELECT e.Name, e.Salary, d.DeptName
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID;

-- Indexed View (Materialized)
CREATE VIEW vw_DeptSalary WITH SCHEMABINDING
AS
SELECT d.DeptID, d.DeptName, SUM(e.Salary) AS TotalSalary, COUNT_BIG(*) AS EmpCount
FROM dbo.Department d
JOIN dbo.Employee e ON d.DeptID = e.DeptID
GROUP BY d.DeptID, d.DeptName;

-- Create unique clustered index on view
CREATE UNIQUE CLUSTERED INDEX IX_vw_DeptSalary ON vw_DeptSalary(DeptID);

Common Table Expressions (CTEs)

-- Simple CTE
WITH EmployeeCTE AS (
    SELECT Name, Salary, DeptID
    FROM Employee
    WHERE Salary > 50000
)
SELECT * FROM EmployeeCTE;

-- Recursive CTE
WITH OrgChart AS (
    -- Anchor member
    SELECT EmployeeID, Name, ManagerID, 0 AS Level
    FROM Employee
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive member
    SELECT e.EmployeeID, e.Name, e.ManagerID, Level + 1
    FROM Employee e
    INNER JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgChart;

Window Functions

-- ROW_NUMBER()
SELECT Name, Salary,
    ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employee;

-- RANK() and DENSE_RANK()
SELECT Name, Salary,
    RANK() OVER (ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employee;

-- Partitioning
SELECT Name, DeptID, Salary,
    ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS DeptRank
FROM Employee;

-- Aggregate Window Functions
SELECT Name, Salary,
    SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal,
    AVG(Salary) OVER (PARTITION BY DeptID) AS DeptAvgSalary
FROM Employee;

-- LEAD/LAG
SELECT Name, Salary,
    LAG(Salary, 1) OVER (ORDER BY EmployeeID) AS PrevSalary,
    LEAD(Salary, 1) OVER (ORDER BY EmployeeID) AS NextSalary
FROM Employee;

Transaction Properties (ACID)

• Atomicity: All or nothing
• Consistency: Valid state to valid state
• Isolation: Concurrent execution as if serial
• Durability: Committed changes persist

Transaction Control

BEGIN TRANSACTION;
    UPDATE Account SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Account SET Balance = Balance + 100 WHERE AccountID = 2;
    
    IF @@ERROR = 0
        COMMIT TRANSACTION;
    ELSE
        ROLLBACK TRANSACTION;

Isolation Levels

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* Levels (least to most restrictive):
1. READ UNCOMMITTED - Dirty reads allowed
2. READ COMMITTED - Default, no dirty reads
3. REPEATABLE READ - No phantom reads for existing rows
4. SERIALIZABLE - No phantom reads at all
5. SNAPSHOT - Row versioning */

Locking

-- Table hints
SELECT * FROM Employee WITH (NOLOCK); -- Read uncommitted
SELECT * FROM Employee WITH (READLOCK); -- Shared lock
SELECT * FROM Employee WITH (UPDLOCK); -- Update lock
SELECT * FROM Employee WITH (TABLOCKX); -- Exclusive table lock

Deadlock Prevention

• Access objects in same order
• Keep transactions short
• Use appropriate isolation level
• Consider SNAPSHOT isolation

Query Optimization Tips

1. Use appropriate indexes
2. **Avoid SELECT ***
3. Use EXISTS instead of IN for large datasets
4. Avoid functions in WHERE clause
5. Use appropriate data types
6. Update statistics regularly

Execution Plan Analysis

-- Enable execution plan
SET SHOWPLAN_ALL ON;
-- or use SSMS GUI

-- Check for:
-- Table scans (consider index)
-- Key lookups (consider covering index)
-- Sort operations (consider index)
-- High cost operations

Query Hints

-- Force index
SELECT * FROM Employee WITH (INDEX(IX_Employee_Name))
WHERE Name = 'John';

-- Join hints
SELECT * FROM Employee e
INNER HASH JOIN Department d ON e.DeptID = d.DeptID;

-- OPTION clause
SELECT * FROM Employee
OPTION (MAXDOP 1, RECOMPILE);

Statistics

-- Update statistics
UPDATE STATISTICS Employee;

-- Create statistics
CREATE STATISTICS stat_Employee_Salary ON Employee(Salary);

-- View statistics
DBCC SHOW_STATISTICS('Employee', 'IX_Employee_Name');

Authentication & Authorization

-- Create login (server level)
CREATE LOGIN AppUser WITH PASSWORD = 'StrongPassword123!';

-- Create user (database level)
CREATE USER AppUser FOR LOGIN AppUser;

-- Grant permissions
GRANT SELECT, INSERT, UPDATE ON Employee TO AppUser;
GRANT EXECUTE ON GetEmployeeByDept TO AppUser;

-- Deny permissions
DENY DELETE ON Employee TO AppUser;

-- Database roles
ALTER ROLE db_datareader ADD MEMBER AppUser;
ALTER ROLE db_datawriter ADD MEMBER AppUser;

Row-Level Security

-- Create security policy
CREATE FUNCTION dbo.fn_SecurityPredicate(@DeptID INT)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS Result
WHERE @DeptID = USER_NAME() OR USER_NAME() = 'Admin';

CREATE SECURITY POLICY DeptFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(DeptID) ON dbo.Employee
WITH (STATE = ON);

Dynamic Data Masking

ALTER TABLE Employee
ALTER COLUMN SSN VARCHAR(11) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');

ALTER TABLE Employee
ALTER COLUMN Email VARCHAR(100) MASKED WITH (FUNCTION = 'email()');

Backup Types

-- Full backup
BACKUP DATABASE MyDB TO DISK = 'C:\Backup\MyDB_Full.bak';

-- Differential backup
BACKUP DATABASE MyDB TO DISK = 'C:\Backup\MyDB_Diff.bak'
WITH DIFFERENTIAL;

-- Transaction log backup
BACKUP LOG MyDB TO DISK = 'C:\Backup\MyDB_Log.trn';

Recovery Models

• Simple: No log backups, minimal log space
• Full: Log backups required, point-in-time recovery
• Bulk-logged: Minimal logging for bulk operations

Restore Operations

-- Restore full backup
RESTORE DATABASE MyDB FROM DISK = 'C:\Backup\MyDB_Full.bak'
WITH REPLACE, NORECOVERY;

-- Restore differential
RESTORE DATABASE MyDB FROM DISK = 'C:\Backup\MyDB_Diff.bak'
WITH NORECOVERY;

-- Restore log
RESTORE LOG MyDB FROM DISK = 'C:\Backup\MyDB_Log.trn'
WITH RECOVERY;

Temporal Tables

-- Create temporal table
CREATE TABLE Employee
(
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

-- Query historical data
SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2024-01-01';

JSON Support

-- Store JSON
CREATE TABLE Products (
    ID INT PRIMARY KEY,
    Details NVARCHAR(MAX) CHECK (ISJSON(Details) = 1)
);

-- Query JSON
SELECT 
    JSON_VALUE(Details, '$.name') AS ProductName,
    JSON_VALUE(Details, '$.price') AS Price
FROM Products;

-- Modify JSON
UPDATE Products
SET Details = JSON_MODIFY(Details, '$.price', 29.99)
WHERE ID = 1;

MERGE Statement

MERGE Employee AS target
USING EmployeeUpdates AS source ON target.EmployeeID = source.EmployeeID
WHEN MATCHED THEN
    UPDATE SET target.Salary = source.Salary
WHEN NOT MATCHED BY TARGET THEN
    INSERT (EmployeeID, Name, Salary) VALUES (source.EmployeeID, source.Name, source.Salary)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

PIVOT/UNPIVOT

-- PIVOT
SELECT DeptName, [2022], [2023], [2024]
FROM (
    SELECT DeptName, Year, Revenue
    FROM DeptRevenue
) AS SourceTable
PIVOT (
    SUM(Revenue) FOR Year IN ([2022], [2023], [2024])
) AS PivotTable;

-- UNPIVOT
SELECT DeptName, Year, Revenue
FROM DeptRevenueByYear
UNPIVOT (
    Revenue FOR Year IN ([2022], [2023], [2024])
) AS UnpivotTable;

Partitioning

-- Create partition function
CREATE PARTITION FUNCTION pf_OrderDate (DATETIME)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01');

-- Create partition scheme
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO (FG1, FG2, FG3);

-- Create partitioned table
CREATE TABLE Orders (
    OrderID INT,
    OrderDate DATETIME,
    Amount DECIMAL(10,2)
) ON ps_OrderDate(OrderDate);

Enterprise Data Patterns

1. Data Warehouse ETL Pattern

-- Staging layer for raw data
CREATE TABLE Staging_SalesData (
    TransactionID NVARCHAR(50),
    CustomerID NVARCHAR(50),
    ProductID NVARCHAR(50),
    SaleDate DATE,
    Amount DECIMAL(10,2),
    LoadDate DATETIME2 DEFAULT SYSDATETIME()
);

-- Fact table with proper dimensions
CREATE TABLE Fact_Sales (
    SalesKey BIGINT IDENTITY(1,1) PRIMARY KEY,
    CustomerKey INT FOREIGN KEY REFERENCES Dim_Customer(CustomerKey),
    ProductKey INT FOREIGN KEY REFERENCES Dim_Product(ProductKey),
    DateKey INT FOREIGN KEY REFERENCES Dim_Date(DateKey),
    SalesAmount DECIMAL(10,2),
    Quantity INT,
    CreatedDate DATETIME2 DEFAULT SYSDATETIME()
);

-- ETL Process with error handling
CREATE PROCEDURE LoadFactSales
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Transform and load
        INSERT INTO Fact_Sales (CustomerKey, ProductKey, DateKey, SalesAmount, Quantity)
        SELECT 
            dc.CustomerKey,
            dp.ProductKey,
            dd.DateKey,
            s.Amount,
            1
        FROM Staging_SalesData s
        INNER JOIN Dim_Customer dc ON s.CustomerID = dc.CustomerID
        INNER JOIN Dim_Product dp ON s.ProductID = dp.ProductID
        INNER JOIN Dim_Date dd ON s.SaleDate = dd.Date
        WHERE NOT EXISTS (
            SELECT 1 FROM Fact_Sales fs 
            WHERE fs.CustomerKey = dc.CustomerKey 
            AND fs.ProductKey = dp.ProductKey 
            AND fs.DateKey = dd.DateKey
        );
        
        -- Archive processed data
        INSERT INTO Archive_SalesData
        SELECT *, SYSDATETIME() FROM Staging_SalesData;
        
        TRUNCATE TABLE Staging_SalesData;
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;
        
        -- Log error
        INSERT INTO ETL_ErrorLog (ProcedureName, ErrorMessage, ErrorDate)
        VALUES ('LoadFactSales', ERROR_MESSAGE(), SYSDATETIME());
        
        THROW;
    END CATCH;
END;

2. Change Data Capture (CDC) Pattern

-- Enable CDC on database
EXEC sys.sp_cdc_enable_db;

-- Enable CDC on table
EXEC sys.sp_cdc_enable_table 
    @source_schema = 'dbo',
    @source_name = 'Employee',
    @role_name = NULL;

-- Query CDC changes
SELECT 
    __$operation,
    __$start_lsn,
    EmployeeID,
    Name,
    Salary,
    CASE __$operation
        WHEN 1 THEN 'DELETE'
        WHEN 2 THEN 'INSERT'
        WHEN 3 THEN 'UPDATE_OLD'
        WHEN 4 THEN 'UPDATE_NEW'
    END AS Operation
FROM cdc.dbo_Employee_CT
WHERE __$start_lsn > sys.fn_cdc_get_min_lsn('dbo_Employee')
ORDER BY __$start_lsn;

3. Advanced Partitioning Strategy

-- Create partition function and scheme
CREATE PARTITION FUNCTION pf_SalesByYear (DATE)
AS RANGE RIGHT FOR VALUES 
('2022-01-01', '2023-01-01', '2024-01-01', '2025-01-01');

CREATE PARTITION SCHEME ps_SalesByYear
AS PARTITION pf_SalesByYear
TO (FG_2021, FG_2022, FG_2023, FG_2024, FG_2025);

-- Partitioned table with aligned indexes
CREATE TABLE Sales (
    SalesID BIGINT IDENTITY(1,1),
    SaleDate DATE NOT NULL,
    CustomerID INT,
    Amount DECIMAL(10,2),
    CONSTRAINT PK_Sales PRIMARY KEY (SalesID, SaleDate)
) ON ps_SalesByYear(SaleDate);

-- Partition elimination query
SELECT SUM(Amount) 
FROM Sales 
WHERE SaleDate >= '2024-01-01' AND SaleDate < '2025-01-01';

-- Switch partitions for maintenance
CREATE TABLE Sales_Staging (
    SalesID BIGINT,
    SaleDate DATE,
    CustomerID INT,
    Amount DECIMAL(10,2),
    CONSTRAINT CK_Sales_Staging CHECK (SaleDate >= '2025-01-01' AND SaleDate < '2026-01-01')
);

ALTER TABLE Sales_Staging 
SWITCH TO Sales PARTITION 5;

High Availability & Disaster Recovery Patterns

1. Always On Availability Groups

-- Create availability group
CREATE AVAILABILITY GROUP AG_ProductionDB
WITH (
    DB_FAILOVER = ON,
    DTC_SUPPORT = NONE
)
FOR DATABASE ProductionDB
REPLICA ON 
    N'SQL-PRIMARY' WITH (
        ENDPOINT_URL = N'TCP://SQL-PRIMARY:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 90,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    ),
    N'SQL-SECONDARY' WITH (
        ENDPOINT_URL = N'TCP://SQL-SECONDARY:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY)
    );

-- Monitor AG health
SELECT 
    ar.replica_server_name,
    ars.role_desc,
    ars.operational_state_desc,
    ars.connected_state_desc,
    ars.synchronization_health_desc,
    drs.synchronization_state_desc,
    drs.last_commit_time
FROM sys.availability_replicas ar
JOIN sys.dm_hadr_availability_replica_states ars 
    ON ar.replica_id = ars.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states drs 
    ON ar.replica_id = drs.replica_id;

2. Log Shipping Pattern

-- Primary server backup job
DECLARE @BackupDirectory NVARCHAR(255) = N'\\shared\backups\';
DECLARE @BackupFile NVARCHAR(255) = @BackupDirectory + 'ProductionDB_' + 
    FORMAT(GETDATE(), 'yyyyMMdd_HHmmss') + '.trn';

BACKUP LOG ProductionDB 
TO DISK = @BackupFile
WITH COMPRESSION, INIT;

-- Secondary server restore job
RESTORE LOG ProductionDB_Secondary
FROM DISK = @BackupFile
WITH NORECOVERY, REPLACE;

-- Monitor log shipping status
SELECT 
    primary_server,
    primary_database,
    backup_threshold,
    last_backup_date,
    last_backup_file
FROM msdb.dbo.log_shipping_monitor_primary;

Performance Optimization Patterns

1. Advanced Indexing Strategies

-- Covering index with included columns
CREATE NONCLUSTERED INDEX IX_Employee_DeptSalary_Covering
ON Employee (DepartmentID, Salary DESC)
INCLUDE (Name, Email, HireDate);

-- Filtered index for active records only
CREATE NONCLUSTERED INDEX IX_Employee_Active_Name
ON Employee (Name)
WHERE IsActive = 1 AND TerminationDate IS NULL;

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

-- Index usage analysis
SELECT 
    i.name AS IndexName,
    ius.user_seeks,
    ius.user_scans,
    ius.user_lookups,
    ius.user_updates,
    ius.last_user_seek,
    ius.last_user_scan
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats ius 
    ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE OBJECT_NAME(i.object_id) = 'Employee'
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC;

2. Query Optimization Techniques

-- Efficient pagination with OFFSET/FETCH
DECLARE @PageSize INT = 20, @PageNumber INT = 5;

SELECT EmployeeID, Name, Department, Salary
FROM Employee
ORDER BY EmployeeID
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

-- Avoiding RBAR (Row By Agonizing Row)
-- Bad: Cursor-based processing
-- Good: Set-based operations
WITH SalaryUpdates AS (
    SELECT 
        EmployeeID,
        CASE 
            WHEN PerformanceRating >= 4 THEN Salary * 1.10
            WHEN PerformanceRating >= 3 THEN Salary * 1.05
            ELSE Salary
        END AS NewSalary
    FROM Employee e
    JOIN PerformanceReview pr ON e.EmployeeID = pr.EmployeeID
)
UPDATE e 
SET Salary = su.NewSalary
FROM Employee e
JOIN SalaryUpdates su ON e.EmployeeID = su.EmployeeID;

-- Dynamic SQL with parameter sniffing prevention
CREATE PROCEDURE GetEmployeesByDepartment
    @DepartmentID INT,
    @SalaryThreshold DECIMAL(10,2) = NULL
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT EmployeeID, Name, Salary, Department
        FROM Employee 
        WHERE DepartmentID = @DepartmentID';
    
    IF @SalaryThreshold IS NOT NULL
        SET @SQL = @SQL + N' AND Salary >= @SalaryThreshold';
    
    SET @SQL = @SQL + N' ORDER BY Salary DESC OPTION (RECOMPILE)';
    
    EXEC sp_executesql @SQL, 
        N'@DepartmentID INT, @SalaryThreshold DECIMAL(10,2)',
        @DepartmentID, @SalaryThreshold;
END;

Security Implementation Patterns

1. Row Level Security Implementation

-- Create security predicate function
CREATE FUNCTION fn_SecurityPredicate(@DepartmentID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS result 
WHERE @DepartmentID = CAST(SESSION_CONTEXT(N'DepartmentID') AS INT)
   OR IS_MEMBER('db_owner') = 1;

-- Create security policy
CREATE SECURITY POLICY DepartmentSecurityPolicy
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(DepartmentID) ON dbo.Employee,
ADD BLOCK PREDICATE dbo.fn_SecurityPredicate(DepartmentID) ON dbo.Employee AFTER INSERT,
ADD BLOCK PREDICATE dbo.fn_SecurityPredicate(DepartmentID) ON dbo.Employee AFTER UPDATE
WITH (STATE = ON);

-- Set user context
EXEC sp_set_session_context N'DepartmentID', 5;

2. Dynamic Data Masking

-- Apply masking to existing table
ALTER TABLE Employee 
ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'partial(2,"XX-XX-",4)');

ALTER TABLE Employee 
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE Employee 
ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'random(50000, 150000)');

-- Grant unmask permission
GRANT UNMASK TO PayrollRole;

Advanced T-SQL Patterns

1. Complex Recursive Queries

-- Organizational hierarchy with levels and paths
WITH OrgHierarchy AS (
    -- Anchor: Top-level managers
    SELECT 
        EmployeeID,
        Name,
        ManagerID,
        0 AS Level,
        CAST(Name AS NVARCHAR(MAX)) AS HierarchyPath,
        CAST(EmployeeID AS NVARCHAR(MAX)) AS IDPath
    FROM Employee 
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive: Subordinates
    SELECT 
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        oh.Level + 1,
        CAST(oh.HierarchyPath + ' -> ' + e.Name AS NVARCHAR(MAX)),
        CAST(oh.IDPath + ',' + CAST(e.EmployeeID AS NVARCHAR) AS NVARCHAR(MAX))
    FROM Employee e
    INNER JOIN OrgHierarchy oh ON e.ManagerID = oh.EmployeeID
    WHERE oh.Level < 10 -- Prevent infinite recursion
)
SELECT 
    EmployeeID,
    Name,
    Level,
    HierarchyPath,
    (SELECT COUNT(*) FROM Employee WHERE ManagerID = OrgHierarchy.EmployeeID) AS DirectReports
FROM OrgHierarchy
ORDER BY IDPath;

2. Advanced Window Functions

-- Running calculations with business rules
SELECT 
    EmployeeID,
    Name,
    Department,
    Salary,
    HireDate,
    
    -- Running total by department
    SUM(Salary) OVER (
        PARTITION BY Department 
        ORDER BY HireDate 
        ROWS UNBOUNDED PRECEDING
    ) AS DepartmentRunningTotal,
    
    -- Median salary by department
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) 
    OVER (PARTITION BY Department) AS MedianSalary,
    
    -- Previous and next salary in department
    LAG(Salary, 1) OVER (PARTITION BY Department ORDER BY HireDate) AS PrevSalary,
    LEAD(Salary, 1) OVER (PARTITION BY Department ORDER BY HireDate) AS NextSalary,
    
    -- Salary rank within department
    DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank,
    
    -- Quartile classification
    NTILE(4) OVER (ORDER BY Salary) AS SalaryQuartile
FROM Employee
WHERE IsActive = 1;

1. Always use parameterized queries to prevent SQL injection
2. Name objects consistently (e.g., tbl_ for tables, sp_ for procedures)
3. Document complex queries
4. Regular maintenance (index rebuild, statistics update)
5. Use appropriate data types and sizes
6. Implement proper error handling
7. Test queries with realistic data volumes
8. Monitor performance regularly
9. Keep transactions short
10. Use schemas for logical grouping

Last updated: Interview preparation guide for SQL Server positions