SQL Server Interview

Data Types, Keys & Constraints

SQL Server interview questions covering data types, keys & constraints.

20 Questions Filter by level

Interview questions

Open a question for the full answer, code and interview guidance.

20 Results

What are data types in SQL Server?

A data type defines what kind of value a column, variable, or parameter can store. Choosing the correct data type improves data integrity, storage efficiency, indexing, and query performance. Common SQL Server data types include INT and BIGINT for integers; DECIMAL for exact numeric values; VARCHAR and NVARCHAR for text; DATE, DATETIME2, and TIME for date/time values; BIT for Boolean-style values; UNIQUEIDENTIFIER for GUIDs; and VARBINARY for binary data. In production systems, use the smallest appropriate type that correctly represents the business data.

JuniorBeginner

What is the difference between CHAR and VARCHAR?

CHAR(n) stores fixed-length non-Unicode character data, while VARCHAR(n) stores variable-length non-Unicode character data. CHAR is appropriate when values have a consistent fixed length, such as certain standardized codes. VARCHAR is generally better when string lengths vary, because it does not always reserve the full declared character length for the stored value. For example, a CHAR(10) value is treated as fixed length, whereas VARCHAR(10) can store shorter strings more efficiently.

JuniorBeginner

What is the difference between VARCHAR and NVARCHAR?

VARCHAR stores non-Unicode character data using the database/collation code page, while NVARCHAR stores Unicode data using UTF-16 encoding in SQL Server. NVARCHAR is appropriate when the application must reliably store multilingual text such as Urdu, Arabic, Chinese, or mixed-language content. String literals intended as Unicode should normally use the N prefix. NVARCHAR can require more storage than VARCHAR for many character sets, so the choice should reflect the actual data requirements.

JuniorBeginner

What is the difference between INT and BIGINT?

INT and BIGINT both store whole numbers, but they have different ranges and storage requirements. INT uses 4 bytes and supports values from -2,147,483,648 through 2,147,483,647. BIGINT uses 8 bytes and supports a much larger range. INT is sufficient for many primary keys and counters. BIGINT is appropriate when the expected row count or business identifier can exceed the INT range. Choosing BIGINT unnecessarily increases storage for the column and related indexes.

JuniorBeginner

What is the difference between DECIMAL and FLOAT?

DECIMAL (also called NUMERIC) stores exact fixed-precision numeric values. FLOAT stores approximate floating-point values. DECIMAL is normally preferred for money, prices, financial calculations, percentages requiring exact precision, and other values where rounding accuracy matters. FLOAT is useful for scientific or engineering values where approximate representation is acceptable. For financial application code, DECIMAL should normally be used rather than FLOAT.

JuniorBeginner

What do precision and scale mean in DECIMAL?

Precision is the total maximum number of decimal digits that can be stored. Scale is the maximum number of digits stored to the right of the decimal point. For DECIMAL(18,2), precision is 18 and scale is 2. Therefore, up to 16 digits can appear before the decimal point and 2 digits after it. Choosing appropriate precision and scale is particularly important for prices, financial balances, percentages, measurements, and calculated values.

JuniorBeginner

Which date and time data types are commonly used in SQL Server?

Common SQL Server date/time types include DATE, TIME, DATETIME, DATETIME2, SMALLDATETIME, and DATETIMEOFFSET. DATE stores only a date. TIME stores only a time. DATETIME2 stores date and time with a wider range and greater fractional-second precision than the older DATETIME type. DATETIMEOFFSET additionally stores a UTC offset. For new application development, DATETIME2 is generally preferable to DATETIME when an offset is not required. For systems where the offset itself matters, DATETIMEOFFSET can be appropriate.

JuniorBeginner

What is BIT in SQL Server?

BIT is a data type commonly used for Boolean-style values. It can store 0, 1, or NULL when the column is nullable. In application code, a non-nullable BIT column usually maps naturally to a C# bool property in Entity Framework Core. BIT is commonly used for fields such as IsActive, IsDeleted, IsFeatured, or EmailConfirmed.

JuniorBeginner

What is UNIQUEIDENTIFIER in SQL Server?

UNIQUEIDENTIFIER is SQL Server's data type for globally unique identifiers (GUIDs). It stores a 16-byte value. GUIDs are useful when identifiers need to be generated independently across applications or distributed systems. They are also common with ASP.NET Core Identity and some domain models. Random GUIDs used as clustered keys can contribute to page splits and index fragmentation. Sequential key strategies may be considered when appropriate for the workload.

JuniorBeginner

What is the difference between NEWID() and NEWSEQUENTIALID()?

