SQL Server interview questions covering database design, normalization & advanced sql.
Open a question for the full answer, code and interview guidance.
Normalization is the process of organizing relational data to reduce unnecessary redundancy and avoid data anomalies. It typically decomposes large structures into related tables while preserving meaningful relationships through keys. Normalization improves consistency and maintainability. However, the goal is not to create the maximum possible number of tables; the schema should model the business correctly while supporting required workloads.
A table is generally considered to satisfy First Normal Form when each row/column intersection contains a single value from the column's domain and repeating groups are removed. For example, storing multiple phone numbers in one comma-separated PhoneNumbers column makes relational querying, validation, indexing, and referential integrity difficult. A separate CustomerPhones table is usually a better relational design.
Second Normal Form requires the table to be in 1NF and requires every non-key attribute to depend on the whole candidate key rather than only part of a composite key. This issue is mainly relevant when a key contains multiple columns. For example, if an OrderItems table has a composite key of OrderId and ProductId, ProductName depends only on ProductId rather than the whole composite key and normally belongs in Products.
Third Normal Form requires the table to satisfy 2NF and removes inappropriate transitive dependencies of non-key attributes on other non-key attributes. For example, if an Employees table stores DepartmentId and DepartmentName, and DepartmentName is determined by DepartmentId, department information normally belongs in a Departments table. Employees then stores DepartmentId as a foreign key.
Boyce-Codd Normal Form (BCNF) is a stronger normalization condition than 3NF. Informally, for every non-trivial functional dependency X → Y in a relation, X should be a superkey. Most routine application schemas focus on 1NF through 3NF, but BCNF becomes relevant when a table has overlapping candidate keys or dependency structures that can still produce anomalies even though the table appears to satisfy 3NF.
Denormalization intentionally introduces redundancy or precomputed data to improve read performance or simplify specific access patterns. Examples include storing summary totals, maintaining reporting tables, or duplicating descriptive data in a warehouse. Denormalization can improve read-heavy workloads but creates additional consistency and update complexity. It should be based on measured requirements rather than used as a substitute for proper indexing or query optimization.
An insertion anomaly occurs when one fact cannot be stored without supplying unrelated data. An update anomaly occurs when the same fact is duplicated in several rows and must be changed everywhere. A deletion anomaly occurs when deleting one record unintentionally removes another independent fact. Normalization reduces these anomalies by separating facts into appropriately related tables.
A one-to-one relationship means one row in one table corresponds to at most one related row in another table, and vice versa. It can be implemented by making the dependent table's foreign key unique or by using the same key as both primary key and foreign key. One-to-one designs are useful for optional extensions, security separation, or splitting data with different lifecycle requirements.
A one-to-many relationship means one parent row can have many child rows, while each child row references one parent. Examples include Category-to-Products, Customer-to-Orders, and PoliceStation-to-CaseReports. It is implemented by storing the parent's key as a foreign key in the child table.
A many-to-many relationship exists when rows on both sides can relate to multiple rows on the other side. In a relational database, it is normally modeled through a junction table. For example, a student can enroll in many courses and each course can contain many students. The junction table can use a composite primary key to prevent duplicate relationships.
A temporary table is a table stored in tempdb and intended for temporary processing. A local temporary table begins with # and is scoped primarily to the creating session, while a global temporary table begins with ## and has broader visibility subject to SQL Server lifetime rules. Temporary tables support indexes and statistics and are often useful when intermediate results need to be reused or when breaking a complex operation into stages improves performance.
A table variable is declared using DECLARE @Name TABLE and stores tabular intermediate data, using tempdb infrastructure internally. Table variables have different optimization and scope characteristics from temporary tables. Modern SQL Server versions have improved table-variable cardinality estimation in some scenarios, but temporary tables can still be preferable for larger or more complex intermediate datasets because of statistics and indexing flexibility. The choice should be based on workload and measured execution plans rather than the simplistic rule that table variables are always memory-only or always faster.
A #temporary table is created in tempdb, supports flexible indexing and statistics, and is often effective for larger intermediate result sets. A table variable has batch/procedure scope and historically had more limited cardinality information, although newer SQL Server versions can improve optimization through deferred compilation. Neither option is universally faster. The correct choice depends on row count, reuse, indexing needs, query complexity, SQL Server version, recompilation behavior, and the actual execution plan.
PIVOT transforms values from rows into columns while applying an aggregate. It is useful for cross-tab reports such as displaying monthly totals as separate columns. PIVOT is convenient when output columns are known in advance. If column values are dynamic, dynamic SQL is commonly required to generate the pivot column list safely.
UNPIVOT performs the conceptual reverse of PIVOT by transforming multiple columns into rows. It is useful when data has been stored or received in a cross-tab structure but needs to be processed in a more relational row-based format. CROSS APPLY with VALUES can also be a flexible alternative for unpivoting in many SQL Server queries.
CROSS APPLY evaluates a table expression for each row from the left input and returns only left rows for which the right-side expression produces rows. It is particularly useful with table-valued functions, correlated TOP queries, JSON/XML processing, and selecting a small related set per parent row. It resembles an INNER JOIN in its row-preservation behavior but supports correlated table expressions naturally.
OUTER APPLY is similar to CROSS APPLY, but it preserves rows from the left input even when the right-side table expression returns no rows. In that case, right-side columns are NULL. Its row-preservation behavior is therefore analogous to LEFT JOIN while retaining APPLY's ability to evaluate a correlated table expression per left row.
MERGE can combine insert, update, and delete-style synchronization logic between a source and target in one statement. Although concise, MERGE has historically had important correctness, concurrency, and implementation caveats in SQL Server. Production teams often prefer explicit INSERT/UPDATE/DELETE statements inside a carefully designed transaction because their behavior can be easier to reason about and test. If MERGE is used, its concurrency semantics, matching rules, duplicate source rows, and the SQL Server version's known behavior should be evaluated carefully.
A sequence is a schema-scoped object that generates numeric values independently of a specific table. Unlike IDENTITY, a sequence can be shared across multiple tables and values can be requested before an INSERT. Sequences support options such as START WITH, INCREMENT BY, MINVALUE, MAXVALUE, CYCLE, and CACHE. Like identity values, sequence-generated numbers should not be assumed to be gap-free.
A computed column derives its value from an expression involving other columns in the same table. SQL Server calculates the value according to the expression rather than requiring the application to maintain it manually. A computed column can sometimes be marked PERSISTED so the computed result is physically stored, subject to SQL Server requirements. Eligible computed columns can also be indexed when determinism, precision, and other requirements are satisfied. Computed columns are useful for reusable derived values, but business rules and performance implications should be considered before storing or indexing them.