Microsoft SQL Server 2012 070-461 exam dumps, free practical effective 070-461 Practice test

Softwaregeneralist updates free and effective exam dumps throughout the year, with all exam content coming from Pass4itsure experts,
the latest Microsoft SQL Server 2012 070-461 Exam Dumps. download the 070-461 PDF Online to help you open the certification gate
and get the Microsoft 070-461 exam certificate by exam Select: https://www.pass4itsure.com/070-461.html VCE dump or PDF Dumps (q&as:164 latest update)

[PDF] Free Microsoft SQL Server 2012 070-461 dumps download from Google Drive:
https://drive.google.com/open?id=1QZba68jM2Ro0Pc3RWduSKdgJ-ieSdlH1

[PDF] Free Full Microsoft dumps download from Google Drive:
https://drive.google.com/open?id=1gdQrKIsiLyDEsZ24FxsyukNPYmpSUDDO

Exam 70-461: Querying Microsoft SQL Server 2012/2014:
https://www.microsoft.com/en-us/learning/exam-70-461.aspx

Pass4itsure offers the latest Microsoft SQL Server 2012 070-461 practice test free of charge (30Q&As)

QUESTION 1
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit.
You need to create a report that displays the profits made by each territory for each year and its previous year.
Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
B. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
C. SELECT Territory, Year, Profit,
LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
D. SELECT Territory, Year, Profit,
LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
Correct Answer: C
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/hh231256.aspx Reference: http://msdn.microsoft.com/en-us/library/hh213125.aspx

QUESTION 2
You develop a Microsoft SQL Server 2012 database that has two tables named SavingAccounts and LoanAccounts. Both tables have a column named
AccountNumber of the nvarchar data type.
You use a third table named Transactions that has columns named TransactionId AccountNumber, Amount, and TransactionDate.
You need to ensure that when multiple records are inserted in the Transactions table, only the records that have a valid AccountNumber in the SavingAccounts or
LoanAccounts are inserted.
Which Transact-SQL statement should you use?
A. CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts) END
B. CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
INSERT INTO Transactions
SELECT TransactionID,AccountNumber,Amount,TransactionDate FROM inserted WHERE AccountNumber IN
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts) END
C. CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN
END
END
D. CREATE TRIGGER TrgValidateAccountNumber
ON Transactions
FOR INSERT
AS
BEGIN
IF EXISTS (
SELECT AccountNumber FROM inserted EXCEPT
(SELECT AccountNumber FROM LoanAccounts
UNION SELECT AccountNumber FROM SavingAccounts)) BEGIN
ROLLBACK TRAN
END
END
Correct Answer: A
Explanation

QUESTION 3
You administer a Microsoft SQL Server 2012 database that has multiple tables in the Sales schema. Some users must be prevented from deleting records in any
of the tables in the Sales schema. You need to manage users who are prevented from deleting records in the Sales schema.
You need to achieve this goal by using the minimum amount of administrative effort. What should you do?
A. Create a custom database role that includes the users. Deny Delete permissions on the Sales schema for the custom database role.
B. Include the Sales schema as an owned schema for the db_denydatawriter role. Add the users to the db_denydatawriter role.
C. Deny Delete permissions on each table in the Sales schema for each user.D. Create a custom database role that includes the users. Deny Delete permissions on each table in the Sales schema for the custom database role.
Correct Answer: A
Explanation

QUESTION 4
You have a vendor application that uses a scalar function.
You discover that the queries for the application run slower than expected.
You need to gather the runtime information of the scalar function.
What are two possible ways to achieve this goal? Each correct answer presents a complete solution.
A. Enable the Include Actual Execution Plan option.
B. Enable the Display Estimated Execution Planoption.
C. Create and then enable a profiler trace.
D. Create and then enable an extended events trace.
E. Run the Database Engine Tuning Advisor.
Correct Answer: AD
Explanation
Explanation/Reference:
A: An execution plan is the result of the query optimizer’s attempt to calculate the mostefficient way to implement the request represented by the T-SQL query you
submitted. To generate the first execution plan, you can enable the Include Actual Execution Plan option.
D: SQL Server Extended Events can be used to capture User Defined Function(UDF) counts
References:https://www.mssqltips.com/sqlservertip/4100/how-to-find-udfs-causing-sql- server- performance-issues/

