You are a database administrator on an instance of SQL Server 2008. You want to create a table named InvTransaction that contains the following information:
• TranDate - A transaction date and time
• ProductID - A product identifier
• TranCOA - A transaction code corresponding to the company’s chart of accounts
• LocationID - A warehouse location identifier
• BinID - A bin location identifier
• Quantity - A quantity
• TranType - A transaction type
• TranDetails - A detailed transaction description
The data will be imported from a legacy system, and the majority of transactions will not have values to be stored in the LocationID and BinID columns. You want to minimize storage requirements for the table.
What should you do?
A. Set the text in row table option to OFF.
B. Use FILESTREAM storage.
C. Implement the LocationID and BinID columns as sparse columns.
D. Create a column set.
Correct Answer: C
Explanation/Reference:
When creating a table, sparse columns can to optimize the storage of NULL values. In this scenario, the majority of rows that will be imported will not have LocationID and BinID values. You can minimize storage requirements by defining these columns as sparse columns. For example, you could use the following statement to create the InvTransaction table with LocationID and BinID as sparse columns:
CREATE TABLE InvTransaction (
TranDate datetime, ProductID int,
ProductID int, TranCOA int,
TranCOA int, LocationID int SPARSE NULL,
LocationID int SPARSE NULL, BinID int SPARSE NULL,
BinID int SPARSE NULL, Quantity smallint,
Quantity smallint, TranType char(2),
TranType char(2), TranDetails varchar(max));
You should not create a column set. Column sets can be used with a table that contains sparse columns to be able to return all sparse column values as XML, but would not be required in this scenario because you are not returning data in sparse columns.
You should not use FILESTREAM storage. FILESTREAM storage is implemented to store large binary objects as files on the file system, such as image or video files, and manage them using Transact-SQL. FILESTREAM data can also be accessed using Win32 APIs. FILESTREAM storage is tightly integrated with most database functionality, including backup and recovery. When you take a database backup, FILESTREAM storage is also backed up unless you override this functionality by performing a partial backup. To create a table that can store FILESTREAM data, you create a table that contains a column of the varbinary(max) data type and include the FILESTREAM attribute.
You should not set the text in row table option to OFF. The text in row option is used to control whether text, ntext, or image columns are stored in the data row. You can also use the large value types out of row option to specify whether large value types, such as varchar(max), nvarchar (max), and varbinary(max), are stored in the data row.
