70 461 certification (31 to 40)

We provide real 70 461 training kit exam questions and answers braindumps in two formats. Download PDF & Practice Tests. Pass Microsoft exam 70 461 pdf Exam quickly & easily. The exam 70 461 practice test PDF type is available for reading and printing. You can print more and practice many times. With the help of our Microsoft exam 70 461 sample questions dumps pdf and vce product and material, you can easily pass the exam 70 461 dumps exam.

♥♥ 2021 NEW RECOMMEND ♥♥

Free VCE & PDF File for Microsoft 70-461 Real Exam (Full Version!)

★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions

Free Instant Download NEW 70-461 Exam Dumps (PDF & VCE):
Available on: http://www.surepassexam.com/70-461-exam-dumps.html

Q31. CORRECT TEXT

You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)

You need to create a view named uv_CustomerFullName to meet the following requirements:

The code must NOT include object delimiters.

The view must be created in the Sales schema.

Columns must only be referenced by using one-part names.

The view must return the first name and the last name of all customers.

The view must prevent the underlying structure of the customer table from being

.....

changed.

. The view must be able to resolve all referenced objects, regardless of the user's default schema.

Which code segment should you use?

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

Answer:

Q32. You develop a Microsoft SQL Server 2012 server database that supports an application.

The application contains a table that has the following definition:

CREATE TABLE Inventory (

ItemID int NOT NULL PRIMARY KEY,

ItemsInStore int NOT NULL,

ItemsInWarehouse int NOT NULL)

You need to create a computed column that returns the sum total of the ItemsInStore and ItemsInWarehouse values for each row.

The new column is expected to be queried heavily, and you need to be able to index the column. Which Transact-SQL statement should you use?

A. ALTER TABLE Inventory

ADD TotalItems AS ItemslnStore + ItemsInWarehouse

B. ALTER TABLE Inventory

ADD TotalItems AS ItemsInStore + ItemsInWarehouse PERSISTED

C. ALTER TABLE Inventory

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse) PERSISTED

D. ALTER TABLE Inventory

ADD TotalItems AS SUM(ItemsInStore, ItemsInWarehouse)

Answer: B

Q33. DRAG DROP

You want to add a new GUID column named BookGUID to a table named dbo.Book that already contains data.

BookGUID will have a constraint to ensure that it always has a value when new rows are inserted into dbo.Book.

You need to ensure that the new column is assigned a GUID for existing rows.

Which four 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.)

Answer:

Q34. You are developing a database application by using Microsoft SQL Server 2012.

An application that uses a database begins to run slowly.

You discover that a large amount of memory is consumed by single-use dynamic queries.

You need to reduce procedure cache usage from these statements without creating any additional indexes.

What should you do?

A. Add a HASH hint to the query.

B. Add a LOOP hint to the query.

C. Add a FORCESEEK hint to the query.

D. Add an INCLUDE clause to the index.

E. Add a FORCESCAN hint to the Attach query.

F. Add a columnstore index to cover the query.

G. Enable the optimize for ad hoc workloads option.

H. Cover the unique clustered index with a columnstore index.

I. Include a SET FORCEPLAN ON statement before you run the query.

J. Include a SET STATISTICS PROFILE ON statement before you run the query.

K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.

L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.

M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.

N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.

Answer: G

Q35. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId- 1 FOR XML AUTO

F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

G. SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')

H. SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML PATH ('Customers')

Answer: D

Q36. You are a database developer of a Microsoft SQL Server 2012 database.

The database contains a table named Customers that has the following definition:

You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table.

Which Transact-SQL statement should you use?

A. ALTER TABLE Orders

ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES

Customer (CustomerId)

B. ALTER TABLE Customer

ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID)

REFERENCES

Orders (CustomerId)

C. ALTER TABLE Orders

ADD CONSTRAINT CK_Crders_CustomerID

CHECK (CustomerId IN (SELECT CustomerId FROM Customer))

D. ALTER TABLE Customer

ADD OrderId INT NOT NULL;

ALTER TABLE Customer

ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES

Orders

(CrderlD);

E. ALTER TABLE Orders

ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)

Answer: A

Reference: http://msdn.microsoft.com/en-us/library/ms189049.aspx

Q37. 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 DELETE ON Purchases.Suppliers TO ContosoUser

B. 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 C. GRANT SELECT ON Purchases.Suppliers TO ContosoUser

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

Answer: D

Q38. You develop a Microsoft SQL Server 2012 database that contains a table named Customers. The Customers table has the following definition:

You need to create an audit record only when either the MobileNumber or HomeNumber column is updated.

Which Transact-SQL query should you use?

A. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_UPDATED (HomeNumber, MobileNumber) - - Create Audit Records

B. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF EXISTS( SELECT HomeNumber FROM inserted) OR EXISTS (SELECT MobileNumber FROM inserted) - - Create Audit Records

C. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF COLUMNS_CHANGED (HomeNumber, MobileNumber) - - Create Audit Records

D. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE AS IF UPDATE (HomeNumber) OR UPDATE (MobileNumber) - - Create Audit Records

Answer: D

Q39. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format:

<row OrderId="1" OrderDate="2000-01-01T00:00:00" Amount="3400.00" Name="Customer

A" Country="Australia" />

<row OrderId="2" OrderDate="2001-01-01T00:00:00" Amount="4300.00" Name="Customer

A" Country="Australia" />

Which Transact-SQL query should you use?

A. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW

B. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW, ELEMENTS

C. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO

D. SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId - Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

E. SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId- 1 FOR XML AUTO

F. SELECT Name, Country, Orderld, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId= 1 FOR XML AUTO, ELEMENTS

Answer: A

Q40. You develop a Microsoft SQL Server 2012 database that contains tables named Employee and Person.

The tables have the following definitions:

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 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

B. 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

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 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

Answer: B

To see all sample questions go to: 70-461 test prep

Related 70-461 posts: