[Free] 2018(May) EnsurePass Examcollection Microsoft 70-461 Dumps with VCE and PDF 91-100

Ensurepass.com : Ensure you pass the IT Exams
2018 May Microsoft Official New Released 70-461
100% Free Download! 100% Pass Guaranteed!

Querying Microsoft SQL Server 2012

Question No: 91 DRAG DROP

You develop a database application for a university. You need to create a view that will be indexed that meets the following requirements:

-> Displays the details of only students from Canada.

-> Allows insertion of details of only students from Canada.

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

image

Answer:

image

Question No: 92

You develop a stored procedure for a wholesale glass manufacturing company. The stored procedure uses a cursor to read all row-based combinations of the following tables:

image

You observe that the stored procedure returns results very slowly. You need to improve the execution speed of the stored procedure. Which cursor type should you use?

  1. GLOBAL FORWARD_ONLY

  2. LOCAL FAST_FORWARD

  3. LOCAL STATIC READ_ONLY FORWARD_ONLY

  4. GLOBAL STATIC

Answer: B Explanation:

FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.

References: https://msdn.microsoft.com/en-us/library/ms180169.aspx

Question No: 93

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

image

image

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. You need to assign the appropriate constraints and table properties to ensure data integrity and visibility. On which column in the Employee table should you create a unique constraint?

  1. DateHired

  2. DepartmentID

  3. EmployeeID

  4. EmployeeNum

  5. FirstName

  6. JobTitle

  7. LastName

  8. MiddleName

  9. ReportsToID

Answer: D

Question No: 94

You are maintaining a Microsoft SQL Server database. You run the following query:

image

You observe performance issues when you run the query. You capture the following query execution plan:

image

You need to ensure that the query performs returns the results as quickly as possible. Which action should you perform?

  1. Add a new index to the ID column of the Person table.

  2. Add a new index to the EndDate column of the History table.

  3. Create a materialized view that is based on joining data from the ActiveEmployee and History tables.

  4. Create a computed column that concatenates the GivenName and SurName columns.

Answer: A

Explanation: Cost is 53% for the Table Scan on the Person (p) table. This table scan is on the ID column, so we should put an index on it.

Question No: 95

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?

  1. GRANT DELETE ON Purchases.Suppliers TO ContosoUser

  2. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers WITH EXECUTE AS USER = #39;dbo#39;

    AS

    DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO

    GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

  3. GRANT SELECT ON Purchases.Suppliers TO ContosoUser

  4. CREATE PROCEDURE Purchases.PurgeInactiveSuppliers AS

DELETE FROM Purchases.Suppliers WHERE IsActive = 0 GO

GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

Answer: D

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

Question No: 96

You are maintaining a Microsoft SQL Server database that stores order information for an online store website. The database contains a table that is defined by the following Transact-SQL statement:

image

You need to ensure that purchase order numbers are used only for a single order. What should you do?

  1. Create a new CLUSTERED constraint on the PurchaseOrderNumber column.

  2. Create a new UNIQUE constraint on the PurchaseOrderNumber column.

  3. Create a new PRIMARY constraint on the PurchaseOrderNumber column.

  4. Create a new FOREIGN KEY constraint on the PurchaseOrderNumber column.

Answer: B

Explanation: You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

Reference: UNIQUE Constraints

https://technet.microsoft.com/en-us/library/ms191166(v=sql.105).aspx

Question No: 97

You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.

image

You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do?

  1. Perform a file restore.

  2. Perform a transaction log restore.

  3. Perform a restore from a full backup.

  4. Perform a filegroup restore.

Answer: A

Question No: 98 DRAG DROP

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

image

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

image

Answer:

image

Question No: 99

You create a view based on the following statement:

image

You grant the Select permission to User1.

You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view

functions correctly for User1.

Which Transact-SQL statement should you use?

image

  1. Option A

  2. Option B

  3. Option C

  4. Option D

Answer: C

Question No: 100 CORRECT TEXT

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.

Answer: DECLARE @XML1 XML(Sales.InvoiceSchema)

100% Ensurepass Free Download!
Download Free Demo:70-461 Demo PDF
100% Ensurepass Free Guaranteed!
70-461 Dumps

EnsurePass ExamCollection Testking
Lowest Price Guarantee Yes No No
Up-to-Dated Yes No No
Real Questions Yes No No
Explanation Yes No No
PDF VCE Yes No No
Free VCE Simulator Yes No No
Instant Download Yes No No

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.