This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Design and implement database objects
      --> Design and implement tables, including data types, size, columns, indexes, and column store indexes

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

One of the most fundamental skills measured on the DP-800: Developing AI-Enabled Database Solutions exam is the ability to design and implement efficient database tables. Every SQL solution—whether supporting traditional applications, analytics, or AI-enabled workloads—depends on well-designed tables that maximize performance, maintain data integrity, minimize storage requirements, and scale effectively.

Poor table design often results in slow queries, excessive storage consumption, locking issues, and difficult maintenance. Conversely, properly designed tables improve application responsiveness, simplify development, and reduce infrastructure costs.

This article covers the key concepts required for the DP-800 exam, including:

• Choosing appropriate data types
• Determining column sizes
• Designing table structures
• Creating clustered and nonclustered indexes
• Understanding filtered, included, and composite indexes
• Implementing columnstore indexes
• Best practices and common design mistakes

Designing Tables

A table stores related information organized into rows and columns.

Good table design should achieve the following goals:

• Eliminate unnecessary duplication
• Support efficient queries
• Enforce data integrity
• Reduce storage requirements
• Support future growth
• Minimize maintenance

A typical design process includes:

1. Identify entities
2. Define columns
3. Choose data types
4. Select appropriate sizes
5. Determine nullable columns
6. Define primary keys
7. Create foreign keys
8. Add indexes based on workload

Choosing Appropriate Data Types

Selecting the correct data type is one of the most important database design decisions.

Using oversized or inappropriate data types increases:

• Storage usage
• Memory usage
• Network traffic
• Index size
• Backup size
• Query execution time

Integer Data Types

Example:

CustomerID INT
OrderID BIGINT
Age TINYINT

CustomerID INT

OrderID BIGINT

Age TINYINT

Decimal and Numeric

Used for precise financial calculations.

Price DECIMAL(10,2)

Price DECIMAL(10,2)

• 10 total digits
• 2 digits after the decimal

Examples:

12345678.90
99999999.99

12345678.90
99999999.99<b
r>


</b

Floating Point Types

Used for scientific calculations.

FLOAT
REAL

FLOAT



REAL

• Measurements
• Statistics
• Sensor data

Avoid for:

• Currency
• Accounting
• Financial systems

Character Data Types

CHAR

Fixed-length storage.

CHAR(2)

CHAR(2)

• Country codes
• State abbreviations
• Status values

VARCHAR

Variable-length storage.

VARCHAR(100)

VARCHAR(100)

Ideal for:

• Names
• Email addresses
• Descriptions

NCHAR and NVARCHAR

Support Unicode characters.

NVARCHAR(100)

NVARCHAR(100)

• Multiple languages
• International names
• Emoji
• Unicode symbols

VARCHAR(MAX)

Stores very large text.

Use only when necessary.

Examples:

• Documents
• Long descriptions
• JSON

Avoid using MAX columns unnecessarily because they reduce performance.

Date and Time Data Types

Common options include:

Microsoft recommends DATETIME2 for most new applications.

Example:

CreatedDate DATETIME2

Binary Data Types

Examples include:

VARBINARY
VARBINARY(MAX)

VARBINARY



VARBINARY(MAX)

• Images
• Encryption keys
• Files
• AI embeddings (in some scenarios)

UniqueIdentifier

Stores globally unique identifiers (GUIDs).

CustomerGuid UNIQUEIDENTIFIER

CustomerGuid UNIQUEIDENTIFIER

• Globally unique
• Useful for distributed systems

Drawbacks:

• Larger indexes
• Can fragment clustered indexes when generated randomly

NULL vs NOT NULL

Every column should explicitly define whether NULL values are allowed.

Example:

FirstName NVARCHAR(50) NOT NULL
MiddleName NVARCHAR(50) NULL

FirstName NVARCHAR(50) NOT NULL

MiddleName NVARCHAR(50) NULL

• Improves data integrity
• Simplifies queries
• Often improves performance

Identity Columns

Automatically generate sequential values.

Example:

CustomerID INT IDENTITY(1,1)

CustomerID INT IDENTITY(1,1)

Start at 1

Increment by 1

Commonly used as surrogate primary keys.

Computed Columns

Values calculated from other columns.

Example:

FullName AS FirstName + ' ' + LastName

FullName AS FirstName + ‘ ‘ + LastName

