Home » Microsoft » MB6-886 » What should you include in the design?
You are designing a document repository application that will contain over 100,000 documents. The repository will have the following specifications:
• Documents can be associated to 30 different properties
• Most documents will have less than 10 properties defined
You need to design a single table for the application. The solution must use the minimum amount of storage space.
What should you include in the design?
A. an XML data type
B. nvarchar() null
C. sparse columns
D. varchar(max) not null
Correct Answer: C
Explanation/Reference:
Taking into account that we need to design a single table, this table will containt rows most of which will have NULL in more than 20 columns. Because our solution must use the minimum amount of storage space, we should use sparse columns.
Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.