🚀 5 Advanced SQL Interview Questions on Estimation, XML Indexes & Compression! #SQL #AdvancedSQL

Prepare to impress your interviewers with deep insights into SQL Server’s internal engines and optimization features!

✅ 1. How Does the Cardinality Estimator Work, and How Can Trace Flags Influence It?

Answer: The cardinality estimator predicts the number of rows returned at each step in a query plan. Accurate estimates allow the optimizer to pick efficient join strategies and memory grants. SQL Server 2014 introduced a new estimator that uses improved histogram sampling and correlation estimates. You can force the legacy behavior or tweak the new estimator via trace flags—for example,

DBCC TRACEON(9481); -- Use pre-2014 CE logic
DBCC TRACEOFF(9481); -- Switch back to the modern estimator
Use trace flags 2312 and 2314 to control specific skewed data scenarios, helping avoid misestimates that lead to suboptimal plans.

✅ 2. Best Practices for Efficient Cross-Database Queries

Answer: When querying across databases on the same server, always use four-part naming (Server.Database.Schema.Object) or USE statements to reduce ambiguity. Avoid SELECT * and explicitly list only needed columns. Minimize cross-database joins when possible—staging data in a temp table can improve performance. Secure cross-database access by granting only the necessary permissions and using contained database users rather than dbo-linked logins.

✅ 3. Creating and Using a Persisted XML Index

Answer: For large XML documents stored in an XML column, you can improve query speed by adding a primary XML index, then optional secondary indexes. Persisted XML indexes physically store shredded XML in internal tables. Example:

-- Primary XML index
CREATE PRIMARY XML INDEX PIdx_Orders_Details
ON Orders (OrderDetails);

-- Secondary XML PATH index for faster PATH() queries

CREATE XML INDEX SecIdx_Orders_Details_Path
ON Orders (OrderDetails)
USING XML INDEX PIdx_Orders_Details FOR PATH;
These indexes optimize common XQuery operations like .value() and .nodes() by precomputing paths and values.

✅ 4. Difference Between ROW and PAGE Compression

Answer:

ROW Compression stores fixed-length data types in variable-length format, saving space on small values.

PAGE Compression extends row compression by also eliminating repeated byte patterns across rows on the same page.
Use ROW compression for moderate space savings with lower CPU overhead, and PAGE compression when disk I/O is the bottleneck and higher CPU usage is acceptable. Example:

-- Enable PAGE compression on a table
ALTER TABLE SalesOrders
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

✅ 5. Clearing or Forcing a Different Execution Plan

Answer: When parameter sniffing causes poor plan reuse, you can:

Use OPTION (RECOMPILE) in your query to generate a fresh plan each execution.

Clear the plan cache for a specific object:

DBCC FREEPROCCACHE (plan_handle);
Employ a plan guide or USE PLAN hint to force a known-good plan:

-- Example plan guide creation (conceptual)

EXEC sp_plan_guide
@name = N'ForceGoodPlan',
@stmt = N'SELECT * FROM Orders WHERE CustomerID = @cust',
@type = N'SQL',
@module_or_batch = NULL,
@hints = N'OPTION (USE PLAN N''vshowplan xmlns=...v'')';

#SQL #AdvancedSQL #SQLInterview #DatabasePerformance #XMLIndex #DataCompression #QueryOptimization #ParameterSniffing #TechInterview #LearnSQL