You use SQL Server 2014. You create a table within a database by using the following DDL: The following table illustrates a representative sample of data: The system is expected to handle 50 million o

Question

Exhibits

Options

• AOrderID: Sparse
• BOrderID: Not Sparse
• COrderDate: Sparse
• DOrderDate: Not Sparse
• ECustomerID: Sparse
• FCustomerID: Not Sparse
• GIsTaxable: Sparse
• HIsTaxable: Not Sparse
• ISubTotal: Sparse
• JSubTotal: Not Sparse
• KTaxAmount: Sparse
• LTaxAmount: Not Sparse
• MFreight: Sparse
• NFreight: Not Sparse

• A
  23% (7)
• B
  58% (18)
• C
  3% (1)
• J
  6% (2)
• M
  10% (3)

Explanation

SPARSE columns are beneficial only when a column contains a high percentage of NULL values - they save storage for NULLs at the cost of extra overhead (4 bytes) per non-NULL value. OrderID, OrderDate, and CustomerID are mandatory fields populated on every order row, so making them SPARSE (choices A, C, E) would increase storage overhead with no benefit. IsTaxable, SubTotal, and TaxAmount are conditional - based on the representative data sample, these columns contain enough NULLs (e.g., non-taxable orders have NULL TaxAmount and NULL SubTotal entries) to meet the NULL-density threshold where SPARSE yields a net saving; marking them NOT SPARSE (H, J, L) would waste space at 50M rows/month scale. Freight appears frequently enough in the sample to be NOT SPARSE - marking it SPARSE (M) would add per-row overhead without sufficient NULL savings.

Memory tip: Think "SPARSE = spare NULLs." If a column is sparsely filled with real data (mostly NULL), use SPARSE. If it's densely filled (mostly non-NULL), SPARSE costs more than it saves. A quick rule of thumb: >20-40% NULLs (threshold varies by data type) -> candidate for SPARSE; core identity columns like IDs and dates -> never SPARSE.

No community discussion yet for this question.