21. What is data partitioning?
Splitting large tables into smaller, manageable pieces (partitions) based on a key like date or region, improving query performance and maintenance.
22. How do you find duplicates in a table?
Use GROUP BY with HAVING:
SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > 1;
23. What is the difference between DELETE and TRUNCATE?
⦁ DELETE removes rows one by one, can have WHERE clause, logs each row, slower.
⦁ TRUNCATE removes all rows instantly, no WHERE, resets identity, faster but less flexible.
24. Explain window functions with examples.
Window functions perform calculations across sets of rows related to the current row without collapsing results. Example:
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
25. What is the difference between correlated and non-correlated subqueries?
⦁ Correlated subqueries depend on the outer query and execute for each row.
⦁ Non-correlated subqueries run independently once.
26. How do you enforce data integrity?
Using constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL), triggers, and transactions.
27. What are CTEs (Common Table Expressions)?
Temporary named result sets within SQL statements to improve query readability and recursion:
WITH cte AS (SELECT * FROM employees WHERE salary > 5000)
SELECT * FROM cte;
28. Explain EXISTS and NOT EXISTS operators.
⦁ EXISTS returns TRUE if a subquery returns any rows.
⦁ NOT EXISTS returns TRUE if subquery returns no rows.
29. How do SQL constraints work?
Constraints enforce rules at the database level to ensure data validity and integrity during insert/update/delete operations.
30. What is an execution plan? How do you use it?
A detailed roadmap of how SQL Server executes a query. Used to analyze and optimize query performance by revealing bottlenecks.
React ♥️ for Part 4