COMMIT waits only for the transaction log to reach disk. Under the FULL recovery model, only a log backup frees log space.Asked in interviews
AskedWhat is SQL Server and what are its main components?Price DECIMAL(10,2)FLOAT is approximate.CreatedAt DATETIME2DATETIME rounds to about 3 milliseconds.Name NVARCHAR(100)Most characters take 2 bytes, where VARCHAR takes 1.NVARCHAR(MAX)VARBINARY(MAX)They replace the deprecated TEXT and IMAGE.TipFrom 2019, a UTF-8 collation lets VARCHAR hold Unicode. SQL Server 2025 adds a JSON type.
TrapWithout the N in N'...', Unicode literals can lose characters.
ID INT PRIMARY KEYIt's clustered by default.REFERENCES Departments(ID) ON DELETE CASCADEWithout CASCADE, deleting a parent with children fails.IDENTITY(1,1)SCOPE_IDENTITY() Avoid @@IDENTITY. A trigger's insert can change it.TipBoyce-Codd normal form (BCNF) is stricter. Any column that decides another must be a candidate key.
DELETE FROM Orders WHERE ID = 1;It logs each row and fires triggers.TRUNCATE TABLE Orders;It logs only freed pages and resets the identity.DROP TABLE Orders;TipInside a transaction, ROLLBACK undoes TRUNCATE and DROP too.
TrapTRUNCATE fails on a table that a foreign key references.
Asked in interviews
AskedWhat is the difference between DELETE, TRUNCATE, and DROP?CREATE CLUSTERED INDEX cx ON Orders (ID);A table has one at most. Without one, it's a heap.CREATE INDEX ix ON Orders (CustomerID);It's nonclustered by default, and it slows every write.INCLUDE (Total)TipKeep the clustering key narrow. Every other index stores a copy.
TrapKey order is logical, not physical, so pages fragment over time.
CREATE CLUSTERED COLUMNSTORE INDEXQueries read only the columns they need.NONCLUSTERED COLUMNSTORETipColumns compress up to ten times, and batch mode reads many rows at once.
TrapIt suits big scans, not single-row lookups or frequent updates.
Asked in interviews
AskedExplain columnstore indexes and their use cases.sys.dm_db_index_physical_stats ALTER INDEX ALL ON Orders REORGANIZE;It's always online, but leaves statistics alone.REBUILD WITH (ONLINE = ON)An offline rebuild blocks the table.TipThe old rule says reorganize at 5 to 30 percent and rebuild above 30.
TrapA rebuild often helps only because it updates statistics. UPDATE STATISTICS is cheaper.
Asked in interviews
AskedHow do you manage index fragmentation in SQL Server?INNER JOINLEFT JOINFULL OUTER JOINA side with no match comes back as NULL.CROSS APPLYOUTER APPLYOUTER APPLY keeps rows with no result.UNIONUNION ALLUNION drops duplicates, so it's slower.MERGE t USING s ON t.ID = s.IDWHEN NOT MATCHED BY SOURCE THEN DELETETrapConcurrent MERGE upserts need WITH (HOLDLOCK) on the target.
WITH cte AS (...)Each reference runs it again, because it isn't stored.OPTION (MAXRECURSION 0)ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Pay)RANK leaves gaps after ties. DENSE_RANK doesn't.ROWS UNBOUNDED PRECEDINGTipThe statement before WITH must end with a semicolon.
TrapThe default frame is RANGE, so tied rows share one total.
CREATE TABLE #t (ID INT);Hold a large set you join or index againDECLARE @t TABLE (ID INT);Hold a small set, or one inside a functionCREATE TABLE ##t (ID INT);Share a temp table with every sessionTipBoth live in tempdb. A table variable isn't memory-only.
TrapThe optimizer guesses 1 row for a table variable before 2019.
Asked in interviews
AskedWhat is the difference between a temporary table and a table variable?CREATE VIEW Totals ASIt stores no data.WITH SCHEMABINDINGCREATE UNIQUE CLUSTERED INDEXEXEC dbo.CountOrders 42, @n OUTPUT;Callers need only EXECUTE.CREATE TRIGGER t ON Orders AFTER UPDATEIt fires once per statement. Join inserted to deleted.INSTEAD OF INSERTViews allow only this kind.TipDDL triggers fire on schema changes, and logon triggers as a session starts.
TrapDon't name procedures sp_.... SQL Server checks master first.
SELECT dbo.CalcAge(BirthDate) It runs once per row. SQL Server 2019 can inline it.SELECTRETURNS TABLE AS RETURNSQL Server optimizes it like a view.SELECT and can't change data. A procedure can.TrapA function can't change data or use TRY...CATCH.
CATCHERROR_NUMBER()ERROR_MESSAGE()Both are NULL outside CATCH.TipUse THROW, not RAISERROR. Only THROW honors XACT_ABORT.
TrapA CATCH block misses compile errors from its own level.
Asked in interviews
AskedHow do you handle errors in a T-SQL stored procedure?EXEC sp_executesql @sql, N'@id INT', @id = 7;It reuses one plan, and values can't inject SQL.QUOTENAME(@table)Names can't be parameters, so quote them.TipPlain EXEC compiles each new string and risks injection.
Trapsp_executesql needs an NVARCHAR string.
Asked in interviews
AskedWhat is the difference between EXEC and sp_executesql for dynamic SQL?READ UNCOMMITTEDRead uncommitted changesREAD COMMITTEDUse the default, with no dirty readsREPEATABLE READKeep the rows you read from changingSERIALIZABLEAlso block inserts into ranges you readSNAPSHOTRead data as of the transaction's startTrapNOLOCK is READ UNCOMMITTED. It can read rows that later roll back.
Asked in interviews
AskedExplain transaction isolation levels in SQL Server.v1 is the row before the UPDATE, v2 after it. A locking READ COMMITTED (RC) reader waits for the COMMIT. RCSI never waits, and SNAPSHOT keeps v1.
SET READ_COMMITTED_SNAPSHOT ONDefault reads then use row versions.SET ALLOW_SNAPSHOT_ISOLATION ONThen each transaction opts in.TrapUnder SNAPSHOT, an update conflict fails with error 3960.
SET (LOCK_ESCALATION = AUTO)Escalation starts at 5,000 locks.OPTIMIZE_FOR_SEQUENTIAL_KEY = ONThe sign is PAGELATCH_EX waits.TrapEscalation takes one table lock, which can block everyone.
sys.dm_exec_requests Read its blocking_session_id column.TipThe system_health session already records every deadlock graph.
TrapBlocking clears by itself. A deadlock needs SQL Server to roll back a victim.
Asked in interviews
AskedWhat is deadlock and how can it be prevented?sys.dm_exec_query_stats Sort by reads or CPU.SET STATISTICS IO ON;SET STATISTICS TIME ON;UPDATE STATISTICS Orders WITH FULLSCAN;TrapSQL Server updates statistics only after enough row changes. New ascending keys fall past the histogram.
OPTION (RECOMPILE)Each run compiles again.OPTIMIZE FOR (@id = 7)OPTIMIZE FOR (@id UNKNOWN)A local variable acts like UNKNOWN.WITH (INDEX(ix))WITH (FORCESEEK)TrapA join hint like INNER HASH JOIN also fixes the join order.
SET QUERY_STORE = ONIt's on by default from 2022.EXEC sp_query_store_force_plan 48, 49;sys.sp_query_store_set_hints TrapA forced plan can go stale as the data changes.
Asked in interviews
AskedWhat is Query Store and how does it help with performance tuning?sys.dm_os_wait_stats Sort by wait_time_ms, and skip idle waits.PAGEIOLATCH_SHLook for scans or slow disks.WRITELOGCheck the log disk.CREATE EVENT SESSION Mon ON SERVERProfiler and SQL Trace are deprecated.CREATE RESOURCE POOL ReportsA classifier function sends sessions to it.TrapThe wait totals count from the last restart. Compare two snapshots.
tempdb.sys.dm_db_file_space_usage TrapIf the shared tempdb fills, the whole workload stops.
Asked in interviews
AskedWhat is tempdb used for, and how do you configure it?ADD FILEGROUP ArchiveAdd an .ndf file, then create tables ON Archive.MODIFY FILEGROUP Archive READ_ONLYPartial backups then skip it.RANGE RIGHT FOR VALUES ('2025-01-01')One boundary makes two partitions.SWITCH PARTITION 1 TO ArchiveTRUNCATE TABLE Sales WITH (PARTITIONS (1));TipWithout the partition column, a lookup reads every partition.
TrapSWITCH fails unless indexes share the partition scheme.
REBUILD WITH (DATA_COMPRESSION = PAGE)Rebuild each nonclustered index too.MEMORY_OPTIMIZED = ONIt needs a memory-optimized filegroup.DURABILITY = SCHEMA_ONLYEvery restart empties the table.TipCompression trades CPU for I/O. ROW is lighter than PAGE.
TrapMemory-optimized tables must fit in memory.
SET RECOVERY FULLYou must take log backups.SET RECOVERY SIMPLEYou restore only to the end of a backup.SET RECOVERY BULK_LOGGEDYou lose point-in-time restore around bulk loads.TABLOCK, and a table that isn't replicated.TrapUnder FULL, the log keeps growing until a log backup runs.
BACKUP DATABASE Sales TO DISK = 'full.bak';WITH DIFFERENTIALBACKUP LOG Sales TO DISK = 'log.trn';You can also back up one file or filegroup.BACKUP LOG Sales TO DISK = 'tail.trn' WITH NORECOVERY;It leaves the database restoring.RESTORE DATABASE Sales FROM DISK = 'full.bak' WITH NORECOVERY;It overwrites the database.WITH STOPAT = '2026-10-20 14:00', RECOVERYUse the same STOPAT on every RESTORE LOG, and RECOVERY on the last.Asked in interviews
AskedExplain different types of backups in SQL Server.AVAILABILITY_MODE = SYNCHRONOUS_COMMIT The primary waits for the secondary to write the log.AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT The secondary can lag behind.FORCE_FAILOVER_ALLOW_DATA_LOSS Commits it never got are lost.TipThe listener, a DNS name, always points to the primary.
TrapAutomatic failover needs synchronous commit on both replicas.
Asked in interviews
AskedExplain Always On Availability Groups.WITH STANDBY = 'undo.tuf'It's read-only between restores.TrapManual failover loses changes that never shipped.
CREATE LOGIN [CORP\Ana] FROM WINDOWS;CREATE USER Ana FOR LOGIN [CORP\Ana];Logins are server-wide. Users belong to one database.ALTER ROLE db_datareader ADD MEMBER Ana;DENY DELETE ON dbo.Orders TO Ana;A DENY overrides a GRANT.ADD FILTER PREDICATERow-level security, set in a security policy.SET ENCRYPTION ONTransparent Data Encryption (TDE).TrapDynamic data masking only hides values in results. Always Encrypted protects them.
Asked in interviews
AskedHow do you implement and manage database security?Nothing matches. Try a shorter word, or clear the search.
With Pro, you can:
Already have Pro? Sign in