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

-- Connect using sqlcmd utility
sqlcmd -S ServerName -U Username -P Password

-- Connection string example
Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

1
2
3
4
5
6
7
8
9
10
11
12
-- 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
SELECT @@VERSION AS SQLServerVersion;

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 = 10%
)
LOG ON
(
    NAME = 'MyDatabase_Log',
    FILENAME = 'C:\SQLData\MyDatabase_Log.ldf',
    SIZE = 50MB,
    MAXSIZE = 2GB,
    FILEGROWTH = 10%
);

-- Use a specific database
USE MyDatabase;

-- Drop a database
DROP DATABASE MyDatabase;

-- Create database with specific collation
CREATE DATABASE MyDatabase 
COLLATE SQL_Latin1_General_CP1_CI_AS;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 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 GETDATE(),
    DepartmentID INT
);

-- Create table with foreign key
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(50),
    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
ALTER TABLE Employees 
DROP COLUMN PhoneNumber;

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

1
2
3
4
5
6
-- Drop a table
DROP TABLE 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
-- Basic select
SELECT * FROM Employees;

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

-- Sorting and limiting
SELECT TOP 5 * 
FROM Employees 
ORDER BY Salary DESC;

-- 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 Department = 'Sales';

-- 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
-- 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;

1
2
3
4
5
6
7
8
9
10
11
12
-- Clustered Index (Primary Key)
CREATE CLUSTERED INDEX IX_Employees_ID
ON Employees(EmployeeID);

-- Non-Clustered Index
CREATE NONCLUSTERED INDEX IX_Employees_LastName
ON Employees(LastName);

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

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
-- Concatenation
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

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

-- Trim
SELECT LTRIM(RTRIM(FirstName)) AS CleanName FROM Employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Current Date
SELECT GETDATE() AS CurrentDateTime;

-- Date Parts
SELECT 
    YEAR(HireDate) AS HireYear,
    MONTH(HireDate) AS HireMonth,
    DAY(HireDate) AS HireDay
FROM Employees;

-- Date Calculation
SELECT 
    DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsEmployed
FROM Employees;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 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;

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

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

    UNION ALL

    -- Recursive case
    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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Ranking
SELECT 
    FirstName, 
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseSalaryRank
FROM Employees;

-- Running Total
SELECT 
    FirstName, 
    Salary,
    SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

1
2
3
4
5
6
7
8
9
10
11
12
-- Upsert Operation
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 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
CREATE PROCEDURE GetEmployeesBySalaryRange
    @MinSalary DECIMAL(10,2),
    @MaxSalary DECIMAL(10,2)
AS
BEGIN
    SELECT * FROM Employees
    WHERE Salary BETWEEN @MinSalary AND @MaxSalary
END;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE FUNCTION dbo.CalculateAge
(
    @Birthdate DATE
)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @Birthdate, GETDATE()) - 
           CASE 
               WHEN (MONTH(@Birthdate) > MONTH(GETDATE())) OR 
                    (MONTH(@Birthdate) = MONTH(GETDATE()) AND DAY(@Birthdate) > DAY(GETDATE()))
               THEN 1 
               ELSE 0 
           END
END;

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

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

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

-- Revoke Permissions
REVOKE INSERT ON Employees FROM MyAppUser;

-- Deny Permissions
DENY DELETE ON Employees TO MyAppUser;

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

-- Avoid functions in WHERE clause that prevent index usage
-- Bad: WHERE YEAR(OrderDate) = 2023
-- Good: WHERE OrderDate >= '2023-01-01' AND OrderDate < '2024-01-01'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
BEGIN TRY
    BEGIN TRANSACTION
        -- Your database operations
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    -- Log error details
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

1
2
3
4
5
6
7
8
9
10
-- Use SQL Server bulk insert for large datasets
BULK INSERT Employees
FROM 'C:\data\employees.csv'
WITH 
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

1
2
3
4
5
6
7
-- Prefer set-based operations over cursors
-- Bad: Cursor-based update
-- Good: Set-based update
UPDATE Employees
SET Salary = Salary * 1.1
WHERE Performance = 'Excellent';