1
2
3
4
5
6
7
8
9
10
-- Connect using SQL Server Management Studio (SSMS) or Azure Data Studio
-- Server name: [ServerName]\[InstanceName]

-- Connect using sqlcmd utility (go-sqlcmd is the modern cross-platform replacement)
sqlcmd -S ServerName -U Username -P Password
sqlcmd -S ServerName -G          -- Microsoft Entra ID (formerly Azure AD) interactive auth

-- Connection string example (TLS encryption is required by default in modern drivers)
Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Encrypt=True;TrustServerCertificate=False;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Show all databases
SELECT name FROM sys.databases;

-- Show current database
SELECT DB_NAME() AS CurrentDatabase;

-- Show current user
SELECT SUSER_SNAME() AS CurrentUser;

-- Check SQL Server version and edition
SELECT @@VERSION AS SQLServerVersion;
SELECT SERVERPROPERTY('ProductVersion')   AS ProductVersion,
       SERVERPROPERTY('ProductLevel')     AS ProductLevel,
       SERVERPROPERTY('Edition')          AS Edition,
       SERVERPROPERTY('EngineEdition')    AS EngineEdition;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Create a new database
CREATE DATABASE MyDatabase
ON PRIMARY
(
    NAME = 'MyDatabase_Primary',
    FILENAME = 'C:\SQLData\MyDatabase.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 64MB
)
LOG ON
(
    NAME = 'MyDatabase_Log',
    FILENAME = 'C:\SQLData\MyDatabase_Log.ldf',
    SIZE = 50MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 64MB
);

-- Use a specific database
USE MyDatabase;

-- Drop a database (use IF EXISTS to avoid errors)
DROP DATABASE IF EXISTS MyDatabase;

-- Create database with specific collation (UTF-8 collations are supported in SQL Server 2019+)
CREATE DATABASE MyDatabase
COLLATE Latin1_General_100_CI_AS_SC_UTF8;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Basic table creation
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    Salary DECIMAL(10,2),
    HireDate DATE DEFAULT SYSUTCDATETIME(),
    DepartmentID INT
);

-- Create table with foreign key
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50)
);

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Add a column
ALTER TABLE Employees
ADD PhoneNumber NVARCHAR(20);

-- Modify column
ALTER TABLE Employees
ALTER COLUMN Email NVARCHAR(150);

-- Drop column (IF EXISTS supported)
ALTER TABLE Employees
DROP COLUMN IF EXISTS PhoneNumber;

-- Add constraint
ALTER TABLE Employees
ADD CONSTRAINT CHK_Salary
CHECK (Salary > 0);

1
2
3
4
5
6
-- Drop a table (IF EXISTS supported)
DROP TABLE IF EXISTS Employees;

-- Truncate (remove all data, keep structure)
TRUNCATE TABLE Employees;

1
2
3
-- Decimal with 10 total digits, 2 after decimal point
DECIMAL(10,2) -- e.g., 12345678.90

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Basic insert
INSERT INTO Employees
    (FirstName, LastName, Email, Salary)
VALUES
    ('John', 'Doe', 'john.doe@example.com', 75000);

-- Multiple row insert
INSERT INTO Employees
    (FirstName, LastName, Email, Salary)
VALUES
    ('Jane', 'Smith', 'jane@example.com', 80000),
    ('Bob',  'Johnson', 'bob@example.com', 65000);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Basic select
SELECT * FROM Employees;

-- Filtered select
SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary > 70000;

-- Sorting and limiting (prefer OFFSET/FETCH for pagination)
SELECT TOP (5) *
FROM Employees
ORDER BY Salary DESC;

SELECT *
FROM Employees
ORDER BY Salary DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

-- Distinct values
SELECT DISTINCT DepartmentID
FROM Employees;

1
2
3
4
5
6
7
8
9
10
-- Update specific rows
UPDATE Employees
SET Salary = Salary * 1.1
WHERE DepartmentID = 3;

-- Conditional update
UPDATE Employees
SET Email = CONCAT(FirstName, '.', LastName, '@company.com')
WHERE Email IS NULL;

1
2
3
4
5
6
7
8
-- Delete specific rows
DELETE FROM Employees
WHERE EmployeeID = 100;

-- Conditional delete
DELETE FROM Employees
WHERE HireDate < '2020-01-01';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Inner Join
SELECT e.FirstName, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Left Join
SELECT e.FirstName, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Right Join
SELECT e.FirstName, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Full Outer Join
SELECT e.FirstName, d.DepartmentName
FROM Employees e
FULL OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- CROSS APPLY / OUTER APPLY (correlated joins, great with table-valued functions)
SELECT e.FirstName, recent.OrderDate
FROM Employees e
CROSS APPLY (
    SELECT TOP (1) o.OrderDate
    FROM Orders o
    WHERE o.EmployeeID = e.EmployeeID
    ORDER BY o.OrderDate DESC
) recent;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Clustered Index (typically the Primary Key)
CREATE CLUSTERED INDEX IX_Employees_ID
ON Employees(EmployeeID);