Sparse Columns

Designed for tables with many NULL values.

Benefits:

• Reduce storage
• Useful for optional attributes

Trade-off:

Slightly higher processing overhead.

Table Constraints

Constraints enforce data integrity.

Primary Key

Uniquely identifies each row.

PRIMARY KEY (CustomerID)

PRIMARY KEY (CustomerID)

• Unique
• NOT NULL
• Automatically indexed

Foreign Key

Maintains relationships between tables.

FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)

UNIQUE Constraint

Prevents duplicate values.

Example:

EmailAddress UNIQUE

CHECK Constraint

Restricts acceptable values.

Example:

CHECK (Salary > 0)

DEFAULT Constraint

Automatically inserts default values.

Example:

CreatedDate DATETIME2
DEFAULT GETDATE()

Index Fundamentals

Indexes improve query performance by reducing table scans.

Without indexes:

SQL Server reads every row.

SQL Server reads every row.

SQL Server quickly locates matching rows.

SQL Server quickly locates matching rows.

• WHERE
• JOIN
• ORDER BY
• GROUP BY

Clustered Index

Determines the physical order of rows.

Each table can have only one clustered index.

Example:

CREATE CLUSTERED INDEX IX_Customers
ON Customers(CustomerID);

CREATE CLUSTERED INDEX IX_Customers

ON Customers(CustomerID);

• Primary keys
• Sequential values

Nonclustered Index

Stores a separate searchable structure.

A table can have many nonclustered indexes.

Example:

CREATE INDEX IX_LastName
ON Customers(LastName);

CREATE INDEX IX_LastName

ON Customers(LastName);

Composite Index

Contains multiple columns.

Example:

CREATE INDEX IX_OrderDateCustomer
ON Orders(OrderDate, CustomerID);

CREATE INDEX IX_OrderDateCustomer

ON Orders(OrderDate, CustomerID);

The leftmost column should be the most selective or frequently filtered.

Included Columns

Include non-key columns to create covering indexes.

Example:

CREATE INDEX IX_LastName
ON Customers(LastName)
INCLUDE (FirstName, EmailAddress);

CREATE INDEX IX_LastName

ON Customers(LastName)

INCLUDE (FirstName, EmailAddress);

• Reduces key lookups
• Improves SELECT performance

Filtered Index

Indexes only selected rows.

Example:

CREATE INDEX IX_ActiveCustomers
ON Customers(Status)
WHERE Status='Active';

CREATE INDEX IX_ActiveCustomers

ON Customers(Status)



WHERE Status=’Active’;

• Smaller index
• Faster maintenance
• Better query performance

Covering Index

A covering index contains every column required by a query.

Example:

Query:

SELECT FirstName, LastName
FROM Customers
WHERE LastName='Smith';

SELECT FirstName, LastName

FROM Customers

WHERE LastName=’Smith’;

• LastName (key)
• FirstName (included)

No lookup to the base table is required.

Index Maintenance

Indexes require regular maintenance.

Common tasks include:

• Rebuild indexes
• Reorganize indexes
• Update statistics
• Monitor fragmentation

Highly fragmented indexes reduce performance.

Columnstore Indexes

Columnstore indexes store data by columns rather than rows.

Traditional storage:

Row 1
Row 2
Row 3

Row 1

Row 2

Row 3

CustomerID
FirstName
LastName
City

CustomerID

FirstName

LastName

City

Benefits of Columnstore Indexes

Advantages include:

• High compression
• Reduced storage
• Faster aggregations
• Parallel processing
• Batch execution mode

Ideal for:

• Data warehouses
• Reporting
• Analytics
• AI feature engineering
• Large fact tables

Clustered Columnstore Index

Entire table stored in column format.

Example:

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales
ON Sales;

CREATE CLUSTERED COLUMNSTORE INDEX CCI_Sales

ON Sales;

• Fact tables
• Large analytical workloads

Nonclustered Columnstore Index

Adds columnstore capabilities to an existing rowstore table.

Example:

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales
ON Sales
(
Revenue,
Quantity,
ProductID
);

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sales

ON Sales


(
Reven
ue,
Quantity,

ProductID

);

Rowstore vs Columnstore

Choosing the Right Index

Best Practices