QUESTION 5
Your application contains a stored procedure for each country. Each stored procedure accepts an employee identification number through the @EmpID
parameter.
You need to build a single process for each employee that will execute the appropriate stored procedure based on the country of residence.
Which approach should you use?
A. A SELECT statement that includes CASE
B. Cursor
C. BULK INSERT
D. View
E. A user-defined function
Correct Answer: E
Explanation
Explanation/Reference:
SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a
value. The return value can either be a single scalar value or a result set.

QUESTION 6
You use a Microsoft SQL Server 2012 database.
You want to create a table to store Microsoft Word documents.
You need to ensure that the documents must only be accessible via Transact-SQL queries.
Which Transact-SQL statement should you use?
A. CREATE TABLE DocumentStore
(
[Id] INT NOT NULL PRIMARY KEY,
[Document] VARBINARY(MAX) NULL
)
GO
B. CREATE TABLE DocumentStore
(
[Id] hierarchyid,
[Document] NVARCHAR NOT NULL
)
GO
C. CREATE TABLE DocumentStore AS FileTable
D. CREATE TABLE DocumentStore
(
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE, [Document] VARBINARY(MAX) FILESTREAM NULL
)
GO
Correct Answer: A
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/gg471497.aspx
Reference: http://msdn.microsoft.com/en-us/library/ff929144.aspx

QUESTION 7
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50
students for various subjects.
You need to ensure that the following requirements are met:
Students must be ranked based on their average marks. If one or more students have the same average, the same rank must be given to these students.
Consecutive ranks must be skipped when the same rank is assigned.
Which Transact-SQL query should you use?
A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
D. SELECT StudentCode as Code,
NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY
Marks ASC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1
F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY
Marks DESC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1
G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY
Marks ASC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1
H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY
Marks DESC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1
Correct Answer: A
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms189798.aspx

QUESTION 8
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:
pass4itsure 070-461 question
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?
A. GLOBAL FORWARD_ONLY
B. LOCAL FAST_FORWARD
C. LOCAL STATIC READ_ONLY FORWARD_ONLY
D. GLOBAL STATIC
Correct Answer: B
Explanation
Explanation/Reference:
FAST_FORWARD specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled.
References: https://msdn.microsoft.com/en-us/library/ms180169.aspx

QUESTION 9
You administer a Microsoft SQL Server 2012 database that has Trustworthy set to On. You create a stored procedure that returns database-level information from
Dynamic Management Views. You grant User1 access to execute the stored procedure. You need to ensure that the stored procedure returns the required
information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required. What should you do? (Each
correct answer presents a complete solution.
Choose all that apply.)
A. Create a SQL Server login that has VIEW SERVER STATE permissions. Create an application role and a secured password for the role.
B. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
D. Grant the db_owner role on the database to User1.
E. Grant the sysadmin role on the database to User1.
Correct Answer: DE
Explanation

QUESTION 10
You develop a Microsoft SQL Server 2012 database.
You need to create and call a stored procedure that meets the following requirements:
Accepts a single input parameter for CustomerID. Returns a single integer to the calling application.
Which Transact-SQL statement or statements should you use? (Each correct answer presents part of the solution. Choose all that apply.)A. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @CustomerRating = CustomerOrders/CustomerValue FROM Customers WHERE CustomerID = @CustomerID
RETURN
GO
B. EXECUTE dbo.GetCustomerRating 1745
C. DECLARE @CustomerRatingByCustomer INT
DECLARE @Result INT
EXECUTE @Result = dbo.GetCustomerRating
1745,
@CustomerRatingByCustomer
D. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT,
@CustomerRating INT OUTPUT
AS
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURN @Result
GO
E. DECLARE @CustomerRatingByCustomer INT
EXECUTE dbo.GetCustomerRating
@CustomerID = 1745,
@CustomerRating = @CustomerRatingByCustomer OUTPUT
F. CREATE PROCEDURE dbo.GetCustomerRating
@CustomerID INT
AS
DECLARE @Result INT
SET NOCOUNT ON
SELECT @Result = CustomerOrders/CustomerValue
FROM Customers
WHERE CustomerID = @CustomerID
RETURNS @Result
GO
Correct Answer: AE
Explanation

QUESTION 11
You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?
A. Float
B. Money
C. Smallmoney
D. Numeric
Correct Answer: D
Explanation
Explanation/Reference:
Numeric is the only one in the list that can give a fixed precision and scale. Reference: http:// msdn.microsoft.com/en-us/library/ms179882.aspx

