Latest Microsoft 070-457 Actual Free Exam Questions & Community Discussion, Page 2

Question #16

You administer a SQL 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema. You need to remove the Select permission for UserA on the Regions table. You also need to ensure that UserA can still access all the tables in the Customers schema, including the Regions table, through the Sales role permissions. Which Transact-SQL statement should you use?

Discussion 0

Correct Answer: F Vote an answer

Question #17

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 UNIQUE, CustomerName varchar(255) NOT NULL);

B. CREATE TABLE Customer (SourceID int NOT NULL, CustomerID int NOT NULL PRIMARY KEY CLUSTERED, CustomerName varchar(255) NOT NULL);

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

D. CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerName varchar(255) NOT NULL);

Discussion 0

Correct Answer: C Vote an answer

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

Question #18

Question #19

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. REPEATABLE READ

B. SERIALIZABLE

C. READ COMMITTED SNAPSHOT

D. SNAPSHOT

Discussion 0

Correct Answer: C Vote an answer

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

Question #20

You administer a Microsoft SQL Server 2012 server that has SQL Server Integration Services (SSIS) installed. You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables. You need to configure the SQL Server environment to support these packages. What should you do?

A. Install Master Data services.

B. Create an Integration Services catalog.

C. Create SSIS configuration files for the packages.

D. Install Data Quality Services.

Discussion 0

Correct Answer: B Vote an answer

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

Question #21

You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person. The tables have the following definitions:

You create a view named VwEmployee as shown in the following Transact-SQL statement.

Users are able to use single INSERT statements or INSERT...SELECT statements into this view. You need to ensure that users are able to use a single statement to insert records into both Employee and Person tables by using the VwEmployee view. Which Transact-SQL statement should you use?

A. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted
END

B. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName FROM VwEmployee
INSERT INTO Employee(PersonID, EmployeeNumber)
SELECT Id, EmployeeNumber FROM VwEmployee
End

C. CREATE TRIGGER TrgVwEmployee ON VwEmployee INSTEAD OF INSERT AS BEGIN
DECLARE @ID INT, @FirstName NVARCHAR(25), @LastName NVARCHAR(25), @PersonID INT, @EmployeeNumber NVARCHAR(15)
SELECT @ID = ID, @FirstName = FirstName, @LastName = LastName,
@EmployeeNumber = EmployeeNumber
FROM inserted
INSERT INTO Person(Id, FirstName, LastName)
VALUES(@ID, @FirstName, @LastName)
INSERT INTO Employee(PersonID, EmployeeNumber)
VALUES(@PersonID, @EmployeeNumber
End

D. CREATE TRIGGER TrgVwEmployee ON VwEmployee FOR INSERT AS BEGIN INSERT INTO Person(Id, FirstName, LastName) SELECT Id, FirstName, LastName, FROM inserted
INSERT INTO Employee(PersonId, EmployeeNumber)
SELECT Id, EmployeeNumber FROM inserted
END

Discussion 0

Correct Answer: A Vote an answer

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

Question #22

You administer a Microsoft SQL Server 2012 database. The database uses SQL Server Agent jobs to perform regular FULL and LOG backups. The database uses the FULL recovery model. You plan to perform a bulk import of a very large text file. You need to ensure that the following requirements are met during the bulk operation:
The database transaction log is minimally affected.
The database is online and all user transactions are recoverable.
All transactions are fully recoverable prior to the import.
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:

Discussion 0

Correct Answer:

Explanation:
--Burgos - NO

Question #23

You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products. You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data. You need to ensure that the following requirements are met:
Future modifications to the table definition will not affect the applications' ability to access data.
The new object can accommodate data retrieval and data modification.
You need to achieve this goal by using the minimum amount of changes to the applications.
What should you create for each application?

A. Views

B. Common table expressions

C. Synonyms

D. Temporary tables

Discussion 0

Correct Answer: A Vote an answer

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

Question #24

You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders. The tables are related by a column named CustomerID. You need to create a query that meets the following requirements:
Returns the CustomerName for all customers and the OrderDate for any orders that they have placed.
Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?

A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

B. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID

C. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

D. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID

Discussion 0

Correct Answer: C Vote an answer

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

Question #25

Question #26

You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9) )
You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. Which three 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:

Discussion 0

Correct Answer:

Question #27

Question #28

You administer a Microsoft SQL Server 2012 database. You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the availability group to have the highest availability. You also need to ensure that no data is lost. Which Transact-SQL statement should you use?

A. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01
\Contoso01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)

B. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01
\Contoso01' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)

C. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01
\Contoso01'
WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = MANUAL)

D. ALTER AVAILABILITY GROUP haContosoDbs MODIFY REPLICA ON 'Server01
\Contoso01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC)

Discussion 0

Correct Answer: D Vote an answer

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

Question #29

You administer a Microsoft SQL Server 2012 database named ContosoDb. The database contains a table named Suppliers and a column named IsActive in the Purchases schema. You create a new user named ContosoUser in ContosoDb. ContosoUser has no permissions to the Suppliers table. You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions. Which Transact-SQL statement should you use?

A. GRANT SELECT ON Purchases.Suppliers TO ContosoUser

B. GRANT DELETE ON Purchases.Suppliers TO ContosoUser

C. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0
GO
GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

D. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = 'dbo' AS DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

Discussion 0

Correct Answer: D Vote an answer

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

Question #30

You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2012. You
attempt to add the [Corpnet\User1] login to the database. However, you receive the following error
message:
"User already exists in current database."
You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the
original permissions. You need to achieve this goal by using the minimum required permissions.
Which Transact-SQL statement should you use?

A. ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];

B. ALTER SERVER ROLE [sysadmin] ADD MEMBER [Corpnet\User1];

C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];

D. DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEMBER [Corpnet\User1];

Discussion 0

Correct Answer: C Vote an answer

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

Microsoft 070-457 Actual Free Exam Questions & Community Discussion

Download Free Microsoft 070-457 Demo