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.
Answer: 
Explanation:
Note:
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.
TaxAmount is Computed Column so it cannot by sparse.
Freight is smallmoney data type and have 40% nulls.
According to
https://msdn.microsoft.com/en-us/library/cc280604.aspx
Smallmoney have 4 bytes so 10 records will take 40 bytes.
Smallmoney sparse have 8 bytes so 6 (not null values) will take 48 bytes.
CREATE TABLE OrderData (
OrderID INT IDENTITY(1,1) Primary Key Clustered,
OrderDate SMALLDATETIME NOT NULL DEFAULT getdate(),
CustomerID INT,
IsTaxable INT sparse,
SubTotal SmallMoney DEFAULT (0),
TaxAmount AS (Case IsTaxable when 1 then SubTotal * .0875 else NULL END) ,
Freight Smallmoney,
OrderReturnedDate DATE,
OrderReturnedCustReason TEXT,
OrderReturnedEval Varchar(max)
)
I think we should make ‘Freight’ sparse as well. Bcz its understand that sparse columns do
cause hdd use. INT too 8 byets
Is taxable = SPARSE
Tax Amount = not SPARSE because is computed column
Freight is smallmonay
smallmoney nonsparse bytes = 4, sparse bytes = 8, BULL percentage = 64%
The NULL Percentage column indicates what percent of the data must be NULL for a net space savings of 40 percent.
In this case Freight = nonsparse.
Correct answer for sparse isTaxable only
[url=http://www.bagkakaku.com/vuitton_belt.html]スーパーコピー、スーパーコピーブランド(N級品)激安通販専門店世界一流ブランドコピー 財布、スーパーコピー 商品、激安ブランドコピー 。 ヴィトンコピー 、 ミョウミョウコピー 、シャネルコピー 、エル メスコピー 品格安通販。商品は全て最高な材料 と優れた技術で造られて、正規と比べて、品質が無差別です！人気ブランド..[/url]
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%
Computed column can not be sparse.
drop table OrderData
go
create table OrderData(
OrderID int identity(1,1) primary key clustered,
OrderDate smalldatetime not null default getdate(),
CustomerID int,
IsTaxable int,
SubTotal smallmoney default(0),
TaxAmount as (case IsTaxable when 1 then SubTotal*.0875 else null END),
Freight smallmoney)
drop table OrderData_sparse
go
create table OrderData_sparse(
OrderID int identity(1,1) primary key clustered,
OrderDate smalldatetime not null default getdate(),
CustomerID int,
IsTaxable int,
SubTotal smallmoney default (0),
TaxAmount as (case IsTaxable when 1 then SubTotal*.0875 else null END),
Freight smallmoney sparse)
declare @i int
select @i=1
while @i<50000
begin
if @i/2*2=@i
insert into OrderData_sparse(CustomerID,IsTaxable,SubTotal,Freight) values (@i,1,@i,@i)
else
insert into OrderData_sparse(CustomerID,IsTaxable,SubTotal) values (@i,1,@i)
select @i=@i+1
end
declare @i int
select @i=1
while @i sp_spaceused OrderData
2> go
name
rows reserved
data index_size unused
——————————————————————————–
———————————————— ——————– ———-
——– —————— —————— ——————
OrderData
49999 2376 KB
2328 KB 16 KB 32 KB
1> sp_spaceused OrderData_Sparse
2> go
name
rows reserved
data index_size unused
——————————————————————————–
———————————————— ——————– ———-
——– —————— —————— ——————
OrderData_sparse
49999 2568 KB
2536 KB 16 KB 16 KB
Freight should not be sparse.
“IsTaxable” – SPARSE
“TaxAmount” – NOT SPARSE:
“•Although a computed column can contain a sparse column, a computed column cannot be marked as SPARSE.”:
https://msdn.microsoft.com/en-us/library/cc280604.aspx
“Freight” – NOT SPARSE:
Only 40% data has NULL values. For storage performance in the utilization, the value mustbe greater than 64%
https://msdn.microsoft.com/en-us/library/cc280604.aspx