You are a database developer on an instance of SQL Server 2008. You created the TestDetails table using the following CREATE TABLE statement:
CREATE TABLE TestDetails (
TestID int IDENTITY(1,1) PRIMARY KEY,
TestName nvarchar(10) NULL UNIQUE,
TestType char(1) CHECK (TestType = ‘A’ OR TestType = ‘B’),
TestAuthorID int SPARSE);
With default settings, which row would be successfully inserted into the TestDetails table?
A. a row that has an explicit value for the TestID
B. a row that has a NULL value for TestID
C. a row that has a non-NULL value for TestAuthorID
D. a row that has a value of ‘C’ for TestType
Correct Answer: C
Explanation/Reference:
The TestAuthorID is defined as a sparse column. When creating a table, sparse columns can be used to optimize the storage of NULL values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. With the given table, you could insert a NULL value or a non-NULL value for the TestAuthorID column. For example, it is possible to run this query (but only once because of the Unique constraint):
INSERT INTO TestDetails (TestName, TestType, TestAuthorID) VALUES (DEFAULT, DEFAULT, DEFAULT);
Sparse columns cannot be defined with a NOT NULL constraint. If you try to do this you will get the following message:
Msg 1731, Level 16, State 1, Line 1
Cannot create the sparse column ‘TestAuthorID’ in the table ‘TestDetails’ because an option or data type specified is not valid. A sparse column must be nullable and cannot have the ROWGUIDCOL, IDENTITY, or FILESTREAM properties. A sparse column cannot be of the following data types: text, ntext, image, geometry, geography, or user-defined type.
A row that has an explicit value for TestID would not be successfully inserted into the TestDetails table. By default, identity columns are assigned the next identity value when a record is inserted. Attempting to include an IDENTITY column in the column list and VALUES clause of an INSERT statement will result in the following error:
Msg 544, Level 16, State 1, Line 14Cannot insert explicit value for identity column in table ‘TestDetails’ when
IDENTITY_INSERT is set to OFF.
However, you can override this behavior and allow an IDENTITY column to be explicitly set. The following statement specifies that the TestDetails table should accept an explicit TestID value without generating an error:
SET IDENTITY_INSERT TestDetails ON;
A row that has a NULL value for TestID would not be successfully inserted into the TestDetails table because the TestID column is defined as the table’s primary key. A primary key must contain a unique, non-NULL value. If you attempted to insert a NULL value for the TestID, the statement would generate the following error:
Msg 339, Level 16, State 1, Line 14DEFAULT or NULL are not allowed as explicit identity values.
A row that has a value of ‘C’ for TestType would not be successfully inserted into the TestDetails table because the TestType column has a CHECK constraint.
CHECK constraints are enabled by default, and attempting to add a row that violates the CHECK constraint would generate an error similar to the following:
Msg 547, Level 16, State 0, Line 14The INSERT statement conflicted with the CHECK constraint
"CK__TestDetai__TestT__6CA31EA0". The conflict occurred in database "KIT3", table "dbo.TestDetails", column ‘TestType’.
