Microsoft 70-457 Online Practice Questions and Exam Preparation

Microsoft 70-457 Online Questions & Answers

• Question 31:

  You administer a Microsoft SQL Server 2012 server.

  When transaction logs grow, SQL Server must send an email message to the database administrators.

  You need to configure SQL Server to send the email messages.

  What should you configure?

  A. SQL Mail
  B. An Extended Events session
  C. Alerts and operators in SQL Server Agent
  D. Policies under Policy-Based Management
  C. Alerts and operators in SQL Server Agent

• Question 32:

  You administer a Microsoft SQL Server 2012 instance that has several SQL Server Agent jobs configured.

  When SQL Server Agent jobs fail, the error messages returned by the job steps are truncated.

  The following error message is an example of the truncated error message:

  "Executed as user CONTOSO\ServiceAccount. ...0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984- 2011. All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code

  0x00000000 Source Log Import Activity Descript... The package execution fa... The step failed."

  You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.

  What should you do?

  A. Expand agent logging to include information from all events.
  B. Disable the Limit size of job history log feature.
  C. Configure event forwarding.
  D. Configure output files.
  D. Configure output files.

• Question 33:

  You develop a database for a travel application. You need to design tables and other database objects. Each media file is less than 1 MB in size. The media files will require fast access and will be retrieved frequently.

  You need to store media files in several tables.

  What should you do?

  A. Use the CAST function.
  B. Use the DATE data type.
  C. Use the FORMAT function.
  D. Use an appropriate collation.
  E. Use a user-defined table type.
  F. Use the VARBINARY data type.
  G. Use the DATETIME data type.
  H. Use the DATETIME2 data type.
  I. Use the DATETIMEOFFSET data type.
  J. Use the TODATETIMEOFFSET function.
  F. Use the VARBINARY data type.

• Question 34:

  You administer a single server that contains a Microsoft SQL Server 2012 default instance. You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.

  You need to ensure that the application login is unable to access other production databases. What should you do?

  A. Use the SQL Server default instance and configure an affinity mask.
  B. Install a new named SQL Server instance on the server.
  C. Use the SQL Server default instance and enable Contained Databases.
  D. Install a new default SQL Server instance on the server.
  B. Install a new named SQL Server instance on the server.

• Question 35:

  You administer a Microsoft SQL Server 2012 database.

  The database contains a table named Employee. Part of the Employee table is shown in the exhibit. (Click the Exhibit button.)

  

  

  Unless stated above, no columns in the Employee table reference other tables.

  Confidential information about the employees is stored in a separate table named EmployeeData. One record exists within EmployeeData for each record in the Employee table.

  A. DateHired
  B. DepartmentID
  C. EmployeeID
  D. EmployeeNum
  E. FirstName
  F. JobTitle
  G. LastName
  H. MiddleName
  I. ReportsToID
  I. ReportsToID

• Question 36:

  You use a Microsoft SQL Server 2012 database that contains a table named BlogEntry that has the following columns:

  

  Id is the Primary Key.

  You need to append the "This is in a draft stage" string to the Summary column of the recent 10 entries based on the values in EntryDateTime.

  Which Transact-SQL statement should you use?

  A. UPDATE TOP(10) BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0)
  B. UPDATE BlogEntry SET Summary = CAST(N' This is in a draft stage' as nvarchar(max)) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
  C. UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', NULL, 0) FROM ( SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC) AS s WHERE BlogEntry.Id = s.ID
  D. UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', 0, 0) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)
  D. UPDATE BlogEntry SET Summary.WRITE(N' This is in a draft stage', 0, 0) WHERE Id IN(SELECT TOP(10) Id FROM BlogEntry ORDER BY EntryDateTime DESC)

• Question 37:

  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. Use the SQL Server default instance and enable Contained Databases.
  B. Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role.
  C. Install a new named SQL Server instance on the server.
  D. Install a new default SQL Server instance on the server.
  C. Install a new named SQL Server instance on the server.

• Question 38:

  You have an XML schema collection named Sales.InvoiceSchema.

  You need to declare a variable of the XML type named XML1. The solution must ensure that XML1 is validated by using Sales.InvoiceSchema.

  Which code segment should you use?

  To answer, type the correct code in the answer area.

  A. Declare @XML1=XML(Sales.InvoiceSchema)
  B. DECLARE @XML1 XML @XML1 = Sales.InvoiceSchema CREATE XML SCHEMA COLLECTION XML1 AS @XML1
  A. Declare @XML1=XML(Sales.InvoiceSchema)

• Question 39:

  You administer a Microsoft SQL Server 2012 instance.

  You need to configure a new database to support FILETABLES.

  What should you do? Choose all that apply.

  A. Disable FILESTREAM on the Database.
  B. Enable FILESTREAM on the Server Instance.
  C. Configure the Database for Partial Containment.
  D. Create a non-empty FILESTREAM file group.
  E. Enable Contained Databases on the Server Instance.
  F. Set the FILESTREAM directory name on the Database.
  B. Enable FILESTREAM on the Server Instance.
  D. Create a non-empty FILESTREAM file group.
  F. Set the FILESTREAM directory name on the Database.

• Question 40:

  You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table.

  You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:

  Always starts with 1.

  Starts again from 1 after it reaches 100.

  Which Transact-SQL statement should you use?

  A. CREATE SEQUENCE CustomerSequence AS int START WITH 0 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALOE FOR CustomerSequence DROP SEQUENCE CustomerSequence
  B. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence
  C. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence
  D. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 0 MAXVALUE 100 CYCLE UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence
  B. CREATE SEQUENCE CustomerSequence AS int START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 100 CYCLE UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence