Beginner SQL Server Interview Questions and Answers

Core Microsoft SQL Server is a relational database management system (RDBMS) built on ANSI SQL with Microsoft’s Transact-SQL (T-SQL) extension. It stores, retrieves, and manages structured data and provides services such as replication, full-text search, reporting (SSRS), integration (SSIS), and analytics (SSAS).

Current editions:

• Enterprise — unlimited virtualisation, advanced HA (Always On AG), full SSAS, mission-critical workloads
• Standard — basic HA, limited memory/CPU caps; most mid-size applications
• Developer — full Enterprise features, free, for development and testing only (not production)
• Express — free, limited to 10 GB database size and 1 GB RAM; small databases and learning
• Web — licensed for internet-facing workloads; restricted features; low cost
• Azure SQL Database — fully managed PaaS cloud version; no infrastructure management
• Azure SQL Managed Instance — near 100% compatibility with on-premises SQL Server in the cloud

The latest version as of 2026 is SQL Server 2022 (version 16), which adds Azure Synapse Link, JSON enhancements, and built-in Azure connectivity.

Core T-SQL (Transact-SQL) is Microsoft’s proprietary extension to the ANSI SQL standard. It adds procedural programming features that standard SQL lacks.

T-SQL additions over ANSI SQL:

• Variables — DECLARE @name VARCHAR(50); SET @name = 'Alice';
• Flow control — IF/ELSE, WHILE, BEGIN...END, BREAK, CONTINUE
• Error handling — TRY...CATCH, THROW, RAISERROR
• Stored procedures & functions — procedural objects stored in the database
• Triggers — automatic event-driven code
• Cursors — row-by-row processing of result sets
• TOP / OFFSET-FETCH — non-standard row limiting (ANSI uses FETCH FIRST)
• String functions — CHARINDEX, PATINDEX, STUFF, STRING_AGG
• Date functions — DATEADD, DATEDIFF, GETDATE, FORMAT
• IDENTITY — auto-increment column (ANSI uses GENERATED AS IDENTITY)

-- T-SQL variable and flow control
DECLARE @threshold DECIMAL(10,2) = 1000.00;
DECLARE @count     INT;

SELECT @count = COUNT(*) FROM Orders WHERE Total > @threshold;

IF @count > 100
    PRINT 'High volume day';
ELSE
    PRINT 'Normal day';

Data Types

Exact numerics: INT, BIGINT, SMALLINT, TINYINT (0-255), BIT (0/1), DECIMAL(p,s) / NUMERIC(p,s), MONEY, SMALLMONEY

Approximate numerics: FLOAT, REAL — avoid for financial calculations (use DECIMAL)

Character strings:

• CHAR(n) — fixed-length, padded with spaces (max 8,000)
• VARCHAR(n) — variable-length (max 8,000); VARCHAR(MAX) up to 2 GB
• NCHAR(n), NVARCHAR(n) — Unicode (2 bytes per char); use for multilingual data
• NVARCHAR(MAX) — up to 2 GB Unicode text

Date and time: DATE, TIME, DATETIME (legacy), DATETIME2 (higher precision, preferred), DATETIMEOFFSET (with time zone), SMALLDATETIME

Other: UNIQUEIDENTIFIER (GUID), BINARY/VARBINARY, XML, JSON (stored as NVARCHAR, queried with JSON functions), GEOGRAPHY, GEOMETRY, ROWVERSION

CREATE TABLE Employees (
    Id          INT           IDENTITY(1,1) PRIMARY KEY,
    FirstName   NVARCHAR(100) NOT NULL,
    Salary      DECIMAL(12,2) NOT NULL,
    HireDate    DATE          NOT NULL,
    IsActive    BIT           NOT NULL DEFAULT 1,
    ProfilePic  VARBINARY(MAX),
    ExternalId  UNIQUEIDENTIFIER DEFAULT NEWID()
);

SQL

• DDL (Data Definition Language) — define and modify database structure: CREATE, ALTER, DROP, TRUNCATE, RENAME
• DML (Data Manipulation Language) — manipulate data: SELECT, INSERT, UPDATE, DELETE, MERGE
• DCL (Data Control Language) — manage permissions: GRANT, REVOKE, DENY
• TCL (Transaction Control Language) — manage transactions: BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT

-- DDL
CREATE TABLE Customers (Id INT PRIMARY KEY, Name NVARCHAR(100));
ALTER TABLE Customers ADD Email NVARCHAR(255);
DROP TABLE IF EXISTS TempData;

-- DML
INSERT INTO Customers (Id, Name) VALUES (1, 'Alice');
UPDATE Customers SET Name = 'Alicia' WHERE Id = 1;
DELETE FROM Customers WHERE Id = 1;
SELECT * FROM Customers WHERE Name LIKE 'A%';

-- DCL
GRANT SELECT, INSERT ON Customers TO ReadWriteRole;
REVOKE DELETE ON Customers FROM ReadWriteRole;
DENY TRUNCATE TABLE TO PublicRole;

-- TCL
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance -= 500 WHERE Id = 1;
    UPDATE Accounts SET Balance += 500 WHERE Id = 2;
COMMIT;  -- or ROLLBACK;

SQL

-- Sample: Customers and Orders

-- INNER JOIN — only matching rows in BOTH tables
SELECT c.Name, o.OrderDate
FROM Customers c
INNER JOIN Orders o ON o.CustomerId = c.Id;

-- LEFT (OUTER) JOIN — all rows from left + matching from right (NULL if none)
SELECT c.Name, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id;
-- Customers with no orders appear with NULL OrderDate

-- RIGHT (OUTER) JOIN — all rows from right + matching from left
SELECT c.Name, o.OrderDate
FROM Customers c
RIGHT JOIN Orders o ON o.CustomerId = c.Id;

-- FULL OUTER JOIN — all rows from both tables
SELECT c.Name, o.OrderDate
FROM Customers c
FULL OUTER JOIN Orders o ON o.CustomerId = c.Id;

-- CROSS JOIN — Cartesian product (every row × every row)
SELECT c.Name, p.ProductName
FROM Customers c
CROSS JOIN Products p;

-- SELF JOIN — join a table to itself
SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerId = m.Id;

-- Find customers with no orders (anti-join pattern)
SELECT c.Name FROM Customers c
LEFT JOIN Orders o ON o.CustomerId = c.Id
WHERE o.Id IS NULL;

Schema

CREATE TABLE Orders (
    Id          INT           IDENTITY(1,1)
                              CONSTRAINT PK_Orders PRIMARY KEY,  -- PRIMARY KEY
    OrderNo     NVARCHAR(20)  NOT NULL
                              CONSTRAINT UQ_Orders_OrderNo UNIQUE,  -- UNIQUE
    CustomerId  INT           NOT NULL
                              CONSTRAINT FK_Orders_Customer
                              REFERENCES Customers(Id)
                              ON DELETE RESTRICT
                              ON UPDATE CASCADE,                -- FOREIGN KEY
    Status      NVARCHAR(20)  NOT NULL DEFAULT 'Pending'        -- DEFAULT
                              CONSTRAINT CK_Orders_Status
                              CHECK (Status IN (
                                'Pending','Processing','Shipped','Delivered','Cancelled'
                              )),                              -- CHECK
    Quantity    INT           NOT NULL
                              CONSTRAINT CK_Orders_Quantity CHECK (Quantity > 0),
    Total       DECIMAL(12,2) NOT NULL
                              CONSTRAINT CK_Orders_Total CHECK (Total >= 0),
    CreatedAt   DATETIME2     NOT NULL DEFAULT SYSDATETIME()
);

-- Adding constraints after table creation
ALTER TABLE Products
ADD CONSTRAINT CK_Products_Price CHECK (Price > 0);

ALTER TABLE Products
ADD CONSTRAINT UQ_Products_Sku UNIQUE (Sku);

-- Named constraints are easier to drop later
ALTER TABLE Products
DROP CONSTRAINT CK_Products_Price;

SQL

• DELETE — DML; removes specific rows matching a WHERE clause (or all rows if no WHERE). Logged row by row. Fires DELETE triggers. Can be rolled back. Does NOT reset IDENTITY. Can use WHERE.
• TRUNCATE — DDL; removes ALL rows instantly by deallocating data pages. Minimally logged (much faster). Does NOT fire row-level DML triggers. Can be rolled back inside a transaction. Resets IDENTITY to seed value. Requires no foreign key references (or all FK-referencing tables must be empty).
• DROP TABLE — DDL; removes the entire table definition, all data, indexes, constraints, and triggers. Cannot be undone without a backup. Requires no active references.

-- DELETE — specific rows, triggers fire, IDENTITY unchanged, can use WHERE
DELETE FROM Orders WHERE Status = 'Cancelled';

-- TRUNCATE — ALL rows, faster, resets IDENTITY, no row triggers
TRUNCATE TABLE SessionLogs;

-- TRUNCATE in a transaction (can be rolled back in SQL Server)
BEGIN TRANSACTION;
TRUNCATE TABLE TempData;
-- ROLLBACK;  -- would restore rows

-- DROP — removes table entirely
DROP TABLE IF EXISTS TempData;

-- Key difference for interview:
-- DELETE is DML and fully logged; TRUNCATE is DDL and minimally logged
-- TRUNCATE cannot be used if a FK references the table
-- Neither DELETE nor TRUNCATE removes the table structure; DROP does

Indexes

• Clustered index — determines the physical order of data rows on disk. Only ONE per table. The leaf pages ARE the data pages. Primary key creates a clustered index by default. Range scans are very efficient.
• Non-clustered index — a separate B-tree structure with pointers back to the data rows (via RID for heap tables, or the clustered index key for clustered tables). Up to 999 per table. Additional lookup required to fetch non-indexed columns.

-- Clustered index (created automatically by PRIMARY KEY)
CREATE TABLE Products (
    Id   INT IDENTITY PRIMARY KEY,  -- creates clustered index on Id
    Name NVARCHAR(200) NOT NULL,
    Price DECIMAL(10,2)
);

-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_Products_Name ON Products (Name);

-- Composite non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
    ON Orders (CustomerId ASC, OrderDate DESC);

-- Covering index — include extra columns to avoid key lookup
CREATE NONCLUSTERED INDEX IX_Orders_Covering
    ON Orders (CustomerId, Status)
    INCLUDE (OrderDate, Total);   -- non-key columns in leaf pages
-- Query: SELECT OrderDate, Total WHERE CustomerId = 1 AND Status = 'Pending'
-- is satisfied entirely from the index — no table lookup needed

-- View indexes
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('Orders');

-- Rebuild / Reorganise index
ALTER INDEX IX_Products_Name ON Products REBUILD;
ALTER INDEX IX_Products_Name ON Products REORGANIZE;

Transactions

-- Explicit transaction with error handling
BEGIN TRANSACTION;

BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 500.00 WHERE AccountId = 1;
    UPDATE Accounts SET Balance = Balance + 500.00 WHERE AccountId = 2;

    -- Verify no negative balance
    IF EXISTS (SELECT 1 FROM Accounts WHERE Balance < 0)
        THROW 50001, 'Insufficient funds', 1;

    COMMIT TRANSACTION;
    PRINT 'Transfer completed successfully';
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Transfer failed: ' + ERROR_MESSAGE();
END CATCH;

-- Savepoints — partial rollback
BEGIN TRANSACTION;
    INSERT INTO Orders (...) VALUES (...);
    SAVE TRANSACTION AfterOrder;   -- savepoint

    INSERT INTO OrderItems (...) VALUES (...);
    -- Something fails...
    ROLLBACK TRANSACTION AfterOrder;  -- rolls back only to savepoint
    -- Orders insert is preserved; OrderItems insert is rolled back