-- Non-Clustered Index with included (covering) columns
CREATE NONCLUSTERED INDEX IX_Employees_LastName
ON Employees(LastName)
INCLUDE (FirstName, Email);

-- Unique Index
CREATE UNIQUE NONCLUSTERED INDEX IX_Employees_Email
ON Employees(Email);

-- Filtered Index (smaller, focused indexes)
CREATE NONCLUSTERED INDEX IX_Employees_ActiveHighEarners
ON Employees(Salary)
WHERE Salary > 100000;

-- Columnstore Index (analytics / OLAP workloads)
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_CS
ON Sales(OrderDate, ProductID, Quantity, Amount);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Primary Key
ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

-- Foreign Key
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

-- Check Constraint
ALTER TABLE Employees
ADD CONSTRAINT CHK_SalaryRange
CHECK (Salary BETWEEN 30000 AND 200000);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Concatenation
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;
SELECT CONCAT_WS(' - ', FirstName, LastName, Email) AS Info FROM Employees;

-- Substring
SELECT SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS Username
FROM Employees;

-- Trim (TRIM handles both sides; LTRIM/RTRIM also accept characters to trim)
SELECT TRIM(FirstName) AS CleanName FROM Employees;

-- STRING_AGG (concatenate rows into a single string)
SELECT DepartmentID, STRING_AGG(LastName, ', ') WITHIN GROUP (ORDER BY LastName) AS Members
FROM Employees
GROUP BY DepartmentID;

-- STRING_SPLIT (with ordinal column, SQL Server 2022+)
SELECT value, ordinal
FROM STRING_SPLIT('a,b,c', ',', 1);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Current date/time (prefer the SYSDATETIME family for higher precision and UTC awareness)
SELECT SYSDATETIME()       AS LocalNow,
       SYSUTCDATETIME()    AS UtcNow,
       SYSDATETIMEOFFSET() AS NowWithOffset;

-- Date Parts
SELECT
    YEAR(HireDate)  AS HireYear,
    MONTH(HireDate) AS HireMonth,
    DAY(HireDate)   AS HireDay,
    DATETRUNC(month, HireDate) AS HireMonthStart  -- SQL Server 2022+
FROM Employees;

-- Date Calculation
SELECT
    DATEDIFF(YEAR, HireDate, SYSUTCDATETIME()) AS YearsEmployed,
    DATE_BUCKET(WEEK, 1, HireDate)             AS HireWeekBucket  -- SQL Server 2022+
FROM Employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Basic Aggregates
SELECT
    COUNT(*)    AS TotalEmployees,
    AVG(Salary) AS AverageSalary,
    MAX(Salary) AS MaxSalary,
    MIN(Salary) AS MinSalary
FROM Employees;

-- Grouping
SELECT
    DepartmentID,
    AVG(Salary) AS AvgDepartmentSalary
FROM Employees
GROUP BY DepartmentID;

-- APPROX_COUNT_DISTINCT and APPROX_PERCENTILE_CONT for very large datasets
SELECT APPROX_COUNT_DISTINCT(CustomerID) AS ApproxCustomers FROM Orders;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Basic CTE
WITH HighSalaryEmployees AS (
    SELECT *
    FROM Employees
    WHERE Salary > 100000
)
SELECT * FROM HighSalaryEmployees;

