Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1

Question 1

You use Microsoft SQL Server 2012 to develop a database application. You create two tables by using the following table definitions.

Which six Transact-SQL statements should you use? (To answer, move the appropriate SQL statements from the list of statements to the answer area and arrange them in the correct order.)
Build List and Reorder:

Correct Answer:

Explanation:
--Burgos - NO

Question 2

You administer a Microsoft SQL Server 2012 instance. After a routine shutdown, the drive that contains tempdb fails. You need to be able to start the SQL Server. What should you do?

A. Configure SQL Server to bypass Windows application logging.

B. Modify tempdb location in startup parameters.

C. Start SQL Server in single-user mode.

D. Start SQL Server in minimal configuration mode.

Correct Answer: D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 3

Question 4

You administer a Microsoft SQL Server 2012 database. You need to ensure that the size of the transaction log file does not exceed 2 GB. What should you do?

A. Execute sp_configure 'max log size', 2G.

B. use the ALTER DATABASE...SET LOGFILE command along with the maxsize parameter.

C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.

D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.

Correct Answer: D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 5

You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema. You need to achieve this goal by using the minimum amount of administrative effort. What should you do?

A. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.

B. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.

C. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.

D. Deny Delete permissions on each table in the Sales schema for each user.

Correct Answer: C

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 6

You have three tables that contain data for vendors, customers, and agents. You create a view that is used to look up telephone numbers for these companies. The view has the following definition: You need to ensure that users can update only the phone numbers by using this view. What should you do?

A. Create an INSTEAD OF UPDATE trigger on the view.

B. Create an AFTER UPDATE trigger on the view.

C. Alter the view. Use the EXPAND VIEWS query hint along with each SELECT statement.

D. Drop the view. Re-create the view by using the SCHEMABINDING clause, and then create an index on the view.

Correct Answer: A

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 7

You administer a single server that contains a Microsoft SQL Server 2012 default instance on which several production databases have been deployed. You plan to install a new ticketing application that requires the deployment of a database on the server. The SQL login for this application requires sysadmin permissions. You need to ensure that the login for the ticketing application cannot access other production databases. What should you do?

A. Install a new default SQL Server instance on the server.

B. Use the SQL Server default instance and enable Contained Databases.

C. Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role.

D. Install a new named SQL Server instance on the server.

Correct Answer: D

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 8

You use a Microsoft SQL Server 2012 database. You want to create a table to store Microsoft Word documents. You need to ensure that the documents must only be accessible via Transact-SQL queries. Which Transact-SQL statement should you use?

A. CREATE TABLE DocumentStore AS FileTable

B. CREATE TABLE DocumentStore
(
[Id] INT NOT NULL PRIMARY KEY,
[Document] VARBINARY(MAX) NULL
)
GO

C. CREATE TABLE DocumentStore
(
[Id] hierarchyid,
[Document] NVARCHAR NOT NULL
)
GO

D. CREATE TABLE DocumentStore
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[Document] VARBINARY(MAX) FILESTREAM NULL
)
GO

Correct Answer: B

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 9

HOTSPOT You administer a Microsoft SQL Server 2012 database. The database contains a table that has the following definition: You want to export data from the table to a flat file by using the SQL Server Import and Export Wizard. You need to ensure that the following requirements are met:

The first row of the file contains the first row of data.
Each record is of the same length.
The date follows the U.S. date format.
The file supports international characters.
What should you do? (To answer, simply select the option or options in the answer area that you would configure.)
Hot Area:

Correct Answer:

Explanation:
--Burgos - NO
Reference: http://msdn.microsoft.com/en-us/library/ms178804.aspx Reference: http://msdn.microsoft.com/en-us/library/ms187828.aspx

Question 10

You administer a single Microsoft SQL Server instance on a two-node failover cluster that has nodes named Node A and Node B.
The instance is currently running on Node A.
You want to patch both Node A and Node B by using the most recent SQL Server Service Pack. You need to ensure that the following requirements are met:
Both nodes receive the update.
Downtime is minimized.
No data is lost.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the
list of actions to the answer area and arrange them in the correct order.)
Build List and Reorder:

Correct Answer:

Explanation:
--Burgos - YES (I dont remember if was with NodeA or NodeB active)
Reference: http://technet.microsoft.com/en-us/library/ms191009.aspx Reference: http://technet.microsoft.com/en-us/library/ms191295.aspx

Question 11

You use Microsoft SQL Server 2012 to write code for a transaction that contains several statements. There is high contention between readers and writers on several tables used by your transaction. You need to minimize the use of the tempdb space. You also need to prevent reading queries from blocking writing queries. Which isolation level should you use?

A. READ COMMITTED SNAPSHOT

B. SERIALIZABLE

C. SNAPSHOT

D. REPEATABLE READ

Correct Answer: A

Explanation: Only visible for Actualtests4sure members. You can sign-up / login (it's free).

Question 12

Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 - 70-457 Exam Practice Test

Latest Update

Useful Links

Contact Us