COMMIT TRANSACTION;

ACID: Atomicity (all or nothing), Consistency (valid state transition), Isolation (concurrent transactions don’t interfere), Durability (committed data survives crashes — written to transaction log before data files).

Objects

-- Create a view (saved query — no data stored)
CREATE VIEW vw_ActiveOrders AS
SELECT
    o.Id,
    o.OrderNo,
    c.Name    AS CustomerName,
    o.Total,
    o.CreatedAt
FROM Orders o
INNER JOIN Customers c ON o.CustomerId = c.Id
WHERE o.Status NOT IN ('Cancelled', 'Delivered');
GO

-- Query like a table
SELECT * FROM vw_ActiveOrders WHERE Total > 100;
SELECT CustomerName, COUNT(*) FROM vw_ActiveOrders GROUP BY CustomerName;

-- Update view (must modify underlying table)
CREATE OR ALTER VIEW vw_ActiveOrders AS ...

-- Drop
DROP VIEW IF EXISTS vw_ActiveOrders;

-- Updatable views: simple single-table views support INSERT/UPDATE/DELETE
-- INSTEAD OF trigger: handle modifications for complex views

-- WITH CHECK OPTION — ensure modifications comply with the view's WHERE clause
CREATE VIEW vw_HighValueOrders AS
SELECT * FROM Orders WHERE Total > 1000
WITH CHECK OPTION;  -- prevents inserting rows with Total <= 1000 through the view

-- WITH SCHEMABINDING — prevents underlying columns/tables from being changed
-- Required for indexed views (materialised views)
CREATE VIEW vw_CustomerSummary
WITH SCHEMABINDING AS
SELECT CustomerId, COUNT_BIG(*) AS OrderCount, SUM(Total) AS TotalSpend
FROM dbo.Orders
GROUP BY CustomerId;

Objects A stored procedure is a precompiled batch of T-SQL statements stored as a named object in the database. The query plan is compiled and cached on first execution, reused on subsequent calls.

-- Create a stored procedure
CREATE OR ALTER PROCEDURE usp_GetOrdersByCustomer
    @CustomerId  INT,
    @StatusFilter NVARCHAR(20) = NULL,   -- optional parameter with default
    @TotalCount  INT OUTPUT              -- output parameter
AS
BEGIN
    SET NOCOUNT ON;  -- suppress "N rows affected" messages

    SELECT
        o.Id,
        o.OrderNo,
        o.Total,
        o.CreatedAt
    FROM Orders o
    WHERE o.CustomerId = @CustomerId
      AND (@StatusFilter IS NULL OR o.Status = @StatusFilter);

    SET @TotalCount = @@ROWCOUNT;  -- capture row count in output param
END;
GO

-- Execute the procedure
EXEC usp_GetOrdersByCustomer
    @CustomerId  = 42,
    @StatusFilter = 'Pending',
    @TotalCount  = @count OUTPUT;

PRINT 'Orders found: ' + CAST(@count AS VARCHAR);

-- Benefits of stored procedures:
-- 1. Precompiled — query plan cached (faster)
-- 2. Security — grant EXECUTE, not SELECT/INSERT/UPDATE
-- 3. Encapsulation — business logic in one place
-- 4. Reduce network traffic — one call executes many statements
-- 5. Parameterised — naturally protects against SQL injection

-- Drop
DROP PROCEDURE IF EXISTS usp_GetOrdersByCustomer;

SQL

