You need to ensure that the solution provides results in the minimum possible time
You are a database developer. You plan to design a database solution by using SQL Server
2008. The database will contain information on retail sales transactions of more than 500
stores. The marketing department uses the solution to analyze daily sales patterns for each
store. Users report that the solution takes a long time to retrieve the required data. You
need to ensure that the solution provides results in the minimum possible time. What should
you do?
You need to ensure that only the Human Resources group can view and modify reports in the Human Resources Repo
You manage the security in SQL Server 2008 Reporting Services (SSRS). The report server
contains a folder named Human Resources Reports.
All groups can view and modify reports in any folder.
You need to ensure that only the Human Resources group can view and modify reports in
the Human Resources Reports folder.
What should you do? (Each correct answer presents part of the solution. Choose two.)
What should you create for each application?
You develop a Microsoft SQL Server 2012 database. The database is used by two web applications
that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly
while still providing access to the required data.
You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications’ ability to access data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the applications.
What should you create for each application?
Which type of index should you use?
You have a table that contains 5 million rows. The table has a clustered index. You plan to
add an additional index on a column that contains 80 percent null values. The column is
used extensively in WHERE clauses. You need to implement an index strategy for the
column. The solution must minimize the amount of storage space required for the index.
Which type of index should you use?
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 CustomerIdvalue
set to 1 in the following XML format.
Which Transact-SQL query should you use?
Which two actions should you perform?
You are a database developer. You plan to design a database solution by using SQL Server
2008. The database supports a Web site and captures user interactions. These interactions
are stored in the Activity table of the User_Activity database. Data older than six months is
archived to the Activity table of the Archive_Activity database on a different instance of SQL
Server 2008. The structure of the Activity table is as shown in the following table. You plan
to design a solution that allows a single query to generate a report that summarizes user
interactions for the last 12 months. You need to ensure that the solution is implemented.
Which two actions should you perform? (Each correct answer presents part of the solution.
Choose two.)
You need to recommend an indexing solution to meet the following requirements…?
You have a table named Books that contains information about books. Books has the
columns in the following table. You plan to create several queries that will filter on Title and
ISBN. The queries will return values from Title, ISBN, and Description. You need to
recommend an indexing solution to meet the following requirements: ·Minimize the amount
of time required to return the results of the planned queries ·Minimize the number of indexes
What should you recommend?
The two warehouses are named Warehouse A and Warehouse
You are a database developer. You plan to design a database solution by using SQL Server
2008. The database supports a warehousing application and contains data from two
warehouses in a table named Product. The Product table contains a warehouse indicator
field named warehouse_id. The two warehouses are named Warehouse A and Warehouse
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.
<CUSTOMERS Name=”Customer A” Country=”Australia”>
<ORDERS OrderID=”1″ OrderDate=”2001-01-01″ Amount=”3400.00″ />
<ORDERS OrderID=”2″ OrderDate=”2002-01-01″ Amount=”4300.00″ />
</CUSTOMERS>
Which Transact-SQL query should you use?
You need to ensure that the stored procedure is consistently executed in the minimum possible time
You are a database developer. You plan to design a database solution by using SQL Server
2008. You plan to design a complex multi-statement stored procedure in the following
manner. CREATE PROCEDURE Sales.GetCustomerActivity @StartDate datetime AS
SELECT order_id, order_date, customer_id FROM Sales.Orders WHERE order_date >=
@StartDate … On testing, you discover that the stored procedure occasionally takes a
longer than expected time to execute. You discover that this degradation is caused by the
first statement in the stored procedure. You need to ensure that the stored procedure is
consistently executed in the minimum possible time. What should you do?