What should you do?
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 B01 that has two RAID-5 arrays for storing data files. Certkiller -DB01 hosts a database named CK_Products that stores data on the products sold by Domain.com.
Domain.com wants to allow prospective customers search for information in the CK_Products database via a Web-based interface. The prospective customers will use key words and phrases to search the CK_Products database. You decide to implement a Full-Text Search on the database. You install the Full-Text Search component on Certkiller -DB01 and start the Microsoft Search service. You then create the required catalog and full-text index. You modify the full-text index by running the following Transact-SQL statement:
ALTER FULLTEXT INDEX ON CK_Products.Description
ADD (shortName)
WITH NO POPULATION
Later you discover that you cannot perform full-text queries against the shortName column. You need to ensure that full-text queries can be performed against the shortName column.
What should you do?
What Transact-SQL statements should he use?
You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com.
Domain.com makes use of a SQL Server 2005.
A Domain.com employee named Andy Booth is appointed as a junior technician in the Research and Development department. You have received a query from various users stating that the report execution is slow. You instruct Andy Booth to troubleshoot the error. Andy Booth discovers that some queries do not use optimal execution plans.
It has come to your attention that some optimizer statistics are missing while others are out of date. Andy Booth needs to solve this problem in order for the reports to be executed faster. Andy Booth needs to identify the Transact-SQL statements that need to be used.
What Transact-SQL statements should he use? (Choose all that apply)
What Transact-SQL statement should you use to recreate the table?
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?
What should you do?
You are hired as a database consultant at Domain.com. The Domain.com network contains a SQL Server database server named Certkiller -DB01. Certkiller -DB01 host a database named CK_Sales that stores line items from Domain.com’s online sales transactions. Domain.com processes about 40,000 transactions every day. An application that processes the sales transactions requires a clustered index on a column named TransID.
You need to create a table that supports an efficient reporting solution that queries the transactions by date.
What should you do? (Each correct answer presents part of the solution. Choose TWO.)
What actions should you execute?
You are employed as a database administrator at Domain.com. Your duties at Domain.com encompass administrating a SQL Server 2005 database named Certkiller -DB03.
The Domain.com Web site has a page for clients to send the comments about its products and the company. You received instruction from the CIO to use Certkiller -DB03 to store the comments in the Comments column of a table named CKFeedback. You implement full-text searching in order to run reports on the comments.
What actions should you execute? (Choose all that apply)
What statement should you use?
You work as the database developer for Domain.com. The Domain.com network contains a database server named Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. Domain.com Sales Representatives should be allowed to check the current commissions due to them.
You need to optimize the indexing strategies for the CK_Sales database. You need to design the indexes for the Orders table. The following query is frequently executed, though it is not the most commonly executed query.
SELECT Salesrepresentative, SUM(Commission)
FROM Orders
WHERE Date BETWEEN @startDate AND @endDate
GROUP BY Salesrepresentative
ORDER BY Salesrepresentative
You need to use the appropriate statement to create the best index to accommodate this query.
What statement should you use?
What should you do?
You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties at Domain.com include managing the database schema for an order entry application in a SQL Server 2005 database named Certkiller -DB04. A Domain.com employee named Mia Hamm works is part of the design review committee. Mia Hamm requests that a new column named CK_Commission to the CK_Product table. The actual commission rates for the products are not known at present. Therefore, every product has a default commission rate of 10 percent. The table allows you to modify the rate if required. You have received instruction from the CIO to add the new column.
You need to configure the table to assign the default value as efficiently as possible.
What should you do?
What should you do?
You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties include administrating a SQL Server 2005 database named Certkiller -DB04. You have received instruction from the CIO that Certkiller -DB04 will support a new Web-based application. This Web-based application will handle approximately 1,500 users simultaneously. The application must display the results of calculation-intensive operations like calculating the instalment and amortization schedules as quick as possible.
What should you do?
What should you do?
You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties include administrating a SQL Server 2005 database named Certkiller -DB04. It has come to your attention that schema changes were made on Certkiller -DB04. Due to these changes on Certkiller -DB04 your Web site stopped functioning. It is however unclear who made the changes. You have received mandate from the CIO that all changes to the database schema be tracked.
What should you do?
What should you do?
You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. Your duties include administrating Certkiller -DB01. Certkiller -DB01 is hosting Domain.com’s commercial Web sites. You are creating a database on Certkiller -DB01 to support a new Web-based application that will handle up to 2,000 simultaneous users. The application that will access this database must quickly display the results of calculation-intensive operations. You need to ensure that the database processes calculations as quickly and efficiently as possible.
What should you do?