SQL Server Interview Questions

T-SQL (Transact-SQL) is Microsoft's proprietary extension to SQL (Structured Query Language) used in SQL Server. It includes:

• All standard SQL features
• Additional programming constructs (variables, loops, conditionals)
• Enhanced error handling (TRY/CATCH)
• Special functions and system stored procedures
• Support for procedural programming

T-SQL is used to manage and manipulate data in SQL Server databases.

SQL Server comes in several editions:

1. Enterprise: Full-featured version for large enterprises
2. Standard: Core database capabilities for mid-tier applications
3. Web: Low-cost option for web hosting
4. Developer: Full features for development/testing (not for production)
5. Express: Free, lightweight version with limitations
6. Azure SQL Database: Cloud-based version

To install SQL Server:

1. Windows:
   • Download SQL Server Installation Center from Microsoft
   • Run setup and choose "New SQL Server stand-alone installation"
   • Select features (Database Engine Services, etc.)
   • Configure instance (Default or Named instance)
   • Set authentication mode (Windows or Mixed)
   • Configure service accounts
   • Complete installation
2. Linux:
   • Add Microsoft SQL Server repository
   • Install packages: sudo apt-get install -y mssql-server
   • Run setup: sudo /opt/mssql/bin/mssql-conf setup
   • Verify service is running: systemctl status mssql-server

After installation, connect using SQL Server Management Studio (SSMS) or sqlcmd utility.

Key SQL Server management tools:

1. SQL Server Management Studio (SSMS):
   • Primary GUI tool for managing SQL Server
   • Query editing with IntelliSense
   • Database design and administration
   • Performance monitoring
2. Azure Data Studio:
   • Cross-platform tool for SQL Server
   • Lightweight alternative to SSMS
   • Excellent for query editing and notebooks
3. SQL Server Configuration Manager:
   • Manage SQL Server services
   • Configure network protocols
   • Manage client configurations
4. sqlcmd Utility:
   • Command-line tool for executing T-SQL
   • Useful for scripting and automation

Common ways to connect to SQL Server:

1. Using SSMS:
   • Launch SSMS
   • Enter server name (e.g., "localhost" or server IP)
   • Select authentication (Windows or SQL Server)
   • Provide credentials if using SQL Server auth
   • Click "Connect"
2. Using sqlcmd:

   sqlcmd -S server_name -U username -P password -d database_name

3. Using Connection String in Applications:

   "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;"

4. Using ODBC/JDBC: Configure data source connections

To create a database in SQL Server:

CREATE DATABASE database_name
[ON PRIMARY 
   (NAME = logical_file_name,
    FILENAME = 'os_file_name',
    SIZE = size,
    MAXSIZE = max_size,
    FILEGROWTH = growth_increment)]
[LOG ON 
   (NAME = logical_log_name,
    FILENAME = 'os_log_name',
    SIZE = log_size)];

Simple example:

CREATE DATABASE CompanyDB;
GO

-- Verify the database was created
SELECT name FROM sys.databases;
GO

-- Use the database
USE CompanyDB;
GO

Basic table creation syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    table_constraints
);

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY IDENTITY(1,1),
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    HireDate DATE NOT NULL,
    Salary DECIMAL(10,2) CHECK (Salary > 0),
    DepartmentID INT,
    CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) 
        REFERENCES Departments(DepartmentID)
);
GO

-- View table structure
EXEC sp_help 'Employees';

SQL Server supports various data types:

1. Numeric Types:
   • INT - Integer (4 bytes)
   • SMALLINT - Small integer (2 bytes)
   • BIGINT - Large integer (8 bytes)
   • DECIMAL(p,s) - Fixed-point number
   • FLOAT - Floating-point number
   • MONEY - Currency values
2. String Types:
   • CHAR(n) - Fixed-length string
   • VARCHAR(n) - Variable-length string
   • NVARCHAR(n) - Unicode variable-length string
   • TEXT - Legacy long text (avoid, use VARCHAR(MAX))
3. Date and Time Types:
   • DATE - Date only
   • TIME - Time only
   • DATETIME - Date and time (legacy)
   • DATETIME2 - More precise date and time
   • SMALLDATETIME - Less precise date and time
   • DATETIMEOFFSET - Date and time with timezone
