PrepAway - Latest Free Exam Questions & Answers

Author: seenagape

Which code segment should you use?

CORRECT TEXT
You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)

You have the following query:

You need to recreate the query to meet the following requirements:
Reference columns by using one-part names only.
Sort aggregates by SalesTerritoryID, and then by ProductID.
Order the results in descending order from SalesTerritoryID to ProductID.
The solution must use the existing SELECT clause and FROM clause.
Which code segment should you use?
To answer, type the correct code in the answer area.

Which Transact-SQL query should you use?

You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as
shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value
set to 1 in the following XML format:
<row OrderId=”1″ OrderDate=”2000-01-01T00:00:00″ Amount=”3400.00″ Name=”Customer
A” Country=”Australia” />
<row OrderId=”2″ OrderDate=”2001-01-01T00:00:00″ Amount=”4300.00″ Name=”Customer
A” Country=”Australia” />
Which Transact-SQL query should you use?

which object should be modified?

You are a database developer and you have about two years experience in creating
business Intelligence (BI) by using SQL Server2008.
Now you are employed in a company named NaproStar which uses SQL Server2008, and
you are in charge of a SQL Server 2008 Analysis Services (SSAS) database.
A Duplicate Key error occurs when you process the Analysis Services database.
As the technical support, you have to solve this problem. In order to make the error condition
be reported and continue processing, the ErrorConfiguration property has to be modified.
So which object should be modified?

what action should you perform to achieve that goal?

You are a database developer and you have about two years experience in creating
business intelligence (BI) solutions by using SQL Server2008.
Now you are employed in a company which is named NaproStar and you are in charge of a
SQL
Server 2008 Analysis Services (SSRS) database for the company.
To meet the business requirements, a new measure group is created in one of the cubes.
A program with partitions maintained is on the production copy of the cube.

Now you get an order from the CIO that you should have the cube definition updated on the
production server, besides, you should not rewrite any existing partitions.
So what action should you perform to achieve that goal?

You need to improve the performance of the application by reducing the I/O performance issue

You are the database administrator for a SQL Server that hosts an application defined as
follows: The database consists of 4 data files and one log file. The physical server has 16
CPUs and 64 GB of memory. Hardware non-uniform memory access (NUMA) is not
available on this server platform. Users of the application report that it is responding slowly.
You use SQL Server Profiler to find out that the I/O performance issue is related to the
lazywriter process. You need to improve the performance of the application by reducing the
I/O performance issue. What should you do? (More than one answer choice may achieve
the goal. Select the BEST answer.)

which one would be used?

You are a database developer and you have about two years experience in creating
business intelligence (BI) solutions by using SQL Server2008.
Now you are employed in a company which is named NaproStar and you are in charge of a
SQL
Server 2008 Analysis Services (SSRS) instance for the company.
According to your plan, a Dynamic Management View (DMV) will be used to troubleshoot
query performance.
Now you get an order from your manager, according to his requirements, query sessions
that execute a query for more than 30,000 ms should be identified.
So of the following DMV, which one would be used?

what action should you perform?

You are a database developer and you have about two years experience in creating
business Intelligence (BI) by using SQL Server2008.
Now you are employed in a company named NaproStar which uses SQL Server2008. There
are two servers in your company.

One is a production server and another one is a development server.
Now you’re in charge of a SQL Server 2008 Analysis Services (SSAS) database on a
production server.
The database has a cube. New functionality has been added to the cube on a development
server.
Now you receive an order from your company CIO, according to the requirement of the CIO,
the new functionality has to be deployed from the development server to the production
server.
You have to accomplish this task and reduce the effect on the users to the least. So what
action should you perform?

what action should you perform?

You are a database developer and you have about two years experience in creating
business Intelligence (BI) by using SQL Server2008. Now you are employed in a company
named NaproStar which uses SQL Server2008. You work as the technical support. The
package includes several data flows. Now you receive an e-mail from your company CIO,
according to his requirement, you have to make use of the Business Intelligence
Development Studio to monitor the data that passes through each path in the data flow.
Besides this, the company CIO also wants the data flow logic is retained. You have to
achieve this goal. So what action should you perform?

You need to ensure that the server processes database queries as fast as possible

You are designing a new SQL Server instance that will support a Web application. The Web
application is hosted on a Web farm that contains 32 nodes. The server has 128 GB of
memory and 16 processor cores. The application contains two databases and supports both
online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
When testing the application, you discover that some queries run extremely slow and some
queries run very fast. You need to ensure that the server processes database queries as
fast as possible. What should you do? (More than one answer choice may achieve the goal.
Select the BEST answer.)