What should you do to guarantee availability of any recommendations suggested by the DTA during implementation
You are employed 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 uses a SQL Server 2005 database named CK_DATA, which is located on a server named Certkiller -DB01.
Domain.com also contains another SQL Server 2005 database server named Certkiller -DB02 on which a recent copy of CK_DATA exists. During routine monitoring you discover a high number of full scans on Certkiller -DB01. You come to a conclusion that extra indexes in CK_DATA are required. You have received instruction from the CIO to analyze the workload file by using DATA. You need to accomplish this without compromising the performance on Certkiller -DB01 during the analysis. You need to guarantee availability of any recommendations suggested by the DTA during implementation.
What should you do?
What type of view should you implement?
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. Domain.com has its headquarters in Chicago and branch offices in Miami, and San Francisco. The Domain.com network contains three database servers named Certkiller -DB01, Certkiller -DB02, and Certkiller -DB03. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. Transactional replication of the CK_Sales database is configured with Certkiller -DB01 as the Publisher and Certkiller -DB02 and Certkiller -DB03 are configured as Subscribers. Certkiller -DB01 is located at headquarters while Certkiller -DB02 is located in Miami and Certkiller -DB03 is located in San Francisco. The tables in the CK_Sales database are shown in the following database diagram.
The Customers table contains more than 200,000 rows. Domain.com uses a custom application to access data in the Customers table. Domain.com users in the Sales department at each office use the custom application to access and update data for the customers assigned to his or her office.
You want to improve database performance by implementing views on the Customers table.
exhibit What type of view should you implement?
What stored procedure you create to create a stored procedure that will return a list of sample chapters from
You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. The network contains a database server named Certkiller -DB01 that hosts a database named CK_Books. The CK_Books database has a table named SampleChapters that is used to store sample chapters from books published by Domain.com. The schema for the SampleChapters table is shown in the following exhibit:
The text for the sample chapters are stored in a column named SampleText. The SampleText column is defined as an xml data type column. The XML schema for the SampleText column is shown below:
<SampleChapter>
<BookTitle>title of book</BookTitle>
<Author>author of book</Author>
<ChapterText>
<ChapterTitle>title of chapter</ ChapterTitle >
<Intro>introduction or synopsis</Intro>
<Topic Title="topic title">topic text</Topic>
<Topic Title="next topic title">next topic text</Topic> </ChapterText>
</SampleChapter>
You need to create a stored procedure that will return a list of sample chapters from the Samples table. The returned data must include the book title and the sample chapter title but must not return any XML tags.
exhibit What stored procedure you create?
What should you do to partition the information in order to increase performance and optimize maintenance of t
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 administrating a SQL Server 2005 database named Certkiller -DB09.
Certkiller -DB09 contains a database named CK_Sales that stores sales transactions for the company. The table structure for the CK_Sales database is shown in the following exhibit:
The Invoices table contains more than 300 million rows of information. In this table some information is historical and some is current. You want to partition the information in order to increase performance and optimize maintenance of the CK_Sales database.
exhibit What should you do?
What should you do to add a key column to an existing clustered index on the OrderDetails 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_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.
You need to optimize the CK_Sales database to support a redesigned stored procedure. You need add a key column to an existing clustered index on the OrderDetails table but you must ensure that the table remains available to users while the index is built.
exhibit What should you do?
What should you do to provide maximum query performance?
You work as a 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 database named CK_Sales that is hosted on a database server named Certkiller -DB01.
The table structure for the CK_Sales database is shown in the following exhibit:
Domain.com users report that table scans on the Invoices table causes a slow query. The Invoices table is a large table that is used regularly. You investigate and discover that the query contains the following statement:
SELECT InvoiceID, InvoiceDate FROM Invoices WHERE DateDue = <value> You need to provide maximum query performance. However, the Invoice table should at all times remain available to users.
exhibit What should you do?
What should you do to enforce the values that the SalesPersonID column in the Invoices table will accept?
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. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.
Domain.com decides to switch to commission-based salaries for its Sales staff. The manager of the Sales department will run queries against the Invoices table to calculate the commission for each sales person. To ensure the success of the queries, you want to enforce the values that the SalesPersonID column in the Invoices table will accept.
exhibit What should you do?
What should you do to ensure that combination of the DateShipped, ProductID and InvoiceID is unique for each r
You work as the database administrator for an IT training company named 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. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.
A PRIMARY KEY column has been declared on the ProductID column of the Products table and the InvoiceID column of the Invoices table. You are creating a table named Inventory to track stock levels. You need to ensure that combination of the DateShipped, ProductID and InvoiceID is unique for each row in the Inventory table.
exhibit What should you do?
What should you do to this solution to be available for reuse in Transact-SQL statements and views?
You are work as a database administrator at Domain.com. All servers on the Domain.com network run Windows 2000 Server and all client computers run Windows 2000 Professional. The Domain.com network contains a SQL Server 2005 computer named Certkiller -DB02.
CERTKILLER-DB02 hosts a database named CK_Sales that stores the sales information for the company. The table structure for the CK_Sales database is shown in the following exhibit:
The Description column in the Products table is defined as an nchar column. Several values in the Description column contain preceding or trailing spaces. You want to implement a mechanism that will ignore preceding or trailing spaces when data from the Products table is retrieved. You want this solution to be available for reuse in Transact-SQL statements and views.
exhibit What should you do?
What should you do to the second trigger to execute once the first trigger is executed?
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. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the company. The tables in the CK_Sales database are shown in the following database diagram.
Several products listed in the Products table have been discontinued. You want to delete the data from the Products table that refer to the discontinued products. You also want to copy data from the Invoices table that are related to the discontinued products to a separate table named DiscontinuedProductInvoices. You decide to use triggers to accomplish this task. The first trigger will delete rows from the Products table while the second trigger will to copy data from the Invoices table to the DiscontinuedProductInvoices table. You want the second trigger to execute once the first trigger is executed.
exhibit What should you do?