4. Binary Types:
   • BINARY(n) - Fixed-length binary
   • VARBINARY(n) - Variable-length binary
   • IMAGE - Legacy binary large object (avoid, use VARBINARY(MAX))
5. Other Types:
   • BIT - Boolean (0 or 1)
   • UNIQUEIDENTIFIER - GUID
   • XML - XML data
   • JSON - JSON data (SQL Server 2016+)

Constraints enforce rules on data columns:

1. PRIMARY KEY - Uniquely identifies each record

   CREATE TABLE Products (
       ProductID INT PRIMARY KEY,
       ...
   );

2. FOREIGN KEY - Ensures referential integrity

   CREATE TABLE Orders (
       OrderID INT PRIMARY KEY,
       CustomerID INT,
       CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) 
           REFERENCES Customers(CustomerID)
   );

3. NOT NULL - Column cannot contain NULL values

   CREATE TABLE Employees (
       FirstName NVARCHAR(50) NOT NULL,
       ...
   );

4. UNIQUE - All values in column must be different

   CREATE TABLE Users (
       Email NVARCHAR(100) UNIQUE,
       ...
   );

5. CHECK - Ensures values meet specific conditions

   CREATE TABLE Employees (
       Salary DECIMAL(10,2) CHECK (Salary > 0),
       ...
   );

6. DEFAULT - Sets a default value for column

   CREATE TABLE Orders (
       OrderDate DATETIME DEFAULT GETDATE(),
       ...
   );

7. IDENTITY - Auto-incrementing column (SQL Server specific)

   CREATE TABLE Products (
       ProductID INT IDENTITY(1,1) PRIMARY KEY,
       ...
   );

The SELECT statement retrieves data from one or more tables:

-- Basic syntax
SELECT column1, column2, ...
FROM table_name;

Examples:

-- Select all columns from a table
SELECT * FROM Employees;

-- Select specific columns
SELECT FirstName, LastName, Salary FROM Employees;

-- Select with column aliases
SELECT FirstName AS "First Name", LastName AS "Last Name" FROM Employees;

-- Select with expressions
SELECT FirstName, LastName, Salary * 12 AS AnnualSalary FROM Employees;

-- Select distinct values
SELECT DISTINCT DepartmentID FROM Employees;

-- Select with table alias
SELECT e.FirstName, e.LastName, d.DepartmentName 
FROM Employees e 
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Select with TOP (SQL Server specific)
SELECT TOP 10 * FROM Employees ORDER BY Salary DESC;

The WHERE clause filters records based on specified conditions:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Examples:

-- Simple equality
SELECT * FROM Employees WHERE DepartmentID = 10;

-- Greater than
SELECT * FROM Employees WHERE Salary > 50000;

-- Multiple conditions with AND
SELECT * FROM Employees 
WHERE Salary > 50000 AND DepartmentID = 10;

-- Using OR
SELECT * FROM Employees 
WHERE DepartmentID = 10 OR DepartmentID = 20;

-- Using NOT
SELECT * FROM Employees 
WHERE NOT DepartmentID = 10;

-- Combining conditions with parentheses
SELECT * FROM Employees 
WHERE (DepartmentID = 10 AND Salary > 50000) 
   OR (DepartmentID = 20 AND Salary > 60000);

The ORDER BY clause sorts the result set:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Examples:

-- Sort by single column ascending (default)
SELECT * FROM Employees ORDER BY LastName;

-- Sort by single column descending
SELECT * FROM Employees ORDER BY Salary DESC;

-- Sort by multiple columns
SELECT * FROM Employees 
ORDER BY DepartmentID ASC, Salary DESC;

-- Sort by column position (not recommended)
SELECT FirstName, LastName, Salary 
FROM Employees 
ORDER BY 3 DESC; -- 3 refers to salary column

SQL Server provides several ways to limit rows:

1. TOP clause:

   -- Get first 10 records
   SELECT TOP 10 * FROM Employees;
   
   -- Get top 5 highest paid employees
   SELECT TOP 5 * FROM Employees 
   ORDER BY Salary DESC;
   
   -- With PERCENT
   SELECT TOP 10 PERCENT * FROM Employees 
   ORDER BY Salary DESC;