• Choose the smallest appropriate data type.
• Avoid VARCHAR(MAX) unless required.
• Use DATETIME2 instead of DATETIME for new development.
• Define NOT NULL whenever appropriate.
• Create indexes based on query patterns rather than every column.
• Avoid excessive indexing because each index increases insert, update, and delete costs.
• Use composite indexes carefully, considering column order.
• Regularly rebuild or reorganize fragmented indexes.
• Use clustered columnstore indexes for large analytical tables.
• Test index changes using execution plans and performance metrics.

Common Exam Tips

For the DP-800 exam, remember these key points:

• Smaller data types improve storage efficiency.
• Clustered indexes determine physical row order.
• A table can have only one clustered index.
• Multiple nonclustered indexes are allowed.
• Included columns create covering indexes.
• Filtered indexes reduce storage and improve performance for selective queries.
• Composite index column order matters.
• Columnstore indexes are optimized for analytical workloads.
• DATETIME2 is preferred over DATETIME for new applications.
• FLOAT should not be used for financial data.

Practice Exam Questions

Question 1

A database designer needs to store a person’s age. The maximum expected value is 120. Which data type is the most storage-efficient?

A. INT

B. SMALLINT

C. TINYINT

D. BIGINT

Answer: C

Explanation: TINYINT stores values from 0 to 255 using only one byte, making it the most efficient choice for age values.

Question 2

A company stores customer names in multiple languages, including Japanese and Arabic. Which data type should be used?

A. CHAR

B. VARCHAR

C. TEXT

D. NVARCHAR

Answer: D

Explanation: NVARCHAR supports Unicode characters, making it suitable for multilingual applications.

Question 3

Which index determines the physical order of rows within a SQL Server table?

A. Nonclustered index

B. Filtered index

C. Clustered index

D. Columnstore index

Answer: C

Explanation: A clustered index defines the physical storage order of rows. Each table can have only one clustered index.

Question 4

A reporting system performs large aggregation queries against a fact table containing hundreds of millions of rows. Which index type is most appropriate?

A. Clustered columnstore index

B. Nonclustered index

C. Filtered index

D. XML index

Answer: A

Explanation: Clustered columnstore indexes are optimized for large analytical workloads, providing high compression and fast aggregations.

Question 5

Why might a developer create a filtered index?

A. To improve backup performance

B. To index only rows matching a specific condition

C. To encrypt indexed values

D. To automatically partition a table

Answer: B

Explanation: A filtered index includes only rows that satisfy a defined predicate, reducing storage and maintenance while improving performance for targeted queries.

Question 6

A table has a composite index on (OrderDate, CustomerID). Which query is most likely to benefit directly from the index?

A. Filtering only on CustomerID

B. Filtering only on ProductID

C. Filtering on OrderDate

D. Filtering only on TotalAmount

Answer: C

Explanation: Composite indexes are most effective when queries use the leftmost indexed column. A filter on OrderDate can efficiently leverage the index.

Question 7

Which statement about clustered indexes is correct?

A. A table can have many clustered indexes.

B. Clustered indexes cannot contain primary keys.

C. Clustered indexes store data separately from the table.

D. A table can have only one clustered index.

Answer: D

Explanation: Because a clustered index defines the physical order of the rows, only one clustered index can exist per table.

Question 8

A developer wants to eliminate expensive key lookups for a frequently executed query without changing the indexed search column. Which feature should be used?

A. Sparse columns

B. Included columns

C. Identity columns

D. Computed columns

Answer: B

Explanation: Included columns allow additional non-key columns to be stored in a nonclustered index, creating a covering index that can avoid key lookups.

Question 9

Which data type is recommended for storing currency values that require exact precision?

A. FLOAT

B. REAL

C. DECIMAL

D. MONEY with floating-point conversion

Answer: C

Explanation: DECIMAL provides fixed precision and scale, making it appropriate for financial calculations where exact values are required.

Question 10

Why are columnstore indexes particularly valuable for AI and analytics workloads?

A. They increase transaction locking.

B. They optimize sequential identity generation.

C. They eliminate the need for primary keys.

D. They provide high compression and significantly accelerate large scan and aggregation queries.

Answer: D

Explanation: Columnstore indexes organize data by column, enabling excellent compression and efficient execution of analytical queries common in reporting, feature engineering, and AI scenarios.

Go to the DP-800 Exam Prep Hub main page.