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;

Use IS NULL. With SET ANSI_NULLS ON (default in modern clients), equality to NULL is UNKNOWN. Historically ANSI_NULLS OFF allowed non-standard = NULL comparisons—still a trap in legacy scripts and in interviews about backward compatibility.

ISNULL returns the data type of the first argument with the second promoted to that type (narrowing surprises). COALESCE follows CASE expression typing rules and can return a wider type. Interviewers use this to test implicit conversion awareness.

No. If the subquery yields any NULL, NOT IN can reduce to UNKNOWN for some outer rows—often returning no rows. Prefer NOT EXISTS, which is NULL-safe and usually semijoin-friendly for the optimizer.

-- Prefer
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM blocked b WHERE b.id = c.id);

It is equivalent to READ UNCOMMITTED: dirty reads, non-repeatable reads, phantom rows, and read skew (including missing/duplicate rows during allocation changes). It does not “take no locks”—schema stability locks still apply. Good answer: use RCSI (READ_COMMITTED_SNAPSHOT) for many reporting patterns instead of blanket NOLOCK.

RCSI versions statements under default read committed using row versions. SNAPSHOT isolation gives a transaction-wide consistent view as of start time and can fail with update conflicts on writers. Both use tempdb version store—interviewers probe tempdb growth and long transactions.

Implicit conversion often converts the column side, destroying sargability. Fix with explicit CAST/CONVERT on the parameter side, correct column data types, or a computed persisted column plus index. Pair with “check actual execution plan warnings.”

Leading-wildcard patterns normally prevent seeks on classic B-trees, but full-text, columnstore scans, or selective filters on other indexed columns can still make the plan efficient. Trick answer path: mention LIKE with Unicode literal vs non-Unicode column causing conversion on the column side.

CROSS APPLY can invoke a table-valued function per outer row or use inner-side predicates that correlate—optimizer rules differ from a flat join on pre-materialized sets. OUTER APPLY preserves outer rows when the inner returns empty—similar to LEFT JOIN but with TVF/chaining idioms.

Classic scalar UDFs were notorious for per-row overhead, lack of inlining, and inhibiting parallelism (behavior improved across versions with inlined UDFs). Interview answer: prefer inline TVFs or set-based expressions; measure with actual plans and STATISTICS IO/TIME.

Historically fixed low cardinality estimates without statistics led to nested loop plans and spills. SQL Server 2019+ can defer compilation or use sniffing in some cases, but the rule of thumb remains: use #temp tables for larger intermediate sets and when you need indexes or accurate histograms.

TRUNCATE is a minimally logged DDL-style page deallocation (subject to restrictions: FKs pointing to the table, published replication edge cases, permissions). DELETE is row-level, fully logged per row (simplified), fires triggers, and respects WHERE. Good tricky follow-up: permissions (ALTER TABLE vs DELETE).

No—rolled-back transactions, failed inserts, replication, and identity cache (especially on restarts with certain configurations) create gaps. Use sequences or external idempotency keys when the business needs human-auditable monotonicity.

Advanced panels probe engine internals: deletes leave ghost records until cleanup; allocation-order scans can speed reads but change consistency interactions with other operators. Shows you read execution plans beyond the shape of nested loops vs hash joins.

Ranking functions don’t use a frame the same way aggregates do; for aggregates, the default frame often excludes peers unless you use RANGE/ROWS explicitly—classic bug when computing running totals with ties on the ordering key.

Without parentheses, older parsers could confuse constants vs variables; with variables you use TOP (@n). Pair with ORDER BY requirement for deterministic “top” and with OFFSET/FETCH as ANSI paging alternative.