Real World SQL Server DBA Interview Questions

These are base on 25 years of interviews and interviewing for SQL Server jobs.

1. What are the 3 Recovery Models and when do we use them?

• Full. This is the one we use for Production. Backs up Data and Logs.
• Simple. Good for Dev or any machine where the data is not important and does not change. Backs up Data but not logs and just gives logs a Checkpoint.
• Bulk Logged.  Rarely used it does not log big bulk imports. If you have huge imports that can be rerun if there is a problem this can be useful but in 25 years of consulting I have never seen any company use this. 

2. What is Checkpoint?

• A Checkpoint provides a known good point for SQL Server.  It is done when we do a Backup it writes dirty pages in memory to disk and cleans out old transactions from the log. 
• For more Info: https://docs.microsoft.com/en-us/sql/relational-databases/logs/database-checkpoints-sql-server  

3. Define ACID?
a. A RDBMS combines Atomicity, Consistency, Isolation, and Durable. This tends to be an old fashioned question in Interviews but it still shows up.

4. What is the Difference between Delete and Truncate?

• Delete is logged and Truncate is not. Truncate is faster but you cannot roll it back. 
• Truncate is DDL and Truncate is DML.

5. What is DBCC?
• This is a holdover from Sybase and was retained and rewritten in 2008.  Pre 2008 it was Database Consistency Checks and after it became Database Console Commands. These include Maintenance, Miscellaneous, Informational and Validation. Best to memorize a few, Interviewers often ask for 3 or 4 examples.
• DBCC CHECKALLOC
• DBCC CHECKCATALOG
• DBCC CHECKCONSTRAINTS
• DBCC CHECKDB
• DBCC CHECKFILEGROUP
• DBCC CHECKIDENT
• DBCC CHECKTABLE
• DBCC CLEANTABLE
• DBCC DBREINDEX Deprecated use  ALTER INDEX  instead.
• DBCC dllname (FREE) Unloads the specified extended stored procedure DLL from memory without shutting down the server.
• DBCC DROPCLEANBUFFERS
• DBCC FLUSHAUTHCACHE Used with KILL DATABASE CONNECTION after password change.
• DBCC FREEPROCCACHE
• DBCC FREESESSIONCACHE
• DBCC FREESYSTEMCACHE
• DBCC HELP
• DBCC INDEXDEFRAG look at ALTER INDEX ALL ON DBName.TableName

REORGANIZE ; instead. Can be done online.

• DBCC INPUTBUFFER
• DBCC OPENTRAN
• DBCC OUTPUTBUFFER
• DBCC PDW_SHOWEXECUTIONPLAN Gives the SQL Server execution plan for a query by SPID.
• DBCC PDW_SHOWPARTITIONSTATS
• DBCC PDW_SHOWSPACEUSED Gives the number of rows, disk space reserved, and disk space used for a specific table, or for all tables in a Azure Synapse Analytics or Analytics Platform System (PDW) database.
• DBCC PROCCACHE
• DBCC SHOW_STATISTICS
• DBCC SHOWCONTIG DEPRICATED use sys.dm_db_index_physical_stats instead.
• DBCC SHRINKDATABASE Best advice, Don't! When you shrink the DB you fragment the Indexes. That shoots performance. If your company cannot afford more disk space it is time to float your resume. If you are doing it to move a file to a new file share look at DBCC SHRINKFILE.
• DBCC SHRINKFILE If you need to move a file this an option.
• DBCC SHRINKLOG: Use DBCC SHRINKLOG to reduce fragmentation and reduce the log size.
• DBCC SQLPERF It gives transaction log space usage statistics for all databases. But a big benefit is that you can use it to reset wait and latch statistics without rebooting the server.
• DBCC TRACEOFF Disables the specified trace flags globally.
• DBCC TRACEON - Trace Flags
• DBCC TRACEON
• DBCC TRACESTATUS
• DBCC UPDATEUSAGE SQL Server table and Index counts are not always accurate in a high volume system or after a bulk copy. If you need accurate numbers use this.
• DBCC USEROPTIONS Shows some good data but most people just use it for checking the Isolation Level.


6. Types of Replication?

• Transactional, 
• Merge, 
• Snapshot. 
• Peer to Peer

7. When should we use Query Hints?

• Only when we are absolutely sure that we are smarter than the SQL Server Query Optimizer. Keep in mind that even if the use is right it will be wrong if you apply a Service Pack, upgrade the software or hardware it will be wrong. 
• https://technet.microsoft.com/en-us/library/ms181714(v=sql.110).aspx 

