SQL Server Questions

SQL Server relational database management system (RDBMS); covers installation and configuration, T-SQL programming, indexing and query optimization, data modeling, data types, transaction handling, backup and recovery, replication, high availability (Always On), security, maintenance, and administration tasks specific to SQL Server.

MediumTechnical

79 practiced

You manage a 500GB OLTP table that shows average fragmentation > 30%. Describe how you would decide between REORGANIZE and REBUILD, including thresholds, online/offline options, impact on locks, and how fillfactor should be considered. Provide a maintenance approach that minimizes user impact.

EasyTechnical

73 practiced

Explain the difference between a PRIMARY KEY and a UNIQUE constraint in SQL Server. Can a UNIQUE constraint allow NULL values? Describe how NULLs are treated with unique constraints and practical implications for data modeling.

HardTechnical

81 practiced

A stored procedure performs poorly for some input parameters but is fast for others. Explain parameter sniffing, how to diagnose it using cached plan information and DMVs, and enumerate strategies to mitigate it (e.g., OPTION(RECOMPILE), OPTIMIZE FOR, local variable tricks, plan guides). Discuss the trade-offs of each approach.

Sample Answer

**Situation (brief)**Parameter sniffing: optimizer uses first-call parameter values to build a plan that may be suboptimal for other parameter values.

**Diagnose using cached plan info and DMVs**- Query sys.dm_exec_cached_plans, sys.dm_exec_query_stats, and sys.dm_exec_sql_text/sys.dm_exec_query_plan to find hot plans. Look for large differences in average vs last compile costs, and high compilation count. Use sys.dm_db_index_usage_stats and sys.dm_exec_query_plan to see estimated vs actual rows mismatches.

**Mitigation strategies (with trade-offs)**- OPTION (RECOMPILE): forces plan per execution; eliminates sniffing but increases CPU due to recompiles. Use for infrequent or parameter-sensitive calls.- OPTIMIZE FOR (specific value or UNKNOWN): locks plan to a representative value or uses average cardinality. Reduces surprises but may be suboptimal for outliers.- Local variable trick: assign parameter to local variable to hide value from optimizer; results in generic plan but may underutilize statistics—less CPU on compilations but risk of poor plans.- OPTIMIZE FOR UNKNOWN + OPTIMIZE FOR (@p = <value>): choose when representative value exists.- Plan guides / forced plan: enforce a known-good plan; brittle if schema/ stats change; useful for critical SPs.- Use parameterization changes: change from simple to forced parameterization or rewrite queries to give stable cardinality.- Statistics management: create filtered statistics or increase sample size to better represent data distribution; use ascending keys or histograms.- Query store: force last-known-good plan or track regressions. Good for production stability.

**Recommendation for Data Engineer**- Start with identifying problematic SPs via DMVs and Query Store. Prefer Query Store forced plans or OPTION(RECOMPILE) for selective calls. Use OPTIMIZE FOR with representative values for common patterns. Combine improved statistics and targeted plan guides for long-lived critical procedures; measure CPU and latency trade-offs after change.

MediumTechnical

60 practiced

Write a T-SQL script that removes duplicate rows from a table called 'orders' where duplicates are defined by the combination (customer_id, order_date). Keep the row with the highest order_id and delete other duplicates in a way that minimizes transaction log and blocking.

Sample Answer

**Goal**: Remove duplicates keeping highest order_id, minimizing log growth and blocking.

**Approach**: Delete in small batches using a windowed CTE to identify duplicates and delete per batch.

Sample T-SQL:

sql

-- delete duplicates in batches
WHILE 1=1
BEGIN
  WITH cte AS (
    SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id, order_date ORDER BY order_id DESC) rn
    FROM dbo.orders
  )
  DELETE TOP (10000) o
  FROM dbo.orders o
  JOIN cte ON o.order_id = cte.order_id
  WHERE cte.rn > 1;
  IF @@ROWCOUNT = 0 BREAK;
  WAITFOR DELAY '00:00:01'; -- small throttle
END

**Reasoning**:- ROW_NUMBER identifies duplicates; keeping highest order_id by ORDER BY DESC.- Batch deletes keep transactions small, reduce log pressure and locks.- Optionally disable nonclustered indexes before large deletes and rebuild after if deletion is extensive.

**Edge considerations**: Ensure foreign keys allow deletes (cascade or remove children), ensure backups exist before large deletes.

MediumTechnical

86 practiced

You need to implement a sliding window purge for a very large partitioned table: new data is arriving daily and older data (> 90 days) must be removed with minimal blocking. Describe the steps in SQL Server to partition the table by date, load new data, and remove old partitions using partition switching. Include index and constraint considerations.

Unlock Full Question Bank

Get access to hundreds of SQL Server interview questions and detailed answers.

Join thousands of developers preparing for their dream job.