Skip to main content

Shibboleths and their answers (Interview Questions)

I'm not a DBA but I've been grilled on various SQL and database questions during interviews as shibboleths. I've tried to come up with satisfactory answers for both my own knowledge and to deal with these shibboleths.

What is an index?

An index is a data structure that enhances the speed of queries that access data from a table; the tradeoff of setting up an index is that it costs more RAM and requires more disk writes to persist the index.

Indices can be unique, non-unique, clustered, or non-clustered.

  • Unique indices are ones that maintain table integrity by ensuring that no two rows of data in a given table have identical keys.
  • Non-unique indices are used to improve query performance by maintaining a sorted order of rows that are accessed most frequently.
  • Clustered indices sort and store data rows in a table or view based on their key values, and there can only be one per table because the rows themselves can only be stored in one order.
  • Non-clustered indices sort and store data rows in a table or view in a specified order, but there can be multiple non-clustered indices per table.

What is a view?

A view is just a virtual table based on the results of a query.

What is the difference between a table expression and a CTE?

The contents of a table expression only exist within the scope of their location in the query, while CTEs can be assigned to variables in T-SQL.

Additionally, CTEs can be recursive in nature, which carries all the dangers of recursion.

What is the difference between dropping a table and using the TRUNCATE statement?

When a table is dropped, everything associated with the table is dropped as well. This includes relationships with other tables, any permissions associated with the table, etc. However, truncate will simply clear all of the rows from the table while retaining the structure.

What is ACID?

ACID stands for:

  • Atomicity (all transactions are completed in an all-or-nothing manner; either all rows are executed or none are)
  • Consistency (updates made to the database are predictable and do not produce unintended side effects)
  • Isolation (if multiple users are accessing the same table, their transactions won't interfere with the other)
  • Durability (changes made by successful transactions will be saved even if the system fails)

 

Back to top