31. What are database statistics?
Answer: Information about data distribution that helps the optimizer estimate how many rows a query will return.
32. What happens when statistics are outdated?
The optimizer may estimate row counts incorrectly and choose a poor execution plan.
33. What is Cardinality?
Answer: The number of distinct values or, depending on context, the number of rows represented by a relation/result.
34. What is Cardinality Estimation?
Answer: The optimizer's estimate of how many rows each operation will produce.
35. What is a Cost-Based Optimizer?
Answer: It evaluates possible execution strategies and chooses what it estimates to be the lowest-cost plan.
CTE, Subqueries & Temporary Objects
36. Is a CTE always faster than a subquery?
Answer: No. A CTE is primarily a query-organization feature; performance depends on the resulting execution plan.
37. Is a temporary table always faster than a CTE?
Answer: No. It depends on the workload, database engine, indexes, intermediate result size, and reuse.
38. When would you use a temporary table?
Useful when you need to:
- Materialize intermediate results
- Reuse results
- Index intermediate data
- Break a complex operation into stages
39. Temp Table vs Table Variable?
In SQL Server, the choice depends on data volume and workload. Temporary tables generally provide richer statistics and indexing options, while table variables can be suitable for smaller datasets.
No comments:
Post a Comment