nerdexam
Microsoft

70-465 · Question #35

Drag and Drop Question You plan to deploy SQL Server 2012. You must create two tables named Table 1 and Table 2 that will have the following specifications: - Table1 will contain a date column named…

The correct answer is Sparse; Computed. For Table1, the 'Sparse' column option is correct because SQL Server sparse columns eliminate storage overhead for NULL values - when a column is NULL approximately 80% of the time (and the threshold for storage savings is typically 60%+ NULLs for most data types including…

Submitted by takeshi77· Mar 5, 2026Design and implement database objects - specifically optimizing column storage using sparse and computed column features in SQL Server 2012 (Exam 70-465: Designing Database Solutions for SQL Server)

Question

Drag and Drop Question You plan to deploy SQL Server 2012. 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 that is the product of two other columns in Table2. Both Table1 and Table2 will contain more than 1 million rows. You need to recommend which options must be defined for the columns. The solution must minimize the storage requirements for 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 #35 exhibit

Answer Area

Drag items

SparseComputedPersisted computed

Correct arrangement

  • Sparse
  • Computed

Explanation

For Table1, the 'Sparse' column option is correct because SQL Server sparse columns eliminate storage overhead for NULL values - when a column is NULL approximately 80% of the time (and the threshold for storage savings is typically 60%+ NULLs for most data types including date), sparse columns significantly reduce storage by using no space for NULL values. For Table2, a non-persisted 'Computed' column is correct because it calculates the product of two other columns on-the-fly without storing the result on disk, thus using zero additional storage space. 'Persisted computed' would be wrong for Table2 because persisting the computed column physically stores the calculated value on disk, which increases storage requirements - the opposite of what is needed for minimizing storage across 1 million+ rows.

Topics

#SQL Server Storage Optimization#Sparse Columns#Computed Columns#Table Design

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-465 Practice