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?
What should you do to ensure that the view cannot be affected by modifications to underlying table schemas?
You are work as a database administrator at Domain.com. Your duties at Domain.com include the administrating of the SQL Server 2005 computer named Certkiller -DB07.
Certkiller -DB07 hosts 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:
You are busy creating a view to join the Customers and Invoices tables. You want to ensure that the view cannot be affected by modifications to underlying table schemas.
exhibit What should you do?
What should you do to add this new column by using the least amount of effort?
You are 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 -DB08. Your duties at Domain.com include the administrating of Certkiller -DB08.
Certkiller -DB08 hosts 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:
You need to modify the Products table to add a new column named ProductName. You want a friendly name for each product will be stored in this column. The table currently contains data. The Marketing department has not yet created a friendly name for each product. You need to add this new column by using the least amount of effort.
exhibit What should you do?
What should you do to improve the performance of the stored procedures?
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. There is at least one index on each table in the CK_Sales database. The tables in the CK_Sales database are shown in the following database diagram.
Domain.com users in the Finance department run several stored procedures against he CK_Sales database to create month end reports. You have recently altered some of the stored procedures. How Finance department users complain that the month-end reports take longer to create. You need to improve the performance of the stored procedures.
exhibit What should you do? (Each correct answer presents part of the solution. Choose two.)