Data Types & Schema Design
7 questions foundWhat are the most commonly used data types in SQL Server, and when would you use each one?
Beginner INT is used for whole numbers like an id or a count, VARCHAR stores variable length text like names or descriptions, DECIMAL is used for precise numbers like currency where rounding errors matter, and DATETIME or DATE store date and time values, with the right choice depending on exactly what kind of data a column needs to hold.
CREATE TABLE Products (
ProductId INT PRIMARY KEY,
Name VARCHAR(100),
Price DECIMAL(10,2),
CreatedDate DATETIME
);
Real-world example A product catalog carefully chooses DECIMAL instead of a floating point type for storing prices, avoiding subtle rounding errors that could otherwise cause small but noticeable mistakes in financial calculations.
Common follow-ups: Why should DECIMAL be preferred over FLOAT for storing currency values?;What is the difference between CHAR and VARCHAR?
Normalization;Constraints (Primary Key Foreign Key Check & Unique)
What is the difference between VARCHAR and NVARCHAR, and when should you choose one over the other?
Beginner VARCHAR stores regular text using one byte per character and is sufficient for languages using the standard English alphabet, while NVARCHAR stores Unicode text using two bytes per character, supporting a much wider range of international characters and symbols, which is necessary for applications that need to support multiple languages.
CREATE TABLE Customers (
Name NVARCHAR(100),
Country VARCHAR(50)
);
Real-world example An international e-commerce platform uses NVARCHAR for customer names to properly support customers from many different countries whose names include characters outside the basic English alphabet.
Common follow-ups: Does using NVARCHAR instead of VARCHAR significantly increase storage requirements?;Should every text column default to NVARCHAR just to be safe?
Query Optimization & Plans;Indexes
How do you choose an appropriately sized data type for a column to balance storage efficiency with future flexibility?
Intermediate You consider the realistic range of values a column will ever need to hold, choosing a smaller data type like TINYINT or SMALLINT when the range is genuinely limited to save storage space, while avoiding overly restrictive types that might require a costly schema change later if the business requirements grow beyond what was originally expected.
-- TINYINT stores 0 to 255, sufficient for something like an age
CREATE TABLE Employees (
Age TINYINT,
Salary DECIMAL(12,2)
);
Real-world example A human resources system uses TINYINT for an employee's age since it will realistically never need to store a value close to that data type's maximum limit, saving a small amount of storage across millions of employee records.
Common follow-ups: What is the storage size difference between TINYINT, SMALLINT, and INT?;How do you safely change a column's data type on a table that already contains data?
Normalization;Constraints (Primary Key Foreign Key Check & Unique)
What are the differences between DATETIME, DATETIME2, and DATE, and when would you choose each one?
Intermediate DATE stores only a calendar date without any time component, DATETIME stores both a date and time but with a fixed precision and a limited date range going back to the year 1753, while DATETIME2 offers a wider date range, greater precision for fractional seconds, and is generally the recommended choice for new development.
CREATE TABLE Events (
EventDate DATE,
CreatedAt DATETIME2(3)
);
Real-world example A scheduling application stores the actual event date using the DATE type since time is not relevant, while using DATETIME2 for the more precise creation timestamp needed for auditing purposes.
Common follow-ups: Why is DATETIME2 generally recommended over the older DATETIME type?;What does the number in DATETIME2(3) actually represent?
Constraints (Primary Key Foreign Key Check & Unique);Query Optimization & Plans
How do you design a schema to properly handle a many to many relationship between two entities, such as students and courses?
Advanced You create a separate junction table, sometimes called a bridge or associative table, that contains foreign keys referencing both related tables, letting a single student be linked to many courses and a single course be linked to many students, without needing to duplicate data in either the students or courses tables themselves.
CREATE TABLE StudentCourses (
StudentId INT,
CourseId INT,
PRIMARY KEY (StudentId, CourseId),
FOREIGN KEY (StudentId) REFERENCES Students(StudentId),
FOREIGN KEY (CourseId) REFERENCES Courses(CourseId)
);
Real-world example A university's course registration system uses a junction table to track which students are enrolled in which courses, correctly supporting the reality that each student can take many courses and each course can have many students.
Common follow-ups: What additional columns might a junction table need beyond the two foreign keys?;How do you query a junction table to find all courses a specific student is enrolled in?
Normalization;Joins
What factors should guide the decision to denormalize a schema for performance reasons, and what tradeoffs come with that decision?
Advanced You might denormalize by duplicating some data across tables to avoid expensive joins in frequently run, performance critical queries, but this comes with the tradeoff of needing to keep the duplicated data synchronized whenever the original value changes, which adds complexity and the risk of inconsistent data if not carefully managed.
-- Denormalized example: storing a customer's name directly on the order
-- to avoid a join for a frequently run reporting query
CREATE TABLE Orders (
OrderId INT PRIMARY KEY,
CustomerId INT,
CustomerNameSnapshot VARCHAR(100)
);
Real-world example A reporting heavy application stores a snapshot of the customer's name directly on each order record, avoiding a join for a report that runs thousands of times a day, while accepting the small added complexity of that duplicated data.
Common follow-ups: How do you keep denormalized data consistent when the original source data changes?;What is a reasonable process for deciding when denormalization is actually worth the tradeoff?
Normalization;Query Optimization & Plans
How do computed columns work in SQL Server, and what are some practical use cases for them?
Intermediate A computed column automatically calculates its value based on an expression involving other columns in the same table, rather than storing a value that must be manually kept up to date, which is useful for things like calculating a full name from first and last name columns or a total price from quantity and unit price.
CREATE TABLE OrderItems (
Quantity INT,
UnitPrice DECIMAL(10,2),
TotalPrice AS (Quantity * UnitPrice)
);
Real-world example An order line item table automatically calculates the total price for each item using a computed column, ensuring that value always stays accurate and consistent without requiring any application code to calculate and maintain it separately.
Common follow-ups: Can a computed column be persisted and indexed for better performance?;What happens if one of the columns used in the computed column's formula changes?
Constraints (Primary Key Foreign Key Check & Unique);Indexes