# Ultimate SQL Server Cheat Sheet

### Getting Started

#### **Connecting to SQL Server**

* Connect using SQL Server Management Studio (SSMS):
  * Open SSMS
  * Enter server name
  * Choose authentication mode (Windows/SQL Server)
  * Click 'Connect'

### Sample Data

* **Table: Employees**

```sql
-- create table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Department VARCHAR(50),
    Salary DECIMAL(10,2),
    Email VARCHAR(100) NULL
);
-- insert sample data
INSERT INTO Employees (Name, Age, Department, Salary, Email)
VALUES 
('John Doe', 30, 'IT', 60000.00, 'john.doe@example.com'),
('Jane Smith', 28, 'HR', 55000.00, 'jane.smith@example.com'),
('Alice Johnson', 35, 'Finance', 75000.00, 'alice.johnson@example.com'),
('Bob Williams', 40, 'IT', 80000.00, 'bob.williams@example.com'),
('Charlie Brown', 27, 'Marketing', 50000.00, 'charlie.brown@example.com');

```

* **Table: Departments**

```sql
-- create table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY IDENTITY(1,1),
    DepartmentName VARCHAR(50) NOT NULL
);
-- insert sample data
INSERT INTO Departments (DepartmentName)
VALUES 
('IT'),
('HR'),
('Finance'),
('Marketing'),
('Operations');

```

* **Table: Employee\_Audit**

```sql
-- create table
CREATE TABLE Employee_Audit (
    AuditID INT PRIMARY KEY IDENTITY(1,1),
    EmployeeID INT,
    ChangeDate DATETIME DEFAULT GETDATE(),
    ChangeDescription VARCHAR(255)
);
-- insert sample data
INSERT INTO Employee_Audit (EmployeeID, ChangeDescription)
VALUES 
(1, 'Salary updated to 65000'),
(2, 'Department changed to Finance'),
(3, 'New email added: alice.johnson@example.com'),
(4, 'Employee promoted to Senior IT Engineer'),
(5, 'Salary increased to 55000');

```

### **Basic Commands**

```sql
-- Create Database
CREATE DATABASE MyDatabase;

-- Use Database
USE MyDatabase;

-- Drop Database
DROP DATABASE MyDatabase;

-- Create Table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    Name VARCHAR(100) NOT NULL,
    Age INT,
    Department VARCHAR(50),
    Salary DECIMAL(10,2)
);

-- Drop Table
DROP TABLE Employees;

-- Alter Table
ALTER TABLE Employees ADD Email VARCHAR(100);
ALTER TABLE Employees DROP COLUMN Email;
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);

```

### CRUD Operations

```sql
-- Insert Data
INSERT INTO Employees (Name, Age, Department, Salary)
VALUES ('John Doe', 30, 'IT', 60000.00);

-- Update Data
UPDATE Employees SET Salary = 65000.00 WHERE EmployeeID = 1;

-- Delete Data
DELETE FROM Employees WHERE EmployeeID = 1;

-- Select Data
SELECT * FROM Employees;
SELECT Name, Salary FROM Employees WHERE Department = 'IT';

```

### Filtering & Sorting

```sql
-- WHERE Clause
SELECT * FROM Employees WHERE Age > 30;

-- ORDER BY Clause
SELECT * FROM Employees ORDER BY Salary DESC;

-- DISTINCT Clause
SELECT DISTINCT Department FROM Employees;

-- TOP Clause (Return top N rows)
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;

```

### Joins

```sql
-- INNER JOIN
SELECT e.Name, d.DepartmentName 
FROM Employees e 
INNER JOIN Departments d ON e.Department = d.DepartmentID;

-- LEFT JOIN
SELECT e.Name, d.DepartmentName 
FROM Employees e 
LEFT JOIN Departments d ON e.Department = d.DepartmentID;

-- RIGHT JOIN
SELECT e.Name, d.DepartmentName 
FROM Employees e 
RIGHT JOIN Departments d ON e.Department = d.DepartmentID;

-- FULL JOIN
SELECT e.Name, d.DepartmentName 
FROM Employees e 
FULL JOIN Departments d ON e.Department = d.DepartmentID;

```

### Aggregations & Grouping

```sql
-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) AS TotalEmployees FROM Employees;
SELECT AVG(Salary) AS AvgSalary FROM Employees;
SELECT MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary FROM Employees;

-- GROUP BY
SELECT Department, COUNT(*) AS EmployeeCount 
FROM Employees 
GROUP BY Department;

-- HAVING Clause
SELECT Department, AVG(Salary) AS AvgSalary 
FROM Employees 
GROUP BY Department 
HAVING AVG(Salary) > 50000;

```

### Subqueries

```sql
-- Subquery in WHERE
SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Subquery in FROM
SELECT * FROM (SELECT Name, Salary FROM Employees) AS TempTable;

```

### Common Table Expressions (CTEs)

```sql
WITH EmployeeCTE AS (
    SELECT Name, Salary FROM Employees WHERE Salary > 50000
)
SELECT * FROM EmployeeCTE;

```

### Window Functions

```sql
-- ROW_NUMBER, RANK, DENSE_RANK, NTILE
SELECT Name, Salary, 
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
       RANK() OVER (ORDER BY Salary DESC) AS RankNum,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;

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

```

### Indexing

```sql
-- Create Index
CREATE INDEX idx_EmployeeName ON Employees(Name);

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

-- Drop Index
DROP INDEX idx_EmployeeName ON Employees;

```

### Stored Procedures & Functions

```sql
-- Stored Procedure
CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees;
END;

EXEC GetEmployees;

-- Function
CREATE FUNCTION GetAverageSalary()
RETURNS DECIMAL(10,2)
AS
BEGIN
    DECLARE @AvgSalary DECIMAL(10,2);
    SELECT @AvgSalary = AVG(Salary) FROM Employees;
    RETURN @AvgSalary;
END;

SELECT dbo.GetAverageSalary();

```

### Transactions

```sql
-- Begin Transaction
BEGIN TRANSACTION;

-- Execute Queries
UPDATE Employees SET Salary = 70000 WHERE EmployeeID = 1;

-- Commit Transaction
COMMIT;

-- Rollback Transaction
ROLLBACK;

```

### Triggers

```sql
-- Create Trigger
CREATE TRIGGER trg_AfterInsert ON Employees
AFTER INSERT
AS
BEGIN
    PRINT 'New Employee Inserted';
END;

-- Drop Trigger
DROP TRIGGER trg_AfterInsert;

```

### Error Handling

```sql
BEGIN TRY
    UPDATE Employees SET Salary = -5000 WHERE EmployeeID = 1;
END TRY
BEGIN CATCH
    PRINT 'An error occurred';
END CATCH;

```

### Security & User Management

```sql
-- Create User
CREATE LOGIN MyUser WITH PASSWORD = 'StrongPassword!';
CREATE USER MyUser FOR LOGIN MyUser;

-- Grant Permissions
GRANT SELECT, INSERT, UPDATE ON Employees TO MyUser;

-- Revoke Permissions
REVOKE DELETE ON Employees FROM MyUser;

-- Drop User
DROP USER MyUser;

```

### Performance Tuning

```sql
-- Execution Plan
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees;
SET SHOWPLAN_ALL OFF;

-- Analyze Query Performance
DBCC FREEPROCCACHE; -- Clears query cache
DBCC DROPCLEANBUFFERS; -- Clears data cache

```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.consoleflare.com/cheat-sheet/ultimate-sql-server-cheat-sheet.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.