QUESTION 12
You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50
students for various subjects.
You need to ensure that the top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank
of 2. Which Transact-SQL query should you use?
A. SELECT StudentCode as Code,
RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
B. SELECT Id, Name, Marks,
DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank FROM StudentMarks
C. SELECT StudentCode as Code,
DENSE_RANK() OVER (ORDER BY AVG (Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
D. SELECT StudentCode as Code,
NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
E. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER BY
Marks ASC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1
F. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER (PARTITION BY SubjectCode ORDER BY
Marks DESC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1
G. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK () OVER (PARTITION BY StudentCode ORDER BY
Marks ASC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1
H. SELECT StudentCode AS Code,Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER (PARTITION BY StudentCode ORDER BY
Marks DESC) AS Rank FROM StudentMarks) tmp
WHERE Rank = 1Correct Answer: D
Explanation

QUESTION 13
You create a view based on the following statement:
pass4itsure 070-461 question
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?
pass4itsure 070-461 question
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: C
Explanation

QUESTION 14
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 parameter
Returns a table of values
Can be referenced within a view
Which object should you use?
A. inline table-valued function
B. user-defined data type
C. stored procedure
D. scalar-valued function
Correct Answer: A
Explanation

QUESTION 15
You are developing a database application by using Microsoft SQL Server 2012.
You have a query that runs slower than expected.
You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer.
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.
Correct Answer: K
Explanation

QUESTION 16
You develop 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 not include customers who have not placed
any orders.
Which Transact-SQL query should you use?
A. SELECT CustomerName, OrderDate
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerId
B. SELECT CustomerName, OrderDate
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerId
C. SELECT CustomerName, OrderDate
FROM Customers
CROSS JOIN Orders
ON Customers.CustomerId = Orders.CustomerId
D. SELECT CustomerName, OrderDate
FROM Customers
JOIN Orders
ON Customers.CustomerId = Orders.CustomerId
Correct Answer: D
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms177634.aspx

QUESTION 17
You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)
pass4itsure 070-461 question
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.
pass4itsure 070-461 question
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, OrderId, 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’)
Correct Answer: F
Explanation

QUESTION 18
You are designing a table for a SQL Server database. The table uses horizontal partitioning.
You have the following requirements:
pass4itsure 070-461 question
You need to choose the appropriate data type for the key value.
What should you do?
A. Use the NEWID function to create a unique identifier.
B. Use the NEWSEQUENTIALID function to create a unique identifier.
C. Generate a random value that uses the bigint datatype.
D. Generate a random value that uses the char(16) data type.
Correct Answer: B
Explanation
Explanation/Reference:
Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows. For example, a table that
contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year. Any queries
requiring data for a specific month only reference the appropriate table.
NEWSEQUENTIALID creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started.
After restarting Windows, the GUID can start again from a lower range, but is still globally unique. When a GUID column is used as a row identifier, using
NEWSEQUENTIALID can be faster than using the NEWID function. This is because the NEWID function causes random activity and uses fewer cached data
pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.
References:https://msdn.microsoft.com/en-us/library/ms189786.aspx

QUESTION 19
You are developing a database that will contain price information.
You need to store the prices that include a fixed precision and a scale of six digits.
Which data type should you use?
A. Real
B. Small money
C. Money
D. Decimal
Correct Answer: D
Explanation

QUESTION 20
You develop a Microsoft SQL Server 2012 database. You create a view from the Orders and OrderDetails tables by using the following definition.
pass4itsure 070-461 question
You need to improve the performance of the view by persisting data to disk. What should you do?
A. Create an INSTEAD OF trigger on the view.
B. Create an AFTER trigger on the view.
C. Modify the view to use the WITH VIEW_METADATA clause.
D. Create a clustered index on the view.
Correct Answer: D
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms188783.aspx

QUESTION 21
You develop a database for a travel application. You need to design tables and other database objects. You create a stored procedure. You need to supply the
stored procedure with multiple event names and their dates as parameters. 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.
Correct Answer: E
Explanation

QUESTION 22
Your database contains a table named Customer that has columns named CustomerID and Name.
You want to write a query that retrieves data from the Customer table sorted by Name listing 20 rows at a time.
You need to view rows 41 through 60.
Which Transact-SQL query should you create?
pass4itsure 070-461 question
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
Explanation

QUESTION 23
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.
Correct Answer: G
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/cc645587.aspx

QUESTION 24
You administer a Microsoft SQL Server database that supports a banking transaction management application.
You need to retrieve a list of account holders who live in cities that do not have a branch location.
Which Transact-SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID NOT IN (SELECT CityID FROM BranchMaster)
B. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID <> ALL (SELECT CityID FROM BranchMaster)
C. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID <> SOME (SELECT CityID FROM BranchMaster)
D. SELECT AccountHolderID
FROM AccountHolder
WHERE CityID <> ANY (SELECT CityID FROM BranchMaster)
Correct Answer: AB
Explanation
Explanation/Reference:
Verified the answers as correct.
Reference: http://msdn.microsoft.com/en-us/library/ms188047.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms177682.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms173545.aspx

QUESTION 25
You are writing a set of queries against a FILESTREAM-enabled database.
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a runtime error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?
A. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
B. SET XACT_ABORT OFF
C. SET TRANSACTION ISOLATION LEVEL SNAPSHOT
D. SET IMPLICIT_TRANSACTIONS ON
E. SET XACT_ABORT ON
F. SET IMPLICIT TRANSACTIONS OFF
Correct Answer: E
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/ms188792.aspx

QUESTION 26
You need to build a table structure for a stored procedure to support data entry from a website form. The website form must meet the following requirements:
pass4itsure 070-461 question
Which two actions should you perform? Each correct answer presents part of the solution.
A. Add the CHECK constraint to the table structure.B. Add the DATEPART function to the stored procedure.
C. Add the DEFAULT constraint to the table structure.
D. Add the SYSDATETIMEOFFSET function to the stored procedure.
E. Add the ISDATE function to the stored procedure.
Correct Answer: DE
Explanation
Explanation/Reference:
SYSDATETIMEOFFSET returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
ISDATE returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
References:https://msdn.microsoft.com/en-us/library/bb677334.aspx

QUESTION 27
You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT CustomerID FROM Customer
WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
B. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
C. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
D. SELECT CustomerID FROM Customer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)
Correct Answer: BD
Explanation

