Exam 70-764 topic 1 question 262 discussion

Actual exam question from Microsoft's 70-764

Question #: 262
Topic #: 1

HOTSPOT -
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 orders a month over the next five years.
You have been instructed by your Team Lead to follow best practices for storage and performance in the utilization of SPARSE columns.
Which columns should you designate as SPARSE? To answer, mark each column as SPARSE or NOT SPARSE in the answer area.
Hot Area:

Show Suggested Answer

Suggested Answer:

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.

by TheSwedishGuy at Jan. 31, 2020, 5:29 p.m.

Comments

Submit Cancel

jolsca

4 years, 11 months ago

IsTaxable = SPARSE TaxAmount = not SPARSE because is a computed column Freight = SPARSE because the threshold is that 64% of data must be NULL in order to have net 40% of savings. However, it is justified for the savings to be between 20% and 40%, not exactly 40% in order to make the column sparse. And in Freight column the savings are lower than 40% but definitely higher than 20%

upvoted 3 times

KC

4 years, 10 months ago

Agreed. It freight may not make it to 40%, but it seems like it should fall between the 20-40% range.

upvoted 1 times

...

doamne

5 years, 4 months ago

I think TaxAmount is a computed column and should not be sparse: "A sparse column cannot be bound to a rule. Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE." https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-sparse-columns?view=sql-server-ver15

upvoted 4 times

Matus

5 years, 3 months ago

Exactly, computed column cannot be marked as SPARSE..

upvoted 1 times

...

DudeHere

5 years, 4 months ago

freight meets the 40% threshold if the entire data domain is listed. lol I'd stick with the answer.

upvoted 1 times

Iva10

5 years, 1 month ago

Freight threshold for smallmoney is 64%, so this column sholdnt be sparse. Also computed colmn TaxAmont also sholdnt.

upvoted 1 times

...

TheSwedishGuy

5 years, 6 months ago

Freight shouldnt be sparse, since it's mostly populated with non-null values.

upvoted 2 times

...

TheSwedishGuy

5 years, 6 months ago

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-sparse-columns?view=sql-server-ver15 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.

upvoted 1 times

...