Passed Microsoft 70-461 Exam with Pass4sure and Lead2pass PDF & VCE (51-60)

QUESTION 51
You develop a Microsoft SQL Server 2012 database. You need to create a batch process that meets the following requirements:
Status information must be logged to a status table.
If the status table does not exist at the beginning of the batch, it must be created.
Which object should you use?

A.    Scalar user-defined function
B.    Inline user-defined function
C.    Table-valued user-defined function
D.    Stored procedure

Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms186755.aspx

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

Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ff878091.aspx

QUESTION 53
You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
Takes an input variable
Returns a table of values Cannot be referenced within a view
Which object should you use?

A.    Scalar-valued function
B.    Inline function
C.    User-defined data type
D.    Stored procedure

Answer: D

QUESTION 54
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.    Reference a user-defined function within the computed column.
B.    Create a BEFORE trigger that maintains the state of the computed column.
C.    Add a default constraint to the computed column that implements hard-coded values.
D.    Add a default constraint to the computed column that implements hard-coded CASE statements.

Answer: A

QUESTION 55
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.PurgeInactiveSu ppliersWITH EXECUTE AS USER = ‘dbo’ AS DELETE
FROM Purchases.Suppliers WHERE IsActive = 0GO GRANT EXECUTE ON
Purchases.PurgelnactiveSu ppliers TO ContosoUser
C.    GRANT SELECT ON
Purchases.Suppliers TO ContosoUser
D.    CREATE PROCEDURE
Purchases.PurgeInactiveSu ppliersAS DELETE FROM Purchases.Suppliers
WHERE IsActive = 0GO GRANT EXECUTE ON
Purchases.PurgeInactiveSu ppliers TO ContosoUser

Answer: D
Explanation:
http://msdn.microsoft.com/en-us/library/ms188354.aspx
http://msdn.microsoft.com/en-us/library/ms187926.aspx

QUESTION 56
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.    User mapped to a certificate
B.    SQL user without login
C.    Domain user
D.    SQL user with login

Answer: C
Explanation:
http://msdn.microsoft.com/en-us/library/ff929071.aspx

QUESTION 57
You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance. What should you do?

A.    Query the sys.dm_db_index_usage_stats DMV.
B.    Query the sys.dm_db_missing_index_details DMV.
C.    Use the Database Engine Tuning Advisor.
D.    Query the sys.dm_db_missing_index_columns DMV.

Answer: C

QUESTION 58
Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day. You need to write a query that will return the correct results in the most efficient manner. Which Transact-SQL query should you use?

A.    SELECT COUNT (*) FROM SaLeaOrders WHERE OrderTime = CONVERT(DATE, GETDATE ())
B.    SELECT COUNT(*) FROM SalesOrders WHERE OrderTime – GETDATE()
C.    SELECT COUNT(-) FROM SaLesCrders WHERE CONCERT(VARCHAR, OrderTime, 112) =
CONVERT (VARCHAR, GETDATE(I, 112)
D.    SELECT CCUNT(*) FROM SalesCrder3 WHERE CrderTime >= CONVERT(DATE, GETDATE())
AND CrderTime < DATEADD(DAY, CONVERT(DATS, GETDATE()))

Answer: D

QUESTION 59
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.    Trigger
C.    An UPDATE statement that includes CASE
D.    Cursor
E.    The foreach SQLCLR statement

Answer: D

QUESTION 60
You use Microsoft SQL Server 2012 to develop a database application. You create a stored procedure named dbo.ModifyData that can modify rows. You need to ensure that when the transaction fails, dbo.ModifyData meets the following requirements:
– Does not return an error
– Closes all opened transactions
Which Transact-SQL statement should you use?

A.    BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ TRANCOUNT = 0
ROLLBACK TRANSACTION;
END CATCH
B.    BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERRCR != 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
C.    BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT = 0
ROLLBACK TRANSACTION;
THROW;
END CATCH
D.    BEGIN TRANSACTION
BEGIN TRY
EXEC dbo.ModifyData
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ZRROR !- 0
ROLLBACK TRANSACTION;
END CATCH

Answer: D

If you want to pass Microsoft 70-461 successfully, do not missing to read latest lead2pass Microsoft 70-461 practice exams.
If you can master all lead2pass questions you will able to pass 100% guaranteed.

http://www.lead2pass.com/70-461.html

         

Why Choose Lead2pass?

If you want to pass the exam successfully in first attempt you have to choose the best IT study material provider, in my opinion, Lead2pass is one of the best way to prepare for the exam.

Lead2pass Testking Pass4sure Actualtests Others
$99.99 $124.99 $125.99 $189 $29.99-$49.99
Up-to-Dated
Real Questions
Error Correction
Printable PDF
Premium VCE
VCE Simulator
One Time Purchase
Instant Download
Unlimited Install
100% Pass Guarantee
100% Money Back

Comments are closed.