You need to identify the most likely cause for the problems by analyzing the execution plan
You are a database developer. You plan to design a database solution by using SQL Server
2008. A database will contain 10 tables that are used to generate reports. Data in the tables
ranges from 50,000 to 100,000 records. During a query execution that joins four tables, you
discover the following problems: ·The size of the tempdb database grows considerably.
·The query execution time is excessive. You need to identify the most likely cause for the
problems by analyzing the execution plan. What should you do?
You need to ensure that merge replication will continue to function after the upgrade
You are planning to upgrade a database application that uses merge replication. The table
currently has a column type of UNIQUEIDENTIFIER and has a DEFAULT constrain that
uses the NEWID() function. A new version of the application requires that the FILESTREAM
datatype be added to a table in the database. The data type will be used to store binary
files. Some of the files will be larger than 2 GB in size. While testing the upgrade, you
discover that replication fails on the articles that contain the FILESTREAM data. You find
out that the failure occurs when a file object is larger than 2 GB. You need to ensure that
merge replication will continue to function after the upgrade. You also need to ensure that
replication occurs without errors and has the best performance. What should you do? (More
than one answer choice may achieve the goal. Select the BEST answer.)
Which parameter should you set in the dtexec command line utility?
You build and deploy a SQL Server 2008 Integration Services (SSIS) package.
During package development, the XML configuration is not specified.
You need to apply the configuration file to the deployed package stored in file system.
Which parameter should you set in the dtexec command line utility?
Which code segment should you add to line 14?
You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code
segment. (Line numbers are included for reference only.)
The procedure can be called within other transactions.
You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table
succeeds, the modification is retained even if the insert into the Audit.Log table fails.
Which code segment should you add to line 14?
Which type of variable should you create?
HOTSPOT
You are an ETL developer for a mortgage company. In your application you have two tables
named Vendor and Loan.
Vendor sample data is shown in the following table:
Loan sample data is shown in the following table:
You need to build a SQL Server 2008 Integration Services (SSIS) package that meets the
following requirements:
The package should be able to generate the Excel files dynamically.
Each file should have the vendor name as the prefix and the date as the suffix (e.g.,
VendorA_DDMMYYYY.xlsx).
All the vendor names should be added to a variable and loop through the variable to
generate and load separate files for each vendor dynamically.
Which type of variable should you create? (To answer, select the appropriate option in the
dialog box in the answer area.)
Which command line utility should you use?
You are implementing a disaster recovery strategy for SQL Server 2008 Reporting Services
(SSRS).
You want to be able to restore the report catalog database in the event of a failure.
You need to back up the encryption key.
Which command line utility should you use?
Which of the following is the best allocation unit size you should use?
You administer a SQL Server 2008 infrastructure. You plan to deploy a new SQL Server
2008 multinode failover cluster. The failover cluster uses a storage area network (SAN) that
will use redundant array of independent disks (RAID) level 5. You need to format the logical
unit numbers (LUNs) for optimal performance of the database data files. Which of the
following is the best allocation unit size you should use?
Which role should you use?
You maintain a SQL Server 2008 Reporting Services instance.
Some users need to view and run reports in the Eastern Region folder on the Report Server.
You need to assign these users the minimum permissions required.
Which role should you use?
Which three components are you required to use?
You are working as reporting administrator for a product development company. The
company uses SQL Server 2008 Reporting Services (SSRS) deployed in native mode.
You need to deploy multiple reports via InstallShield.
Which three components are you required to use? (Each correct answer presents part of the
solution. Choose three.)
You need to ensure that the query executes as quickly as possible
You are a database developer. You plan to design a database solution by using SQL Server
2008. Account managers in your company store order data in a database. Your company
requires a list of customers for each account manager. The list must be sorted in the
descending order of the order amount. You create a query that generates the list at the end
of each month. You need to ensure that the query executes as quickly as possible. What
should you do?