Q1. You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows.
You need to recommend a repeatable method to migrate the database to SQL Azure.
Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Create a SQL Server Integration Services (SSIS) package, and then run the package.
B. Back up the database, and then restore the database.
C. Extract a data-tier application, and then import the application.
D. Generate scripts to create all of the all database objects and all of the data, and then execute the scripts by using SQL Azure.
Answer: A
Q2. Your company has a main office in London and a branch office in New York.
Your network contains a server named Server5 that has SQL Server 2012 installed. Server5 contains a database name ContentDB and a table named ContentTable.
You add an additional server named Server9 that runs SQL Server 2012.
You need to create a distributed partitioned view. The solution must minimize the amount of network traffic.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Create the view on Server5.
B. Add Server9 as a linked server.
C. Create the view on Server9.
D. Add the Customers table to Server9.
E. Add Server9 as a Distributor.
F. Remove the Customers table from Server5.
Answer: ABCD
Q3. You use SQL Server 2012 to maintain the data used by the applications at your company.
You plan to create a table named Table1 by using the following statement. (Line numbers are included for reference only.)
You need to ensure that Table1 contains a column named UserName. The UserName column will:
Store string values in any language.
Accept a maximum of 200 characters.
Be case-insensitive and accent-insensitive.
Which code segment should you add at line 03?
A. UserName nvarchar(200) COLLATE Latin1_General_CS_AS NOT NULL,
B. UserName varchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
C. UserName varchar(200) COLLATE Latin 1_General_CS_AS NOT NULL,
D. UserName nvarchar(200) COLLATE Latin1_General_CI_AI NOT NULL,
Answer: D
Q4. You have a server named Server1 that has 2 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1. Each instance will have multiple databases.
You need to recommend a method to allocate processor time to each database.
What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Resource Governor
B. Max Degree of Parallelism
C. Windows System Resource Manager (WSRM)
D. Processor affinity
Answer: A
Q5. You have a SQL Server instance on a server named Server1.
You need to recommend a solution to perform the following tasks every week:
Rebuild the indexes by using a new fill factor.
Run a custom T-SQL command.
Back up the databases.
What should you recommend?
More than one answer choice may achieve the goal. Select the BEST answer.
A. A trigger
B. An alert
C. A maintenance plan
D. Windows PowerShell
E. A system policy
Answer: C
Q6. You need to modify the function in CountryFromID.sql to ensure that the country name is returned instead of the country ID.
Which line of code should you modify in CountryFromID.sql?
A. 04
B. 05
C. 06
D. 19
Answer: D
Q7. You need to implement a solution that meets the locking requirements.
Which line of code should you modify?
A. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (UPDLOCK)
B. Change line 09 in usp_GetOpenings to: FROM Openings o (ROWLOCK)
C. Change line 07 in usp_UpdateOpening to: UPDATE Openings WITH (READPAST)
D. Change line 09 in usp_GetOpenings to: FROM Openings o (NOLOCK)
Answer: D
Q8. You need to resolve the performance issues of the usp_ExportOpenings stored procedure. The solution must minimize the amount of hard disk space used.
Which statement should you execute on DB1?
A. EXEC sp_dboption 'DB1', 'auto create statistics', 'TRUE'
B. CREATE INDEX IX_Exp_Openings ON Openings(PostDate, FilledDate) INCLUDE
(Description, Title, Salary);
C. CREATE INDEX IX_Exp_Openings ON Openings(PostDate) INCLUDE (Description,
Title,
Salary) WHERE FilledDate IS NULL;
D. EXEC sp_recompile 'usp_ExportOpenings'
Answer: C
Q9. You have a database named DB1.
You plan to configure change data capture on the existing tables in DB1.
The database file settings for the DB1 are shown in the exhibit. (Click the Exhibit button.)
You need to minimize disk contention caused by change data capture.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. Increase the autogrowth value of the database file.
B. Set the database recovery model to simple.
C. Increase the autogrowth value of the log file.
D. Configure change data capture to use to a secondary filegroup.
Answer: D
Q10. You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.
What should you do?
A. Execute sp_help usp_GetOrdersByProduct\
B. Add WITH (FORCESEEK) to line 69 in usp.GetOrdersByProduct.
C. Add WITH RECOMPILE to line 64 in usp.GetOrdersByProduct.
D. Execute sp_recompile usp.GetOrdersByProduct'.
Answer: B