You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. The Domain.com network contains a database server named Certkiller -DB01.
Certkiller -DB01 hosts a database named CK_News that stores news items from the company. The CK_News database has a table named NewsStories that stores the news story text. These news stories are written in Microsoft Word. A data capturer uses an application to copy the news story to the CK_News database. You need to redesign the NewsStories table so that you can implement full-text searches on it. You backup the CK_News database and drop the NewsStories table. What Transact-SQL statement should you use to recreate the table?
A.
CREATE TABLE NewsStories
(
NewsID bingint UNIQUE,
ReporterID int REFERENCES Employees.StaffID,
NewsText varchar(max),
Date smalldatetime NOT NULL
)
B.
CREATE TABLE NewsStories
(
NewsID bingint PRIMARY KEY CLUSTERED,
ReporterID int REFERENCES Employees.StaffID,
NewsText xml,
Date smalldatetime NOT NULL
)
C.
CREATE TABLE NewsStories
(
NewsID bingint PRIMARY KEY CLUSTERED,
ReporterID int REFERENCES Employees.StaffID,
NewsText varbinary(max),
FileType varchar(8),
Date smalldatetime NOT NULL
)
D.
CREATE TABLE NewsStories
(
NewsID bingint UNIQUE,
ReporterID int REFERENCES Employees.StaffID,
NewsText image,
FileExtension varchar(8),
Date smalldatetime NOT NULL
)
Explanation:
You should create the NewsID column as the primary key so that it can be used as the key column for the full-text search. Because the data capturer uses an application to copy the news story from Microsoft Word to the NewsStories table, you need to define the NewsText column as either varchar(max) or image. To enable Full-text indexing on a Microsoft Word document, you also need a column that stores the file extension so that the full-text search uses the correct filter to read the document.
Incorrect Answers:
A: Full-text search requires a key column that contains unique, non-null values. A UNIQUE constraint does not ensure that the column as no null values; it limits null values to one row. Because the data capturer uses an application to copy the news story from Microsoft Word to the NewsText table, you need to define the NewsText column as either varchar(max) or image and not varchar(max). Also, to enable Full-text indexing on a Microsoft Word document, you need a column that stores the file extension so that the full-text search uses the correct filter to read the document.
B: Because the Web master uses an application to copy the news story from Microsoft Word to the NewsStories table, you need to define the NewsText column as either varchar(max) or image and not xml. Also, to enable Full-text indexing on a Microsoft Word document, you need a column that stores the file extension so that the full-text search uses the correct filter to read the document.
D: Full-text search requires a key column that contains unique, non-null values. A UNIQUE constraint does not ensure that the column as no null values; it limits null values to one row.