70-457 by Microsoft Valid Free Exam Practice Test

Question 1

You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to prevent users from disabling server audits in Server01. What should you create?

A. a Policy B. a Resource Pool C. a SQL Profiler Trace D. a Database Audit Specification E. an Extended Event session F. a Server Audit Specification G. an Alert

Correct Answer: A

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

Question 2

You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

A. CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL); B. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL, CustomerName varchar(255) NOT NULL, CONSTRAINT UQ_Customer UNIQUE CLUSTERED (SourceID, CustomerID)); C. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL PRIMARY KEY CLUSTERED, CustomerName varchar(255) NOT NULL); D. CREATE TABLE Customer (SourceID int NOT NULL PRIMARY KEY CLUSTERED, CustomerID int NOT NULL UNIQUE, CustomerName varchar(255) NOT NULL);

Correct Answer: B

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

Question 3

You administer a Microsoft SQL Server 2012 server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting database is only updated from the transactional database. The recovery model and
backup schedule are configured as shown in the following table:

One of the hard disk drives that stores the reporting database fails at 16:40 hours. You need to ensure that
the reporting database is restored. You also need to ensure that data loss is minimal.
What should you do?

A. Perform a point-in-time restore. B. Perform a page restore. C. Restore the latest full backup. Then, restore the latest differential backup. D. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup. E. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup. F. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup. G. Perform a partial restore. H. Restore the latest full backup.

Correct Answer: C

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

Question 4

Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID parameter. You plan to build a single process for each employee that will execute the stored procedure based on the country of residence. Which approach should you use?

A. a recursive stored procedure B. Cursor C. Trigger D. An UPDATE statement that includes CASE E. The foreach SQLCLR statement

Correct Answer: B

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

Question 5

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed
column that references a lookup table by using an INNER JOIN against another table.
What should you do?

A. Add a default constraint to the computed column that implements hard-coded CASE statements. B. Add a default constraint to the computed column that implements hard-coded values. C. Reference a user-defined function within the computed column. D. Create a BEFORE trigger that maintains the state of the computed column.

Correct Answer: C

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

Question 6

You create a stored procedure that will update multiple tables within a transaction. You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back. Which Transact-SQL statement should you include at the beginning of the stored procedure?

A. BEGIN B. TRY C. SET ARITHABORT ON D. SET XACT_ABORT OFF E. SET ARITHABORT OFF F. SET XACT_ABORT ON

Correct Answer: F

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

Question 7

Question 8

You administer a Microsoft SQL Server 2012 database. You provide temporary securityadmin access to User1 to the database server. You need to know if User1 adds logins to securityadmin. Which server-level audit action group should you use?

A. SERVER_PRINCIPAL_IMPERSONATION_GROUP B. SERVER_STATE_CHANGE_GROUP C. SUCCESSFUL_LOGIN_GROUP D. SERVER_ROLE_MEMBER_CHANGE_GROUP

Correct Answer: D

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

Question 9

You administer two instances of Microsoft SQL Server 2012. You deploy an application that uses a database on the named instance. The application is unable to connect to the database on the named instance. You need to ensure that the application can connect to the named instance. What should you do?

A. Configure the named SQL Server instance to use an account that is a member of the Domain Admins group. B. Open port 1433 on the Windows firewall on the server. C. Configure the application as data-tiered. D. Start the SQL Server Browser Service.

Correct Answer: D

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

Question 10

You use Microsoft SQL Server 2012 to develop a database application. Your application sends data to an NVARCHAR(MAX) variable named @var. You need to write a Transact-SQL statement that will find out the success of a cast to a decimal (36,9). Which code segment should you use?select

A. SELECT
CASE
WHEN convert(decimal(36,9), @var) IS NULL
THEN 'True'
ELSE 'False'
END
AS BadCast B. BEGIN TRY
SELECT convert(decimal(36,9), @var) AS Value, 'True' AS BadCast
END TRY
BEGIN CATCH
SELECT convert(decimal(36,9), @var) AS Value, 'False' AS BadCast
END CATCH C. SELECT
IIF(TRY_PARSE(@var AS decimal(36,9)) IS NULL, 'True', 'False')
AS BadCast D. TRY( SELECT convert(decimal(36,9), @var) SELECT 'True' AS BadCast
)
CATCH(
SELECT 'False' AS BadCast
)

Correct Answer: C

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

Question 11

You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?

A. Update statistics on SalesOrderId on both tables. B. Use a FORCESEEK hint in the query. C. Add a clustered index on SalesOrderId in SalesOrderHeader. D. Use a FORCESCAN hint in the query.

Correct Answer: A

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

Question 12

You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. Which type of user should you create?

A. login mapped to a virtual account B. SQL user without login C. Domain user D. User mapped to an asymmetric key

Correct Answer: C

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

Question 13

You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B.
A single instance of SQL Server is installed on the cluster. An additional node named Node C has been added to the existing cluster. You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?

A. Use Node B to install SQL Server on Node B. Use Node A to install SQL Server on Node C. C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C. D. Run the New SQL Server stand-alone installation Wizard on Node C.

Correct Answer: C

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

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