QUESTION 28
You develop a Microsoft SQL Server 2012 database that contains a table named Products. The Products table has the following definition:
pass4itsure 070-461 question
You need to create an audit record only when either the RetailPrice or WholeSalePrice column is updated.
Which Transact-SQL query should you use?
A. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS
IF CCLUMNS_CHANGED(RetailPrice, WholesalePrice)
– – Create Audit Records
B. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF EXISTS(SELECT RetailPrice from inserted) OR
EXISTS (SELECT WholeSalePnce FROM inserted)
– – Create Audit Records
C. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF COLUMNS_UPDATED (RetailPrice, WholesalePrice)
– – Create Audit Records
D. CREATE TRIGGER TrgPriceChange ON Products FOR UPDATE AS IF UPDATE(RetailPrice) OR UPDATE(WholeSalePrice)
– – Create Audit Records
Correct Answer: D
Explanation
Explanation/Reference:
Reference: http://msdn.microsoft.com/en-us/library/bb510663.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms186329.aspx

QUESTION 29
You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code.
You need to protect the code from being viewed by your customers.
Which stored procedure option should you use?
A. ENCRYPTBYKEY
B. ENCRYPTION
C. ENCRYPTBYPASSPHRASE
D. ENCRYPTBYCERT
Correct Answer: BExplanation
Explanation/Reference:
Reference: http://technet.microsoft.com/en-us/library/bb510663.aspx Reference: http://technet.microsoft.com/en-us/library/ms174361.aspx
Reference: http://msdn.microsoft.com/en-us/library/ms187926.aspx
Reference: http://technet.microsoft.com/en-us/library/ms190357.aspx
Reference: http://technet.microsoft.com/en-us/library/ms188061.aspx

QUESTION 30
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.
Correct Answer: A
Explanation

Conclusion:
Softwaregeneralist free sharing of 30 Microsoft SQL Server 2012 070-461 exam dumps and 070-461 PDF online downloads to help you
practice testing, we share high quality exam dumps for free throughout the year, If you want to get a 070-461 exam certificate,
select Microsoft 070-461 full Exam dumps:https://www.pass4itsure.com/070-461.html (Q&As:164 vce+ pdf)

[PDF] Free Microsoft SQL Server 2012 070-461 dumps download from Google Drive:
https://drive.google.com/open?id=1QZba68jM2Ro0Pc3RWduSKdgJ-ieSdlH1

[PDF] Free Full Microsoft dumps download from Google Drive:
https://drive.google.com/open?id=1gdQrKIsiLyDEsZ24FxsyukNPYmpSUDDO

Pass4itsure Promo Code 15% Off

pass4itsure 070-461 coupon

related: https://www.softwaregeneralist.com/100-pass-rate-lpi-101-400-dumps/