2. OFFSET-FETCH (SQL Server 2012+):

   -- Get records 11-20 (skip 10, take 10)
   SELECT * FROM Employees
   ORDER BY EmployeeID
   OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

The INSERT statement adds new records to a table:

-- Basic syntax
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

Examples:

-- Insert single row
INSERT INTO Employees (FirstName, LastName, HireDate, Salary)
VALUES ('John', 'Doe', '2023-01-15', 75000);

-- Insert multiple rows (SQL Server 2008+)
INSERT INTO Employees (FirstName, LastName, HireDate, Salary)
VALUES 
    ('Jane', 'Smith', '2023-02-20', 80000),
    ('Mike', 'Johnson', '2023-03-10', 65000);

-- Insert from another table
INSERT INTO EmployeeArchive (EmployeeID, FirstName, LastName)
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE HireDate < '2020-01-01';

-- Insert with OUTPUT clause (returns inserted values)
INSERT INTO Employees (FirstName, LastName, HireDate)
OUTPUT inserted.EmployeeID, inserted.FirstName
VALUES ('Sarah', 'Williams', GETDATE());

The UPDATE statement modifies existing records:

-- Basic syntax
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Examples:

-- Update single column
UPDATE Employees
SET Salary = 80000
WHERE EmployeeID = 101;

-- Update multiple columns
UPDATE Employees
SET Salary = Salary * 1.05,
    LastUpdated = GETDATE()
WHERE DepartmentID = 10;

-- Update with FROM clause (join with another table)
UPDATE e
SET e.Salary = e.Salary * 1.1
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'Engineering';

-- Update with OUTPUT clause
UPDATE Employees
SET Salary = Salary * 1.05
OUTPUT deleted.Salary AS OldSalary, inserted.Salary AS NewSalary
WHERE DepartmentID = 20;

The DELETE statement removes records from a table:

-- Basic syntax
DELETE FROM table_name
WHERE condition;

Examples:

-- Delete specific records
DELETE FROM Employees
WHERE EmployeeID = 105;

-- Delete with JOIN
DELETE e
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'HR';

-- Delete with OUTPUT clause
DELETE FROM EmployeeArchive
OUTPUT deleted.*
WHERE HireDate < '2010-01-01';

* TRUNCATE and DROP can be rolled back if performed within an explicit transaction.

IN checks if a value matches any value in a list, while NOT IN checks if a value doesn't match any value in a list:

-- IN operator
SELECT * FROM Employees
WHERE DepartmentID IN (10, 20, 30);

-- Equivalent to multiple OR conditions
SELECT * FROM Employees
WHERE DepartmentID = 10 OR DepartmentID = 20 OR DepartmentID = 30;

-- NOT IN operator
SELECT * FROM Employees
WHERE DepartmentID NOT IN (10, 20);

-- With subquery
SELECT * FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments 
                      WHERE LocationID = 1);

BETWEEN filters results within a specified range (inclusive):

-- Numeric range
SELECT * FROM Employees
WHERE Salary BETWEEN 50000 AND 80000;

-- Date range
SELECT * FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

-- Equivalent to using >= and <=
SELECT * FROM Employees
WHERE Salary >= 50000 AND Salary <= 80000;

LIKE performs pattern matching with wildcards:

• % - Matches any sequence of characters
• _ - Matches any single character
• [] - Matches any single character within the brackets
• [^] - Matches any single character not within the brackets

-- Names starting with 'J'
SELECT * FROM Employees
WHERE FirstName LIKE 'J%';

-- Names with 'oh' anywhere
SELECT * FROM Employees
WHERE FirstName LIKE '%oh%';

-- Names exactly 5 characters long
SELECT * FROM Employees
WHERE FirstName LIKE '_____';

-- Names starting with A, B, or C
SELECT * FROM Employees
WHERE LastName LIKE '[A-C]%';

-- Names not starting with A, B, or C
SELECT * FROM Employees
WHERE LastName LIKE '[^A-C]%';

Use IS NULL to find NULL values and IS NOT NULL to find non-NULL values:

