nerdexam
Microsoft

70-465 · Question #134

You plan to deploy a database to SQL Azure. You must create two tables named Table 1 and Table 2 that will have the following specifications: - Table1 will contain a date column named Column1 that wil

The correct answer is Sparse; Persisted computed. Column1 in Table1 should use the SPARSE option because sparse columns are optimized for storing NULL values - when approximately 80% of values are NULL, sparse columns reduce storage overhead significantly, improving retrieval performance over millions of rows. Column2 in Table2

Submitted by lukas.cz· Mar 5, 2026Design and implement database objects - specifically optimizing column storage and retrieval performance in Azure SQL Database

Question

You plan to deploy a database to SQL Azure. You must create two tables named Table 1 and Table 2 that will have the following specifications: - Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time. - Table2 will contain a column named Column2 which is the product of two other columns in Table2. Both Table1 and Table2 will contain over one million rows. You need to recommend which options must be defined for the columns. The solution must minimize the time it takes to retrieve data from the tables. Which options should you recommend? To answer, drag the appropriate options to the correct column in the answer area. Answer:

Exhibit

70-465 question #134 exhibit

Answer Area

Drag items

SparseComputedPersisted computed

Correct arrangement

  • Sparse
  • Persisted computed

Explanation

Column1 in Table1 should use the SPARSE option because sparse columns are optimized for storing NULL values - when approximately 80% of values are NULL, sparse columns reduce storage overhead significantly, improving retrieval performance over millions of rows. Column2 in Table2 should use PERSISTED COMPUTED because a persisted computed column physically stores the calculated result on disk, meaning the product of the two columns is pre-calculated and stored rather than recalculated at query time, which minimizes retrieval time across over one million rows. The plain COMPUTED option (non-persisted) is incorrect for Column2 because non-persisted computed columns recalculate the expression every time the data is queried, which is slower for large tables with frequent reads.

Topics

#SQL Azure#Sparse Columns#Computed Columns#Table Design Optimization

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-465 Practice