SELECT
    COUNT(*)                      AS TotalRows,       -- includes NULLs
    COUNT(Email)                  AS NonNullEmails,   -- excludes NULLs
    COUNT(DISTINCT City)          AS UniqueCities,
    SUM(Salary)                   AS TotalPayroll,
    AVG(Salary)                   AS AvgSalary,
    MIN(Salary)                   AS MinSalary,
    MAX(Salary)                   AS MaxSalary,
    STDEV(Salary)                 AS SalaryStdDev,
    VAR(Salary)                   AS SalaryVariance,

    -- String aggregation (SQL Server 2017+)
    STRING_AGG(Name, ', ')
        WITHIN GROUP (ORDER BY Name)  AS NamesList,

    -- Checksum (detect changes in a set of rows)
    CHECKSUM_AGG(CHECKSUM(Salary)) AS SalaryChecksum

FROM Employees
WHERE DepartmentId = 3;

-- GROUP BY with HAVING
SELECT
    DepartmentId,
    COUNT(*)    AS HeadCount,
    AVG(Salary) AS AvgSalary,
    SUM(Salary) AS TotalPayroll
FROM Employees
WHERE IsActive = 1                    -- filter BEFORE grouping
GROUP BY DepartmentId
HAVING AVG(Salary) > 60000           -- filter AFTER grouping
ORDER BY TotalPayroll DESC;

SQL

• WHERE — filters individual rows before grouping and aggregation. Cannot reference aggregate functions. Applied to column values directly.
• HAVING — filters groups after grouping and aggregation. Can reference aggregate functions. Used together with GROUP BY.

-- Find departments with more than 5 active employees where avg salary > £50k

SELECT
    d.Name AS Department,
    COUNT(e.Id)    AS EmployeeCount,
    AVG(e.Salary)  AS AvgSalary
FROM Departments d
INNER JOIN Employees e ON e.DepartmentId = d.Id
WHERE  e.IsActive = 1                  -- filter rows BEFORE GROUP BY
GROUP BY d.Name
HAVING COUNT(e.Id) > 5                -- filter groups AFTER aggregation
   AND AVG(e.Salary) > 50000
ORDER BY AvgSalary DESC;

-- SQL execution order:
-- FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP/OFFSET

-- Common mistake: trying to use a SELECT alias in WHERE
-- ❌ SELECT Salary * 1.1 AS NewSalary FROM Employees WHERE NewSalary > 50000  (alias not yet defined)
-- ✅ SELECT Salary * 1.1 AS NewSalary FROM Employees WHERE Salary * 1.1 > 50000
-- ✅ Or wrap in a subquery / CTE

Functions

-- Length
SELECT LEN('  Hello  ');          -- 9 (counts chars, not bytes, excludes trailing spaces)
SELECT DATALENGTH('Hello');       -- 5 (bytes)

-- Case
SELECT UPPER('hello');            -- 'HELLO'
SELECT LOWER('HELLO');            -- 'hello'

-- Trimming
SELECT LTRIM('  Hello');          -- 'Hello' (removes leading spaces)
SELECT RTRIM('Hello  ');          -- 'Hello' (removes trailing spaces)
SELECT TRIM('  Hello  ');         -- 'Hello' (both sides — SQL Server 2017+)

-- Substring
SELECT SUBSTRING('SQL Server', 5, 6);  -- 'Server' (start pos 5, length 6)
SELECT LEFT('SQL Server', 3);          -- 'SQL'
SELECT RIGHT('SQL Server', 6);         -- 'Server'

-- Search
SELECT CHARINDEX('Serv', 'SQL Server');    -- 5 (position of first occurrence)
SELECT PATINDEX('%[0-9]%', 'abc123');      -- 4 (pattern search)

-- Replace / Stuff
SELECT REPLACE('Hello World', 'World', 'SQL');  -- 'Hello SQL'
SELECT STUFF('abcdef', 2, 3, 'XYZ');            -- 'aXYZef' (delete 3 chars at pos 2, insert 'XYZ')

-- Concatenation
SELECT CONCAT('Hello', ' ', 'World');           -- 'Hello World' (NULL-safe)
SELECT 'Hello' + ' ' + 'World';                -- 'Hello World' (NULL propagates)
SELECT CONCAT_WS(', ', 'Alice', 'Bob', NULL);   -- 'Alice, Bob' (separator, NULL-safe)

