What is an index in SQL?
An index is a database object that improves query performance by allowing faster lookups of rows, avoiding full table scans.
What is a clustered index?
A clustered index defines the physical order of rows in the table. Only one clustered index can exist per table.
What is the cost of a clustered index?
Updating a clustered index can be expensive because the table data itself must be reordered.
What is a non-clustered index?
A separate structure that stores indexed column values and pointers (row locators) to the actual table data.
Does a non-clustered index keep a copy of the data?
No, it keeps the indexed column plus a pointer to the actual row.
If you create a non-clustered index on salary in Employees (1000 rows, 100 distinct salaries), what does it store?
The index will store all salary values (including duplicates), each linked to row pointers, not just distinct values.
What is a full table scan?
Scanning all rows in a table to execute a query, usually happening when no suitable index exists.
What is an inner join?
Returns rows that have matching values in both tables.
What is a left join?
Returns all rows from the left table, plus matching rows from the right table (or NULL if no match).
What is a full outer join?
Returns all rows from both tables, matching where possible, NULL where not.
What is a Cartesian product in SQL?
When two tables are joined without a condition (or with CROSS JOIN), producing every combination of rows.
How do you find the second highest salary in SQL using TOP?
SELECT TOP 1 Salary
FROM Employees
WHERE Salary NOT IN (
SELECT TOP 1 Salary FROM Employees ORDER BY Salary DESC
)
ORDER BY Salary DESC;
How do you find the second highest salary in SQL using MAX?
SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
What is a transaction in SQL?
A sequence of SQL operations executed as a single unit that ensures ACID (Atomicity, Consistency, Isolation, Durability).
What are isolation levels in SQL?
They define how transactions interact with each other. Levels:
- Read Uncommitted → allows dirty reads.
- Read Committed → prevents dirty reads (default in SQL Server).
- Repeatable Read → prevents dirty and non-repeatable reads.
- Serializable → prevents dirty, non-repeatable, and phantom reads.
How to set isolation levels?
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
When a row is locked, can it still be read?
Under Read Committed, other transactions must wait until the lock is released.
Under Read Uncommitted, the row can still be read (dirty read).
What is a stored procedure?
A precompiled set of SQL statements stored in the database, used to encapsulate logic and reuse code.
Benefits of stored procedures?
Performance (precompiled), reusability, security, maintainability.
Downsides of stored procedures?
Can become complex, harder to version-control, not portable across DBMS, business logic tightly coupled to DB.
What is a trigger?
A special stored procedure that automatically executes in response to an event (INSERT, UPDATE, DELETE) on a table.
When to use triggers?
For enforcing business rules, maintaining audit logs, or automatically updating related data.
