SQL Server Interview Tricks & Tips

Essential techniques to ace your SQL Server interviews and improve database performance

These tricks will help you stand out in interviews by showcasing deeper understanding of SQL Server concepts.

Installation & Setup Tricks

Use Docker for Quick Setup
Docker Setup

Run SQL Server without system changes using Docker:

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourStrong@Passw0rd" -p 1433:1433 -d mcr.microsoft.com/mssql/server:latest

Pro Tip: Always change the default SA password after installation.

Secure SQL Server After Installation
Security Essential

Basic security hardening steps:

-- Change SA password ALTER LOGIN sa WITH PASSWORD = 'new_strong_password'; -- Disable SA account (best practice) ALTER LOGIN sa DISABLE;

Database & Table Tricks

Clone Table Structure
Schema

Create an empty table with the same structure:

SELECT * INTO new_table FROM original_table WHERE 1 = 0;
Efficient Data Insertion
Performance Optimization

Insert data from another table with conditions:

INSERT INTO target_table (col1, col2) SELECT col1, col2 FROM source_table WHERE condition;

Query Optimization Tricks

Use Execution Plans to Debug Queries
Optimization Debugging

Analyze query execution plan:

SET SHOWPLAN_TEXT ON; GO SELECT * FROM users WHERE age > 25; GO SET SHOWPLAN_TEXT OFF;

Key things to check: scan vs seek operations, estimated vs actual rows

Force Index Usage
Indexing Advanced

When the optimizer doesn't choose the best index:

SELECT * FROM users WITH (INDEX(age_index)) WHERE age > 25;

JOINs & Subqueries Tricks

Replace NOT IN with LEFT JOIN
Performance Best Practice

More efficient than NOT IN for large datasets:

SELECT a.* FROM table_a a LEFT JOIN table_b b ON a.id = b.id WHERE b.id IS NULL;
Use EXISTS Instead of IN
Optimization Best Practice

Better performance for large subqueries:

SELECT * FROM orders o WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.id = o.customer_id );

Aggregation Tricks

Top N Rows Per Group
Advanced Complex Query

Using window functions (SQL Server 2005+):

WITH RankedProducts AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn FROM products ) SELECT * FROM RankedProducts WHERE rn <= 3; -- Top 3 products per category
Running Totals
Window Functions Modern SQL

Using window functions for cumulative sums:

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total FROM sales;

Date & Time Tricks

Last 30 Days Records
Date Functions Common Task

Find records from the last 30 days:

SELECT * FROM orders WHERE order_date >= DATEADD(day, -30, GETDATE());
Group By Time Periods
Aggregation Reporting

Group by week/month/year easily:

SELECT YEAR(order_date) AS year, MONTH(order_date) AS month, COUNT(*) AS total_orders FROM orders GROUP BY YEAR(order_date), MONTH(order_date) ORDER BY year, month;

Indexing Tricks

Find Unused Indexes
Performance Maintenance

Identify indexes that aren't being used:

SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name, i.type_desc FROM sys.indexes i LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id AND s.index_id = i.index_id WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.name IS NOT NULL AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0;
Covering Indexes
Optimization Best Practice

Create indexes that satisfy queries without table access:

CREATE INDEX idx_covering ON users(name, email); -- Query uses index only: SELECT name, email FROM users WHERE name LIKE 'A%';

Transaction Tricks

Test Before Committing
Safety Essential

Test queries in a transaction before committing:

BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- Check for errors, then: COMMIT TRANSACTION; -- or ROLLBACK TRANSACTION if issues
Set Isolation Level
Concurrency Advanced

Control how transactions interact with each other:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Other levels: READ UNCOMMITTED, REPEATABLE READ, SERIALIZABLE, SNAPSHOT

Security Tricks

Audit User Privileges
Security Essential

Check what permissions a user has:

EXEC sp_helprotect @username = 'username';
Prevent SQL Injection
Best Practice Critical

Always use parameterized queries in applications:

-- C# Example string sql = "SELECT * FROM users WHERE email = @email"; SqlCommand cmd = new SqlCommand(sql, connection); cmd.Parameters.AddWithValue("@email", userInput);

Never concatenate user input directly into SQL queries!

Backup & Recovery Tricks

Single Table Backup
Maintenance Common Task

Backup just one table using SELECT INTO:

SELECT * INTO backup_table FROM original_table;
Schema-Only Backup
Structure Migration

Generate scripts for database schema:

-- In SSMS: Right-click database > Tasks > Generate Scripts -- Select "Script only schema" option

Bonus: Common Interview Questions

SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users ) DELETE FROM CTE WHERE rn > 1;

  • DELETE: Removes rows (can rollback, logs each row, can have WHERE clause)
  • TRUNCATE: Faster, resets identity (cannot rollback, no WHERE clause)
  • DROP: Deletes entire table structure

Final Interview Tips

  • Know the difference between CHAR vs VARCHAR vs NVARCHAR
  • Explain indexing trade-offs (clustered vs non-clustered)
  • Mention execution plans for query analysis
  • Discuss transaction isolation levels and their impact

More SQL Server Resources

T-SQL Basics

Learn fundamental T-SQL concepts and commands

View Notes
SQL Server MCQs

Test your knowledge with practice questions

Practice MCQs
SQL Server CERTIFICATIONS

Learn details of SQL Server database certifications

SQL Server certifications