-- Recursive CTE (Hierarchical Data)
WITH EmployeeHierarchy AS (
    -- Anchor member
    SELECT EmployeeID, ManagerID, FirstName, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.EmployeeID, e.ManagerID, e.FirstName, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy
OPTION (MAXRECURSION 100);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Ranking
SELECT
    FirstName,
    Salary,
    RANK()       OVER (ORDER BY Salary DESC) AS SalaryRank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseSalaryRank,
    ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowInDept
FROM Employees;

-- Running Total with explicit frame
SELECT
    FirstName,
    Salary,
    SUM(Salary) OVER (
        ORDER BY EmployeeID
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningTotal
FROM Employees;

-- Named windows (SQL Server 2022+)
SELECT FirstName,
       AVG(Salary) OVER w AS DeptAvg,
       MAX(Salary) OVER w AS DeptMax
FROM Employees
WINDOW w AS (PARTITION BY DepartmentID);

1
2
3
4
5
6
7
8
9
10
11
12
13
-- Upsert Operation. Always terminate MERGE with a semicolon and review known caveats
-- before using it in concurrent workloads; many teams prefer separate UPDATE/INSERT.
MERGE INTO Customers AS target
USING (SELECT * FROM NewCustomers) AS source
   ON (target.CustomerID = source.CustomerID)
WHEN MATCHED THEN
    UPDATE SET
        Name  = source.Name,
        Email = source.Email
WHEN NOT MATCHED BY TARGET THEN
    INSERT (CustomerID, Name, Email)
    VALUES (source.CustomerID, source.Name, source.Email);

1
2
3
4
5
-- Capture inserted/deleted rows
DELETE FROM Employees
OUTPUT deleted.EmployeeID, deleted.FirstName, deleted.LastName
WHERE Salary < 50000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Query JSON data
SELECT JSON_VALUE(Payload, '$.customer.id')     AS CustomerId,
       JSON_QUERY(Payload, '$.items')           AS Items,
       JSON_OBJECT('id': EmployeeID, 'name': FirstName) AS EmpJson  -- SQL Server 2022+
FROM Orders
WHERE ISJSON(Payload) = 1;

-- Shred JSON into rows/columns
SELECT *
FROM OPENJSON(@json, '$.items')
WITH (
    ProductID INT          '$.productId',
    Quantity  INT          '$.qty',
    Name      NVARCHAR(100)'$.name'
);

-- Produce JSON from a query
SELECT EmployeeID, FirstName, LastName
FROM Employees
FOR JSON PATH, ROOT('employees');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR ALTER PROCEDURE dbo.GetEmployeesBySalaryRange
    @MinSalary DECIMAL(10,2),
    @MaxSalary DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM Employees
    WHERE Salary BETWEEN @MinSalary AND @MaxSalary;
END;

-- Execute procedure
EXEC dbo.GetEmployeesBySalaryRange @MinSalary = 50000, @MaxSalary = 100000;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Mark scalar UDFs as deterministic when possible; SQL Server can inline many
-- T-SQL scalar UDFs automatically (2019+) for much better performance.
CREATE OR ALTER FUNCTION dbo.CalculateAge
(
    @Birthdate DATE
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEDIFF(YEAR, @Birthdate, SYSUTCDATETIME()) -
           CASE
               WHEN (MONTH(@Birthdate) > MONTH(SYSUTCDATETIME())) OR
                    (MONTH(@Birthdate) = MONTH(SYSUTCDATETIME()) AND DAY(@Birthdate) > DAY(SYSUTCDATETIME()))
               THEN 1
               ELSE 0
           END;
END;

1
2
3
4
5
6
7
8
9
10
11
CREATE OR ALTER FUNCTION dbo.EmployeesInDept (@DeptId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM dbo.Employees
    WHERE DepartmentID = @DeptId
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Create Login
CREATE LOGIN MyAppUser WITH PASSWORD = 'StrongPassword123!';

-- Create Database User
CREATE USER MyAppUser FOR LOGIN MyAppUser;

-- Microsoft Entra ID (Azure AD) user in Azure SQL / managed identities
-- CREATE USER [app@contoso.com] FROM EXTERNAL PROVIDER;

-- Grant Permissions
GRANT SELECT, INSERT ON Employees TO MyAppUser;

-- Revoke / Deny
REVOKE INSERT ON Employees FROM MyAppUser;
DENY DELETE ON Employees TO MyAppUser;

-- Role-based access
CREATE ROLE app_reader;
GRANT SELECT ON SCHEMA::dbo TO app_reader;
ALTER ROLE app_reader ADD MEMBER MyAppUser;

1
2
3
4
-- Example: mask an email column
ALTER TABLE Employees
ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Use SET STATISTICS IO, TIME ON to analyze query performance
SET STATISTICS IO, TIME ON;
-- Your query here
SET STATISTICS IO, TIME OFF;

-- Inspect actual plans / live data via Query Store and sys.dm_exec_* DMVs
SELECT TOP (20) qs.execution_count, qs.total_elapsed_time, st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_elapsed_time DESC;

-- Make predicates SARGable (no functions on indexed columns)
-- Bad : WHERE YEAR(OrderDate) = 2025
-- Good: WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SET XACT_ABORT ON;  -- Ensures the transaction is rolled back on most runtime errors

BEGIN TRY
    BEGIN TRANSACTION;
        -- Your database operations
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Optionally rethrow the original error
    THROW;

    -- Or inspect it
    -- SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(),
    --        ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE();
END CATCH;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Use BULK INSERT or bcp for large data loads
BULK INSERT Employees
FROM 'C:\data\employees.csv'
WITH
(
    FORMAT          = 'CSV',
    FIRSTROW        = 2,
    FIELDQUOTE      = '"',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '0x0a',
    TABLOCK
);

-- Load directly from Azure Blob Storage / S3-compatible storage
-- BULK INSERT dbo.Employees FROM 'employees.csv'
-- WITH (DATA_SOURCE = 'MyAzureBlobStorage', FORMAT = 'CSV', FIRSTROW = 2);

1
2
3
4
5
-- Avoid cursors and RBAR (row-by-agonizing-row) loops when a set-based statement works.
UPDATE Employees
SET Salary = Salary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM Reviews WHERE Rating = 'Excellent');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Automatically track every change with a hidden history table
CREATE TABLE dbo.Products
(
    ProductID    INT PRIMARY KEY,
    Name         NVARCHAR(100),
    Price        DECIMAL(10,2),
    ValidFrom    DATETIME2 GENERATED ALWAYS AS ROW START   NOT NULL,
    ValidTo      DATETIME2 GENERATED ALWAYS AS ROW END     NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));

-- Query historical state
SELECT * FROM dbo.Products
FOR SYSTEM_TIME AS OF '2025-01-01T00:00:00';