-- Padding
SELECT FORMAT(42, '000000');          -- '000042'
SELECT STR(3.14159, 6, 2);           -- '  3.14'
SELECT REPLICATE('0', 5);            -- '00000'

Functions

-- Current date/time
SELECT GETDATE();           -- DATETIME: 2026-04-20 14:30:00.000  (local server time)
SELECT SYSDATETIME();       -- DATETIME2: higher precision
SELECT GETUTCDATE();        -- UTC time (use this for portability)
SELECT SYSDATETIMEOFFSET(); -- DATETIMEOFFSET with timezone

-- Date parts
SELECT YEAR(GETDATE());     -- 2026
SELECT MONTH(GETDATE());    -- 4
SELECT DAY(GETDATE());      -- 20
SELECT DATEPART(HOUR, GETDATE());      -- 14
SELECT DATEPART(WEEKDAY, GETDATE());   -- 2 (depends on DATEFIRST setting)
SELECT DATENAME(MONTH, GETDATE());     -- 'April'

-- Date arithmetic
SELECT DATEADD(DAY,    7,  GETDATE());   -- add 7 days
SELECT DATEADD(MONTH, -3,  GETDATE());   -- subtract 3 months
SELECT DATEADD(YEAR,   1,  GETDATE());   -- add 1 year
SELECT DATEADD(HOUR,   8,  GETDATE());   -- add 8 hours

-- Difference
SELECT DATEDIFF(DAY,  '2026-01-01', GETDATE());   -- days since Jan 1
SELECT DATEDIFF(MONTH,'2024-01-01', GETDATE());   -- months

-- Formatting and conversion
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');            -- '2026-04-20'
SELECT FORMAT(GETDATE(), 'dd MMM yyyy HH:mm');     -- '20 Apr 2026 14:30'
SELECT CONVERT(VARCHAR, GETDATE(), 103);           -- '20/04/2026'
SELECT CAST(GETDATE() AS DATE);                    -- '2026-04-20' (truncate time)

-- Truncate to start of day/month
SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME2); -- start of today
SELECT DATETRUNC(MONTH, GETDATE());                -- start of current month (SQL Server 2022)

SQL

-- NULL comparisons — ALWAYS use IS NULL / IS NOT NULL
SELECT * FROM Customers WHERE Phone IS NULL;       -- ✅
SELECT * FROM Customers WHERE Phone = NULL;        -- ❌ always 0 rows

-- NULL in arithmetic and comparisons propagates NULL
SELECT NULL + 5;            -- NULL
SELECT NULL = NULL;         -- NULL (not TRUE)
SELECT NULL <> NULL;        -- NULL

-- ISNULL — return replacement if NULL
SELECT ISNULL(Phone, 'N/A') FROM Customers;

-- COALESCE — return first non-NULL (ANSI standard, prefers COALESCE)
SELECT COALESCE(MobilePhone, HomePhone, 'No phone') FROM Customers;

-- NULLIF — return NULL if two values are equal (avoid divide-by-zero)
SELECT Revenue / NULLIF(Units, 0) AS PricePerUnit FROM Sales;

-- IS DISTINCT FROM / IS NOT DISTINCT FROM (SQL Server 2022)
SELECT * FROM Products WHERE Price IS DISTINCT FROM OldPrice;  -- NULL-safe compare

-- COUNT behaviour
SELECT COUNT(*)     FROM Orders;  -- counts all rows including NULLs
SELECT COUNT(Notes) FROM Orders;  -- counts only non-NULL Notes

-- Sorting: NULLs sort LAST in ORDER BY ASC by default in SQL Server
SELECT Name, Phone FROM Customers ORDER BY Phone;
-- NULLs appear at the end in ascending order

-- ANSI_NULLS setting (on by default — NULL <> NULL)
SET ANSI_NULLS ON;

SQL

-- UNION — combines result sets, removes duplicates (sort + deduplicate, slower)
SELECT Email FROM Customers
UNION
SELECT Email FROM Prospects;
-- 'alice@example.com' appears ONCE even if in both tables