-- Find employees without a manager
SELECT * FROM Employees
WHERE ManagerID IS NULL;

-- Find employees with a manager
SELECT * FROM Employees
WHERE ManagerID IS NOT NULL;

-- Common mistake (this won't work)
SELECT * FROM Employees
WHERE ManagerID = NULL; -- Incorrect!

SQL Server supports several JOIN types:

1. INNER JOIN: Returns only matching rows from both tables

   SELECT e.FirstName, e.LastName, d.DepartmentName
   FROM Employees e
   INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

2. LEFT JOIN (LEFT OUTER JOIN): Returns all rows from left table and matching rows from right table

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

3. RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from right table and matching rows from left table

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

4. FULL JOIN (FULL OUTER JOIN): Returns all rows when there's a match in either table

   SELECT e.FirstName, e.LastName, d.DepartmentName
   FROM Employees e
   FULL JOIN Departments d ON e.DepartmentID = d.DepartmentID;

5. CROSS JOIN: Produces Cartesian product (all possible combinations)

   SELECT e.FirstName, p.ProductName
   FROM Employees e
   CROSS JOIN Products p;

A self-join is when a table is joined with itself:

-- Find employees and their managers
SELECT e.FirstName AS Employee, m.FirstName AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

Example:

-- UNION (removes duplicates)
SELECT FirstName FROM Employees WHERE DepartmentID = 10
UNION
SELECT FirstName FROM Employees WHERE Salary > 70000;

-- UNION ALL (keeps duplicates)
SELECT FirstName FROM Employees WHERE DepartmentID = 10
UNION ALL
SELECT FirstName FROM Employees WHERE Salary > 70000;

Derived tables and CTEs (Common Table Expressions) are temporary result sets:

1. Derived Tables:

   SELECT d.DepartmentName, e.EmployeeCount
   FROM Departments d
   JOIN (
       SELECT DepartmentID, COUNT(*) AS EmployeeCount
       FROM Employees
       GROUP BY DepartmentID
   ) e ON d.DepartmentID = e.DepartmentID;

2. CTEs (WITH clause):

   WITH EmployeeCount AS (
       SELECT DepartmentID, COUNT(*) AS EmployeeCount
       FROM Employees
       GROUP BY DepartmentID
   )
   SELECT d.DepartmentName, e.EmployeeCount
   FROM Departments d
   JOIN EmployeeCount e ON d.DepartmentID = e.DepartmentID;

SQL Server provides several aggregate functions:

1. COUNT: Counts rows

   SELECT COUNT(*) FROM Employees;

2. SUM: Calculates total

   SELECT SUM(Salary) FROM Employees;

3. AVG: Calculates average

   SELECT AVG(Salary) FROM Employees;

4. MIN: Finds minimum value

   SELECT MIN(Salary) FROM Employees;

5. MAX: Finds maximum value

   SELECT MAX(Salary) FROM Employees;

6. STDEV: Calculates statistical standard deviation
7. VAR: Calculates statistical variance

GROUP BY groups rows that have the same values in specified columns:

-- Basic syntax
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

Examples:

-- Count employees per department
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

-- Average salary per department
SELECT d.DepartmentName, AVG(e.Salary) AS AvgSalary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentName;

-- Multiple grouping columns
SELECT DepartmentID, JobTitle, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID, JobTitle;

Examples:

-- WHERE filters rows before grouping
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
WHERE HireDate > '2020-01-01'
GROUP BY DepartmentID;

-- HAVING filters groups after grouping
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

-- Both WHERE and HAVING
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
WHERE HireDate > '2020-01-01'
GROUP BY DepartmentID
HAVING AVG(Salary) > 60000;

These are advanced grouping options in SQL Server:

1. GROUPING SETS: Specify multiple grouping sets in one query

   SELECT DepartmentID, JobTitle, COUNT(*) AS EmployeeCount
   FROM Employees
   GROUP BY GROUPING SETS (
       (DepartmentID, JobTitle),
       (DepartmentID),
       (JobTitle),
       ()
   );

2. CUBE: Generates all possible grouping sets

   SELECT DepartmentID, JobTitle, COUNT(*) AS EmployeeCount
   FROM Employees
   GROUP BY CUBE (DepartmentID, JobTitle);

3. ROLLUP: Generates hierarchical grouping sets

   SELECT Year, Quarter, Month, SUM(Sales) AS TotalSales
   FROM Sales
   GROUP BY ROLLUP (Year, Quarter, Month);

A subquery is a query nested inside another query (SELECT, INSERT, UPDATE, or DELETE). It can return:

• A single value (scalar subquery)
• Multiple values (multi-valued subquery)
• A table result set (table subquery)

Subqueries can appear in SELECT, FROM, WHERE, and HAVING clauses.

Subqueries can be categorized as:

1. Scalar Subquery: Returns exactly one value

   SELECT FirstName, LastName, 
       (SELECT AVG(Salary) FROM Employees) AS AvgSalary
   FROM Employees;

2. Multi-valued Subquery: Returns multiple values (one column)

   SELECT * FROM Employees
   WHERE DepartmentID IN (SELECT DepartmentID FROM Departments 
                         WHERE Location = 'New York');

3. Correlated Subquery: References columns from outer query

   SELECT e.FirstName, e.LastName
   FROM Employees e
   WHERE Salary > (SELECT AVG(Salary) FROM Employees 
                  WHERE DepartmentID = e.DepartmentID);

4. Table Subquery: Returns a table result set

   SELECT d.DepartmentName, e.EmployeeCount
   FROM Departments d
   JOIN (SELECT DepartmentID, COUNT(*) AS EmployeeCount
         FROM Employees
         GROUP BY DepartmentID) e ON d.DepartmentID = e.DepartmentID;

SQL Server provides several operators for subqueries:

1. IN / NOT IN: Check if value is (not) in subquery results

   SELECT * FROM Products
   WHERE ProductID IN (SELECT ProductID FROM OrderDetails);

2. EXISTS / NOT EXISTS: Check if subquery returns any rows

   SELECT * FROM Customers c
   WHERE EXISTS (SELECT 1 FROM Orders 
                 WHERE CustomerID = c.CustomerID);

3. ANY / SOME: Compare to any value in subquery

   SELECT * FROM Employees
   WHERE Salary > ANY (SELECT Salary FROM Employees 
                      WHERE DepartmentID = 10);

4. ALL: Compare to all values in subquery

   SELECT * FROM Employees
   WHERE Salary > ALL (SELECT Salary FROM Employees 
                      WHERE DepartmentID = 10);

A view is a virtual table based on the result set of a SQL query. Views:

• Don't store data themselves (except indexed views)
• Can simplify complex queries
• Provide security by restricting access to specific columns
• Can present data in a different format than underlying tables

Example:

CREATE VIEW vw_EmployeeDetails AS
SELECT e.EmployeeID, e.FirstName, e.LastName, 
       d.DepartmentName, e.Salary
FROM Employees e
JOIN Departments d ON e.DepartmentID = d.DepartmentID;

-- Use the view
SELECT * FROM vw_EmployeeDetails;

Indexes are database objects that improve query performance by providing faster data access. SQL Server supports:

1. Clustered Index:
   • Determines the physical order of data in a table
   • Only one per table (the table is the index)
   • Best for columns frequently used in range queries

   CREATE CLUSTERED INDEX IX_Employees_EmployeeID
   ON Employees(EmployeeID);

2. Nonclustered Index:
   • Separate structure from the table data
   • Can have multiple per table
   • Contains key values and pointers to data

   CREATE NONCLUSTERED INDEX IX_Employees_LastName
   ON Employees(LastName);

3. Other Index Types:
   • Filtered Index (for subset of data)
   • Columnstore Index (for data warehousing)
   • Full-text Index (for text search)

Indexing best practices:

1. Create clustered index on primary key (usually)
2. Create nonclustered indexes on columns frequently used in:
   • WHERE clauses
   • JOIN conditions
   • ORDER BY clauses
3. Avoid over-indexing (too many indexes slow down INSERT/UPDATE/DELETE)
4. Consider index maintenance (rebuild/reorganize)
5. Use the Database Engine Tuning Advisor for recommendations
6. Consider included columns to cover queries

Example with included columns:

CREATE NONCLUSTERED INDEX IX_Employees_DepartmentID
ON Employees(DepartmentID)
INCLUDE (FirstName, LastName);

SQL Server provides many string functions:

1. CONCAT: Combines strings

   SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Employees;

2. SUBSTRING: Extracts part of a string

   SELECT SUBSTRING(FirstName, 1, 3) FROM Employees;

3. LEFT/RIGHT: Extracts left/right part of string

   SELECT LEFT(FirstName, 3), RIGHT(LastName, 2) FROM Employees;

4. LEN: Returns length of string

   SELECT LEN(FirstName) FROM Employees;

5. LTRIM/RTRIM: Removes leading/trailing spaces

   SELECT LTRIM(RTRIM(FirstName)) FROM Employees;

6. REPLACE: Replaces occurrences of substring

   SELECT REPLACE(Email, '@company.com', '@newcompany.com') FROM Employees;

7. UPPER/LOWER: Changes case

   SELECT UPPER(FirstName), LOWER(LastName) FROM Employees;

SQL Server date functions:

1. GETDATE(): Current date and time

   SELECT GETDATE();

2. DATEADD: Adds interval to date

   SELECT DATEADD(DAY, 7, OrderDate) AS DueDate FROM Orders;

3. DATEDIFF: Difference between dates

   SELECT DATEDIFF(YEAR, BirthDate, GETDATE()) AS Age FROM Employees;

4. DATEPART: Extracts part of date

   SELECT DATEPART(MONTH, OrderDate) AS OrderMonth FROM Orders;

5. FORMAT: Formats date as string (SQL Server 2012+)

   SELECT FORMAT(OrderDate, 'yyyy-MM-dd') FROM Orders;

6. EOMONTH: End of month (SQL Server 2012+)

   SELECT EOMONTH(GETDATE());

SQL Server mathematical functions:

1. ROUND: Rounds number

   SELECT ROUND(Salary, 0) FROM Employees;

2. ABS: Absolute value

   SELECT ABS(-10); -- Returns 10

3. CEILING/FLOOR: Round up/down

   SELECT CEILING(4.3), FLOOR(4.9); -- Returns 5, 4

4. POWER: Raises to power

   SELECT POWER(2, 3); -- Returns 8

5. SQRT: Square root

   SELECT SQRT(16); -- Returns 4

6. RAND: Random number

   SELECT RAND(); -- Returns random float between 0 and 1

A transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure data integrity by following the ACID properties.

Example:

BEGIN TRANSACTION;
BEGIN TRY
    -- Transfer money between accounts
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
    
    -- If we get here, both updates succeeded
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Something went wrong
    ROLLBACK TRANSACTION;
    
    -- Report the error
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH

ACID stands for:

1. Atomicity: All operations in a transaction complete or none do
2. Consistency: Database remains in a consistent state before and after transaction
3. Isolation: Intermediate transaction states are invisible to other transactions
4. Durability: Committed transactions persist even after system failure

SQL Server implements ACID through:

• Transaction logging
• Locking mechanisms
• Recovery processes

SQL Server supports several isolation levels:

1. READ UNCOMMITTED:
   • Lowest isolation level
   • Allows dirty reads
   • No shared locks
2. READ COMMITTED:
   • Default level
   • Prevents dirty reads
   • Allows non-repeatable reads and phantom reads
3. REPEATABLE READ:
   • Prevents dirty reads and non-repeatable reads
   • Allows phantom reads
4. SERIALIZABLE:
   • Highest isolation level
   • Prevents dirty reads, non-repeatable reads, and phantom reads
   • Can cause blocking
5. SNAPSHOT:
   • Uses row versioning
   • Readers don't block writers and vice versa

Set isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
-- Your statements here
COMMIT TRANSACTION;

A stored procedure is a prepared SQL code that you can save and reuse. Benefits include:

• Improved performance (precompiled execution plan)
• Reduced network traffic
• Better security (granular permissions)
• Code reusability
• Easier maintenance

Example:

CREATE PROCEDURE usp_GetEmployeesByDepartment
    @DepartmentID INT,
    @MinSalary DECIMAL(10,2) = 0
AS
BEGIN
    SELECT FirstName, LastName, Salary
    FROM Employees
    WHERE DepartmentID = @DepartmentID
    AND Salary >= @MinSalary
    ORDER BY LastName, FirstName;
END;
GO

-- Execute the procedure
EXEC usp_GetEmployeesByDepartment @DepartmentID = 10, @MinSalary = 50000;

Parameters allow passing values to stored procedures:

1. Input Parameters: Pass values into the procedure

   CREATE PROCEDURE usp_AddEmployee
       @FirstName NVARCHAR(50),
       @LastName NVARCHAR(50),
       @Salary DECIMAL(10,2)
   AS
   BEGIN
       INSERT INTO Employees (FirstName, LastName, Salary)
       VALUES (@FirstName, @LastName, @Salary);
   END;

2. Output Parameters: Return values from the procedure

   CREATE PROCEDURE usp_GetEmployeeCount
       @DepartmentID INT,
       @Count INT OUTPUT
   AS
   BEGIN
       SELECT @Count = COUNT(*)
       FROM Employees
       WHERE DepartmentID = @DepartmentID;
   END;
   
   -- Execute with output parameter
   DECLARE @EmpCount INT;
   EXEC usp_GetEmployeeCount @DepartmentID = 10, @Count = @EmpCount OUTPUT;
   SELECT @EmpCount AS EmployeeCount;

3. Default Parameters: Parameters with default values

   CREATE PROCEDURE usp_GetEmployees
       @DepartmentID INT = NULL
   AS
   BEGIN
       IF @DepartmentID IS NULL
           SELECT * FROM Employees;
       ELSE
           SELECT * FROM Employees WHERE DepartmentID = @DepartmentID;
   END;
   
   -- Can be called with or without parameter
   EXEC usp_GetEmployees;
   EXEC usp_GetEmployees @DepartmentID = 10;

Both are used to store temporary result sets:

Example:

-- Table variable
DECLARE @EmployeeTable TABLE (
    EmployeeID INT,
    FirstName NVARCHAR(50)
);

-- Temporary table
CREATE TABLE #EmployeeTemp (
    EmployeeID INT,
    FirstName NVARCHAR(50)
);

Window functions perform calculations across a set of table rows related to the current row:

1. Ranking Functions:

   -- ROW_NUMBER, RANK, DENSE_RANK, NTILE
   SELECT 
       FirstName, LastName, Salary,
       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
       RANK() OVER (ORDER BY Salary DESC) AS Rank,
       DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
   FROM Employees;

2. Aggregate Functions with OVER:

   -- Running totals, moving averages
   SELECT 
       OrderID, OrderDate, Amount,
       SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal,
       AVG(Amount) OVER (PARTITION BY CustomerID) AS AvgPerCustomer
   FROM Orders;

3. Analytic Functions:

   -- LAG, LEAD, FIRST_VALUE, LAST_VALUE
   SELECT 
       EmployeeID, OrderDate, Amount,
       LAG(Amount, 1) OVER (PARTITION BY EmployeeID ORDER BY OrderDate) AS PrevAmount,
       LEAD(Amount, 1) OVER (PARTITION BY EmployeeID ORDER BY OrderDate) AS NextAmount
   FROM Orders;

PIVOT and UNPIVOT are operators for transforming data:

1. PIVOT: Rotates rows into columns

   -- Pivot sales data by year
   SELECT ProductID, [2019], [2020], [2021]
   FROM (
       SELECT ProductID, YEAR(OrderDate) AS OrderYear, Amount
       FROM Sales
   ) AS SourceTable
   PIVOT (
       SUM(Amount)
       FOR OrderYear IN ([2019], [2020], [2021])
   ) AS PivotTable;

2. UNPIVOT: Rotates columns into rows

   -- Unpivot quarterly sales data
   SELECT ProductID, Quarter, Amount
   FROM (
       SELECT ProductID, Q1, Q2, Q3, Q4
       FROM QuarterlySales
   ) AS SourceTable
   UNPIVOT (
       Amount FOR Quarter IN (Q1, Q2, Q3, Q4)
   ) AS UnpivotTable;