NEWID() generates a GUID with values that are effectively non-sequential for indexing purposes. NEWSEQUENTIALID() generates GUID values that are more sequential on a given machine, which can reduce page splits when the GUID column is indexed. NEWSEQUENTIALID() has usage restrictions: it is typically used as a DEFAULT constraint for a UNIQUEIDENTIFIER column and cannot simply be called anywhere like NEWID(). The choice depends on requirements such as distribution, predictability, indexing behavior, and identifier exposure.

JuniorBeginner

What is a UNIQUE constraint?

A UNIQUE constraint enforces uniqueness for the specified column or combination of columns. It is useful for candidate keys such as usernames, email addresses, codes, or SKUs. Unlike a primary key, a table can have multiple UNIQUE constraints. SQL Server implements uniqueness through a unique index. NULL behavior is also important: for a normal single-column UNIQUE constraint in SQL Server, only one NULL value is permitted.

JuniorBeginner

What is the difference between PRIMARY KEY and UNIQUE?

Both PRIMARY KEY and UNIQUE enforce uniqueness, but they serve different relational purposes. A PRIMARY KEY identifies the principal key chosen to identify each row. A table can have only one primary-key constraint, and its key columns cannot be NULL. A table can have multiple UNIQUE constraints, which are useful for alternate candidate keys. In SQL Server, a primary key creates a unique index by default; whether that index is clustered or nonclustered depends on the table's existing indexes and the explicit constraint definition.

JuniorBeginner

What is a composite key?

A composite key is a key formed from two or more columns. The combination of the columns uniquely identifies a row even though each individual column may contain duplicate values. Composite keys are common in junction tables representing many-to-many relationships. For example, an OrderItems-style or StudentCourses table may use two foreign-key columns together as its primary key.

JuniorBeginner

What is a candidate key?

A candidate key is a minimal set of one or more columns capable of uniquely identifying a row. A table can have several candidate keys. One candidate key is selected as the primary key, while other candidate keys can be enforced using UNIQUE constraints. For example, in an Employees table, EmployeeId may be the primary key while a government-issued employee code or another guaranteed-unique business identifier may be an alternate candidate key.

JuniorBeginner

What is a surrogate key?

A surrogate key is an artificial identifier created specifically to identify a database row rather than representing business meaning. Common examples are an INT IDENTITY column or a UNIQUEIDENTIFIER. Surrogate keys are often stable even when business attributes change. They can simplify relationships and foreign keys, particularly when the natural business key is long or consists of multiple columns. However, business uniqueness requirements should still be enforced separately with UNIQUE constraints where necessary.

JuniorBeginner

What is a natural key?

A natural key is an identifier derived from real business data rather than generated solely by the database. Examples can include an ISO country code, a stable product code, or another business identifier that is guaranteed to be unique. Natural keys can be meaningful, but they may be problematic if business rules allow them to change, if they are large, or if they contain multiple columns. Many production designs therefore use a surrogate primary key while enforcing the natural business identifier with a UNIQUE constraint.

JuniorBeginner

What is a DEFAULT constraint?

A DEFAULT constraint supplies a value when an INSERT statement does not explicitly provide a value for the column. Defaults are useful for properties such as creation timestamps, active flags, status values, and generated identifiers. A default does not override an explicitly supplied value, including an explicit NULL when NULL is allowed.

JuniorBeginner

What is a CHECK constraint?

A CHECK constraint validates data according to a Boolean condition before SQL Server accepts an INSERT or UPDATE. It is useful for enforcing domain rules close to the data, such as preventing negative prices, restricting percentages to a valid range, or validating allowed numeric ranges. Application validation is still useful for user experience, but database CHECK constraints protect the rule regardless of which application writes to the database.

JuniorBeginner

What is referential integrity?

Referential integrity ensures that relationships between tables remain valid. In SQL Server, foreign-key constraints are the primary mechanism for enforcing it. For example, if Products.CategoryId references Categories.Id, SQL Server can prevent a product from referencing a category that does not exist. It can also prevent deletion of a referenced parent row unless the relationship permits an action such as cascading deletion. Referential integrity prevents orphaned and inconsistent relational data.

JuniorBeginner

What are CASCADE actions in a foreign key?

Foreign-key referential actions determine what SQL Server should do to child rows when a referenced parent key is deleted or updated. Important options include NO ACTION, CASCADE, SET NULL, and SET DEFAULT. ON DELETE CASCADE automatically deletes related child rows when the parent row is deleted. It should be used only when that behavior matches the domain because an unintended parent deletion can otherwise remove a large hierarchy of dependent records. In production systems, cascade behavior should be designed deliberately and coordinated with Entity Framework Core relationship configuration.