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.

HardSystem Design

66 practiced

Your analytics team needs to run heavy joins across on-prem OLTP SQL Server data and large data in cloud blob storage. Compare strategies: PolyBase/external tables, Elastic Query, and full ETL into Synapse/Azure SQL Data Warehouse. For each approach, list pros/cons around freshness, performance, cost, security, and operational complexity.

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.

HardTechnical

108 practiced

A compliance requirement mandates that PII columns must be encrypted at rest and application code should not have access to plaintext for most services, but analytics still requires equality searches on some pseudonymized fields. Compare TDE and Always Encrypted for this scenario, discuss key management (BYOK), performance impacts, and how to enable searchable encrypted columns safely.

EasyTechnical

77 practiced

Describe SQL Server transaction isolation levels and their trade-offs. State the default isolation behavior in a modern SQL Server instance, explain 'READ COMMITTED' and 'SNAPSHOT' isolation, and give examples of concurrency anomalies (dirty reads, nonrepeatable reads, phantoms) that each level prevents or allows.

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.