You need to design a solution to guarantee that a stored procedure is part of every transaction. What should you do?
A. Enable implicit transactions.
B. Enable Distributed Transaction Coordinator (DTC).
C. Set the NOCOUNT option to ON.
D Set the XACT_ABORT option to ON.
You have a server that runs SQL Server 2008. The server supports an online application. New products are constantly being added to a database stored on the server. You identify the following:
– Searches often take too long
– Users cannot search by using multiple criteria
– Users cannot adequately search product descriptions
– Most queries that search the product descriptions use “LIKE *%…%”‘ as the filter predicate
You need to provide a developer the ability to create and modify database diagrams from an existing database by using SQL Server Management Studio. The solution must minimize the amount of permissions assigned to the developer. What should you do?
A. Add the developer to the sysadmin role.
B. Add the developer to the db_owner role.
C. Grant the developer the CREATE TABLE permission only.
D. Grant the developer the CREATE SCHEMA permission only.
Your company has four offices. Each office contains a server that runs SQL Server 2008. All corporate data is stored in a central location. You need to create a reporting solution. The solution must meet the following requirements:
– Minimize hardware costs
– Minimize administrative effort
– Minimize the time it takes to generate the reports
– Enable users to run reports locally, even if a WAN link fails
What should you do?
A. Implement database mirroring.
B. Implement log shipping.
C. Create horizontally partitioned tables and a federated database.
D. Implement a geographically dispersed SQL Server 2008 cluster.
You need to design a database solution that meets the following requirements:
– Supports different types of databases
– Provides reliable messaging between databases
– Provides data workload distribution across several databases
Which SQL Server component should you use?
A. SQL Server Agent
B. SQL Mail
C. Notification Services
D. Service Broker
You are a database developer. You plan to design a database solution by using SQL Server 2008. The database has a table named Sales. The Sales table contains 10 million rows. You discover that the following query takes a long time to execute.
FROM Sales AS s
JOIN Country AS c
ON s.Country_id = c.Country_id
AND c.Country_name = ‘USA’
A summary of the execution plan is as shown in the following code segment. |–Hash Match(Inner Join, HASH:
([s].[Country_id]) = ([c].[Country_id]) |–Clustered Index Scan(OBJECT:([Country].[PK_Country_Country_id] AS
[c]) |–Clustered Index Scan(OBJECT:([Sales].[PK_Sales_Sale_id] AS [s]))
You need to ensure that the query retrieves data in minimum possible time.
What should you do?
A. Modify the query to use a loop join hint.
B. Modify the query to use a merge join hint.
C. Create a nonclustered index in the Country_id column of the Sales table.
D. Create a nonclustered index in the Country_name column of the Country table.