-- UNION ALL — combines all rows including duplicates (faster, no sort)
SELECT Email FROM Customers
UNION ALL
SELECT Email FROM Prospects;
-- 'alice@example.com' appears TWICE if in both tables

-- Rules for UNION:
-- Both queries must have the same number of columns
-- Column data types must be compatible (implicit conversion allowed)
-- Column names come from the FIRST query
-- ORDER BY must be at the end of the final query

-- Multiple sets with labels
SELECT CustomerId, Total, 'Invoice'   AS DocumentType FROM Invoices
UNION ALL
SELECT CustomerId, Total, 'CreditNote' AS DocumentType FROM CreditNotes
UNION ALL
SELECT CustomerId, Total, 'Payment'   AS DocumentType FROM Payments
ORDER BY CustomerId, DocumentType;

-- Performance: prefer UNION ALL unless you explicitly need deduplication
-- UNION adds a sort/hash operation; UNION ALL does not

Tooling SSMS (SQL Server Management Studio) is Microsoft’s free IDE for managing SQL Server, Azure SQL, and Azure Synapse. It provides a visual interface for database administration, query writing, and performance analysis.

Key features:

• Object Explorer — tree view of all databases, tables, views, stored procedures, and server objects
• Query Editor — IntelliSense, syntax highlighting, multi-tab, plan display
• Execution Plan — visual query execution plan (Ctrl+M to include actual plan)
• Activity Monitor — live CPU, waits, expensive queries, blocked processes
• Database Diagrams — visual ER diagram
• Generate Scripts — script objects (tables, procedures, data) for deployment
• Import/Export Wizard — bulk data transfer between sources
• SQL Server Profiler — trace live queries (deprecated; use Extended Events instead)
• Extended Events — lightweight query monitoring and diagnostics
• Database Backup/Restore — GUI-based backup management

-- Useful keyboard shortcuts in SSMS:
-- F5 or Ctrl+E  — execute query
-- Ctrl+M        — include actual execution plan
-- Ctrl+L        — display estimated execution plan
-- Ctrl+K, Ctrl+C — comment selection
-- Ctrl+K, Ctrl+U — uncomment selection
-- Ctrl+R        — toggle results pane

Schema

-- IDENTITY(seed, increment) — auto-generated sequential integer
CREATE TABLE Products (
    Id    INT           IDENTITY(1, 1) PRIMARY KEY,  -- starts at 1, increments by 1
    Name  NVARCHAR(200) NOT NULL
);

-- Insert without specifying Id — it is generated automatically
INSERT INTO Products (Name) VALUES ('Widget');   -- Id = 1
INSERT INTO Products (Name) VALUES ('Gadget');   -- Id = 2

-- Get the last inserted IDENTITY value
SELECT SCOPE_IDENTITY();    -- last value in current scope (PREFERRED)
SELECT @@IDENTITY;          -- last value in current connection (may include triggers)
SELECT IDENT_CURRENT('Products'); -- last value for the table (any scope)

-- Temporarily allow explicit INSERT into IDENTITY column
SET IDENTITY_INSERT Products ON;
INSERT INTO Products (Id, Name) VALUES (100, 'SpecialItem');
SET IDENTITY_INSERT Products OFF;

-- Reseed IDENTITY (reset counter)
DBCC CHECKIDENT ('Products', RESEED, 0);  -- next insert will be 1

-- Sequence object (modern alternative to IDENTITY — more flexible)
CREATE SEQUENCE dbo.OrderSequence
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 9999999
    CYCLE;  -- wrap around

SELECT NEXT VALUE FOR dbo.OrderSequence; -- get next value

Data Types

