🔥 5 Advanced SQL Interview Questions on Optimization & Functions!
🚀 Elevate your SQL expertise with these advanced interview questions that dive into optimization and advanced functions!
✅ 1. LATERAL JOIN vs. CROSS APPLY:
LATERAL JOIN: Enables a subquery in the FROM clause to reference columns from preceding tables. Supported in PostgreSQL and SQL Server (using CROSS APPLY).
CROSS APPLY: In SQL Server, it performs a similar role to LATERAL JOIN by applying a table-valued function to each row of an outer table.
Example:
-- PostgreSQL Example:
SELECT a.id, b.value
FROM TableA a,
LATERAL (SELECT value FROM TableB WHERE TableB.ref_id = a.id) b;
✅ 2. SARGability:
SARGable (Search Argument Able): Refers to a query's ability to efficiently utilize indexes.
Non-SARGable queries (e.g., using functions on indexed columns) force full table scans, degrading performance.
Tip: Write queries that allow the optimizer to use indexes directly.
✅ 3. Inline vs. Multi-Statement Table-Valued Functions:
Inline Table-Valued Functions: Act like parameterized views. They contain a single SELECT statement and are generally more efficient.
Multi-Statement Table-Valued Functions: Allow multiple statements to build a table variable, but may lead to poorer performance due to lack of optimization and statistics.
Example (Inline):
CREATE FUNCTION dbo.GetEmployeeByDept(@DeptID INT)
RETURNS TABLE
AS
RETURN (SELECT * FROM Employees WHERE department_id = @DeptID);
✅ 4. Computed Columns:
Computed Columns: Columns whose values are calculated from other columns in the same table.
Benefits: Simplify queries by encapsulating logic within the table schema; can be persisted to improve performance.
Example:
ALTER TABLE Orders
ADD TotalPrice AS (Quantity * UnitPrice) PERSISTED;
✅ 5. Query Hints:
Query Hints: Directives used to influence the SQL optimizer's execution plan (e.g., forcing an index or join order).
Usage: Should be applied sparingly after thorough testing, as they can affect long-term performance and maintenance.
Example (SQL Server):
SELECT * FROM Employees WITH (INDEX(idx_employee_name))
WHERE name LIKE 'A%';
💡 Master these advanced SQL concepts to stand out in your next interview!
💬 Have questions or need further clarifications? Drop your queries in the comments!
#SQLInterview #AdvancedSQL #QueryOptimization #TechInterview #SQLTips