8. What is a Sparse Column and when do you use it.

• It is used for column that is rarely populated. When it equal 0 or null it takes zero space but when it is populated it takes 4 bytes more space than each one. If you have Sparse Columns match them with Filtered Indexes.

9. Name 4 ways to create a Database

• TSQL, SSMS, Restore a DB, the Copy DB Wizard
• Stupid Question: Real world DBAs are rarely if ever going to use a Wizard and only SSMS in Dev. Restoring a Database is common and reasonable as is a TSQL script but Wizards and GUI do not offer a record of the action. 

10. What are Magic Tables?

• Inserted, Updated and Deleted tables built into SQL Server to hold recent changes.  They are primarily used for DML Triggers. 
• Stupid Question: SQL Server does not call them Magic Tables in their documentation. This term is commonly used by Programmers. I don’t like it but it is asked in Interviews so don’t get caught on it. 

11. Fill Factor

• You can set the Fill Factor when you Build, Reorganize or Rebuild the Index. It often seems like a good idea to adjust this. However there are some Caveats:
• If it is an Auto Incrementing field such as an Identity Cluster ID adding space will hurt performance since the pages don’t split, they just keep adding the end page.
• If the Column is subject to many changes the fill Factor only helps the First Page split and then it splits 50/50 until you rebuild or reorg.
• Overall fiddling with Fill Factor is a waste of time.

12. What is TempDB used for?

• For Temp objects tables, indexes, also building Indexes, Group By, Order By, and Union/Union All.
• Also if you have heap tables this is where the Table Scans trigger Hash Tables.

13. TempDB Configuration

• New versions of SQL Server default to one TempDB file for each Logical Core or 8 whichever is less. If you are using a 2019 or 2022 Database created in an earlier version you would benefit from increasing the number of TempDB files correspondingly.

14. What kind of Indexes does SQL Server support.

• Memory Optimized Non Clustered, 
• Clustered, 
• Non Clustered, 
• ColumnStore, 
• XML, 
• Hash Index (Since 2014), 
• Filtered, 
• Full Text, 
• Sparse,
• Unique.

15. How many Indexes can a Table have.

• 1 Clustered and 999 (2008 - 2022)
• Stupid Question: Asking how many Custers is fine but asking the maximum number of Non Clustered is silly. Ten Indexes is a lot and 50 is unlikely except in very rare cases. So why ask a question about something a DBA never needs to know. 

16. How many Clustered Indexes can a Table have?

• Just one, because the Cluster sets the order of the table.

17. Does a Small Table need a Cluster?

• Yes. Some old documentation says No but SQL Server runs faster if every table has a Cluster. If SQL Server Query Optimizer can always ignore the Cluster if it determines it is better. 
• If the table is ever queried it should have a clustered indexed. However tables not normally queried DO NOT benefit from a cluster with faster inserts.

18. What sort of Fields makes the best Primary Key?

• The more compact the PK the more efficient. Using an Int or Big Int, Identity works great. Composite Unique Keys used to be popular. They can still be OK if they are not too wide. 
• SQL Server uses 8K pages So the performance improves with more indexes on an 8 K page. A Big Int  is twice as big as an Int. But on large tables a GUID is twice as big as a Big Int so it may be a choice. Try to avoid large compound index clusters using Varchars/NVarchars.

19. Name ways to Import Data besides SSIS?

• BCP, 
• Bulk Import, 
• Linked Server, 
• Open Rowset, 
• Open Data Source. 
• DB Library, 
• ODBC, 
• JDBC and likely others.
• I am not sure why I hear this question but it is out there.

20. What is the Default Port for SQL Server.

• 1433 but for security reasons companies commonly change the port. 

21. What is the Order of Lock Escalation?

• Row Lock to Table lock. Or Page Lock to Table lock. But not Row Lock  to Page Lock.

22. When was Synonyms Introduced?

• SQL Server 2005.

23. What is the difference between Union and Union All?

• Union All is faster but it includes Nulls. Union does not include Nulls.

24. Does Null = Null work

• No, "Null IS Null" but since Null is undefined it cannot equal Null.

25. Does a Cluster need to be Unique?

• No SQL Server has a Uniquifier. When you create a Cluster without specifying Unique SQL Server  adds a hidden column to make it Unique.

I will add and update. If you have a comment or correction or if you want to prove you are smarter than I am just drop me a line at Barry.OConnell@Yahoo.com