• CHAR(n) — fixed-length non-Unicode. Always n bytes. Padded with trailing spaces. Max 8,000 bytes. Best for fixed-length codes (ISO country codes, status flags).
• VARCHAR(n) — variable-length non-Unicode. Up to n bytes. No padding. VARCHAR(MAX) = up to 2 GB. Best for variable English/ASCII text.
• NCHAR(n) — fixed-length Unicode (UTF-16). Always 2n bytes. Max 4,000 characters.
• NVARCHAR(n) — variable-length Unicode. Up to 2n bytes. NVARCHAR(MAX) = up to 2 GB. Use for any text that may contain non-ASCII characters (names, addresses, multilingual content).

-- Storage comparison
-- CHAR(10) 'Hi' = 10 bytes (padded with 8 spaces)
-- VARCHAR(10) 'Hi' = 2 bytes + 2 overhead = 4 bytes
-- NCHAR(10) 'Hi' = 20 bytes
-- NVARCHAR(10) 'Hi' = 4 bytes + 2 overhead = 6 bytes

-- String literal prefix for Unicode (IMPORTANT!)
INSERT INTO Users (Name) VALUES ('Alice');         -- ASCII literal
INSERT INTO Users (Name) VALUES (N'Álice');        -- N prefix = Unicode literal
-- Without N prefix, accented chars may lose diacritics if column is NVARCHAR

-- Recommendation:
-- Use NVARCHAR for user-entered text, names, addresses (internationalization)
-- Use VARCHAR for internal codes, URLs, email addresses (ASCII-safe)
-- Avoid CHAR unless you have a true fixed-length requirement

SQL

-- Scalar subquery — returns a single value
SELECT Name, Salary,
       (SELECT AVG(Salary) FROM Employees) AS CompanyAvg
FROM Employees;

-- Subquery in WHERE
SELECT Name FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

-- Subquery in FROM (derived table)
SELECT dept, avg_sal
FROM (
    SELECT Department AS dept, AVG(Salary) AS avg_sal
    FROM Employees
    GROUP BY Department
) AS dept_stats
WHERE avg_sal > 70000;

-- IN subquery
SELECT Name FROM Customers
WHERE Id IN (SELECT CustomerId FROM Orders WHERE Status = 'Pending');

-- EXISTS subquery (often more efficient than IN for large sets)
SELECT Name FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id);

-- NOT EXISTS (anti-join)
SELECT Name FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id);

-- Correlated subquery — references outer query's current row (re-evaluates per row)
SELECT e.Name, e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)           -- uses e.DepartmentId from outer query
    FROM Employees e2
    WHERE e2.DepartmentId = e.DepartmentId
);

SQL

-- Simple CASE (test for equality)
SELECT
    OrderId,
    Status,
    CASE Status
        WHEN 'Pending'    THEN 'Awaiting processing'
        WHEN 'Processing' THEN 'In progress'
        WHEN 'Shipped'    THEN 'On its way'
        WHEN 'Delivered'  THEN 'Complete'
        ELSE 'Unknown status'
    END AS StatusDescription
FROM Orders;

-- Searched CASE (flexible conditions — like IF/ELSE IF)
SELECT
    Name, Salary,
    CASE
        WHEN Salary <  30000 THEN 'Entry'
        WHEN Salary <  60000 THEN 'Mid'
        WHEN Salary < 100000 THEN 'Senior'
        ELSE 'Executive'
    END AS SalaryBand,

    -- CASE in ORDER BY
    CASE WHEN IsVIP = 1 THEN 0 ELSE 1 END AS SortPriority
FROM Employees
ORDER BY SortPriority, Name;

-- CASE in aggregate (conditional aggregation)
SELECT
    DepartmentId,
    COUNT(*) AS Total,
    SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) AS FemaleCount,
    SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) AS MaleCount,
    AVG(CASE WHEN IsManager = 1 THEN Salary END)   AS AvgManagerSalary
FROM Employees
GROUP BY DepartmentId;

-- IIF — shorthand for simple two-branch CASE (SQL Server 2012+)
SELECT Name, IIF(IsActive = 1, 'Active', 'Inactive') AS Status FROM Employees;