Ensurepass.com : Ensure you pass the IT Exams
2018 Mar Microsoft Official New Released 70-461
100% Free Download! 100% Pass Guaranteed!
http://www.EnsurePass.com/70-461.html
Querying Microsoft SQL Server 2012
Question No: 61
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?
-
CREATE SEQUENCE CustomerSequence AS int START WITH 0
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence DROP SEQUENCE CustomerSequence
-
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
-
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
-
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
Reference: http://msdn.microsoft.com/en-us/library/ff878091.aspx
Question No: 62 CORRECT TEXT
You have a SQL database that contains a table named Products.
You are implementing a stored procedure that retrieves the list of products, performs custom business logic and then retrieve the list of products again.
The custom business logic in the stored procedure does not modify data from the Products
table.
The stored procedure contains the following:
You need to complete line 01 of the stored procedure to ensure that when the transaction occurs, the data read from the SELECT * FROM Products statement on line 05 is identical to the data read from the SELECT * FROM Products statement on line 10. The solution must maximize concurrency.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
Answer: REPEATABLE READ
Explanation:
Add REPEATABLE READ to line 1 to get:
SET TRANSACTIONISOLATION LEVEL REPEATABLE READ;
REPEATABLE READ specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
Question No: 63
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?
-
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE
AS
IF COLUMNS_UPDATED (HomeNumber, MobileNumber)
– Create Audit Records
-
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE
AS
IF EXISTS( SELECT HomeNumber from inserted) OR EXISTS (SELECT MobileNumber FROM inserted)
– Create Audit Records
-
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE
AS
IF COLUMNS_CHANGED (HomeNumber, MobileNumber)
– Create Audit Records
-
CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE
AS
IF UPDATE (HomeNumber) OR UPDATE (MobileNumber)
– Create Audit Records
Answer: D
Reference: http://msdn.microsoft.com/en-us/library/bb510663.aspx Reference: http://msdn.microsoft.com/en-us/library/ms186329.aspx
Question No: 64
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:
lt;row OrderId=quot;1quot; OrderDate=quot;2000-01-01T00:00:00quot; Amount=quot;3400.00quot; Name=quot;Customer
Aquot; Country=quot;Australiaquot; /gt;
lt;row OrderId=quot;2quot; OrderDate=quot;2001-01-01T00:00:00quot; Amount=quot;4300.00quot; Name=quot;Customer
Aquot; Country=quot;Australiaquot; /gt;
Which Transact-SQL query should you use?
-
SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML RAW
-
SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML RAW, ELEMENTS
-
SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML AUTO
-
SELECT OrderId, OrderDate, Amount, Name, Country
FROM Orders INNER JOIN Customers ON Orders.CustomerId – Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS
-
SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML AUTO
-
SELECT Name, Country, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML AUTO, ELEMENTS
-
SELECT Name AS #39;@Name#39;, Country AS #39;@Country#39;, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML PATH (#39;Customers#39;)
-
SELECT Name AS #39;Customers/Name#39;, Country AS #39;Customers/Country#39;, OrderId, OrderDate, Amount
FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1
FOR XML PATH (#39;Customers#39;)
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/bb510464.aspx
Question No: 65 CORRECT TEXT
You have a data warehouse that contains the data for all the customers of your company.
You need to create a query dynamically generates a SELECT statement from a table named CUSTOMERS. The SELECT statement must generate a full list of columns.
Part of the correct Transact-SQL has been provided in the answer area below. Enter the code in the answer area that resolves the problem and meets the stated goals or requirements. You can add code within the code that has been provided as well as below it.
Use the Check Syntax button to verify your work. Any syntax or spelling errors will be reported by line and character position.
Answer: XML PATH
Explanation:
In line 7 add XML PATH to get thefollowing line: FOR XML PATH (#39; #39;)), 1, 1, #39; #39;)
Here is how it works:
-
Get XML element string with FOR XML
Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained inthe PATH argument. For example, if we were to run the following statement:
SELECT #39;,#39; name
FROM temp1
FOR XML PATH (#39;#39;)
By passing in a blank string (FOR XML PATH(#39;#39;)), we get the following instead:
,aaa,bbb,ccc,ddd,eee
-
Remove leading commawith STUFF
The STUFF statement literally quot;stuffs鈥?one string into another, replacing characters within the first string. We, however, are using it simply to remove the first character of the resultant list of values.
SELECT abc = STUFF( ( SELECT #39;,#39; NAME
FROM temp1
FOR XML PATH(#39;#39;)
), 1, 1, #39;#39;)
FROM temp1
Note: The full code will be: SELECT #39;SELECT#39; STUFF ((
SELECT #39;, [#39; name #39;]#39;
FROM
WHERE id = OBJECT_ID(#39;Customers#39;) AND
…name lt;gt; #39;me#39;
FOR XML PATH (#39; #39;)), 1, 1, #39; #39;)
#39;FROM[Customers] #39;
References: http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path- work-in-sql-server
Question No: 66 CORRECT TEXT
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You deploy a new server that has SQL Server 2012 installed. You need to create a table named
Sales.OrderDetails on the new server. Sales.OrderDetails must meet the following requirements:
-> Write the results to a disk.
-> Contain a new column named LineItemTotal that stores the product of ListPrice
and Quantity for each row.
-> The code must NOT use any object delimiters.
The solution must ensure that LineItemTotal is stored as the last column in the table. Which code segment should you use?
To answer, type the correct code in the answer area.
Answer: Please review the explanation part for this answer
Explanation:
CREATE TABLE Sales.OrderDetails ( ListPrice money not null,
Quantity int not null,
LineItemTotal as (ListPrice * Quantity) PERSISTED)
Question No: 67
You are designing a table for a SQL Server database. The table uses horizontal partitioning.
You have the following requirements:
You need to choose the appropriate data type for the key value. What should you do?
-
Use the NEWID function to create a unique identifier.
-
Use the NEWSEQUENTIALID function to create a unique identifier.
-
Generate a random value that uses the bigint datatype.
-
Generate a random value that uses the char(16) data type.
-
-
Answer: B Explanation:
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 No: 68 CORRECT TEXT
You are maintaining a SQL Server database that uses the default settings. The database contains a table that is defined by the following Transact-SQL statement:
You must write a query that returns the AddressLine1, AddressLine2, and Region fields
separated by carriage returns. You must return an empty string for any null values.
-
Option A
-
Option B
-
Option C
-
Option D
Answer: A
Explanation:
Char(13) is a carriage return.
Use the IIF construct to return an empty string for NULL values of the Adressline2 column. IIF returns one of two values, depending on whether theBoolean expression evaluates to true or false in SQL Server.
References: https://msdn.microsoft.com/en-us/library/hh213574.aspx
Question No: 69
You administer a Microsoft SQL Server 2012 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation.
You need to achieve this goal without taking the index offline. Which Transact-SQL batch should you use?
-
CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP
EXISTING
-
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
-
ALTER INDEX ALL ON OrderDetail REBUILD
-
ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Answer: B
Reference: http://msdn.microsoft.com/en-us/library/ms188388.aspx
Question No: 70 CORRECT TEXT
You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You have an application named Appl. You have a parameter named @Count that uses the int data type. App1 is configured to pass @Count to a stored procedure. You need to create a stored procedure named usp_Customers for Appl. Usp_Customers must meet the following requirements:
-> NOT use object delimiters.
-> Minimize sorting and counting.
-> Return only the last name of each customer in alphabetical order.
-> Return only the number of rows specified by the @Count parameter.
-> The solution must NOT use BEGIN and END statements.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer: Please review the explanation part for this answer
Explanation:
CREATE PROCEDURE usp_Customers @Count int AS
SELECT TOP(@Count) Customers.LastName FROM Customers
ORDER BY Customers.LastName
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 |
100-105 Dumps VCE PDF
200-105 Dumps VCE PDF
300-101 Dumps VCE PDF
300-115 Dumps VCE PDF
300-135 Dumps VCE PDF
300-320 Dumps VCE PDF
400-101 Dumps VCE PDF
640-911 Dumps VCE PDF
640-916 Dumps VCE PDF
70-410 Dumps VCE PDF
70-411 Dumps VCE PDF
70-412 Dumps VCE PDF
70-413 Dumps VCE PDF
70-414 Dumps VCE PDF
70-417 Dumps VCE PDF
70-461 Dumps VCE PDF
70-462 Dumps VCE PDF
70-463 Dumps VCE PDF
70-464 Dumps VCE PDF
70-465 Dumps VCE PDF
70-480 Dumps VCE PDF
70-483 Dumps VCE PDF
70-486 Dumps VCE PDF
70-487 Dumps VCE PDF
220-901 Dumps VCE PDF
220-902 Dumps VCE PDF
N10-006 Dumps VCE PDF
SY0-401 Dumps VCE PDF