[Free] 2018(Mar) EnsurePass Braindumps Microsoft 70-461 Dumps with VCE and PDF 61-70

By | April 25, 2018
0 Comment

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?

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

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

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

  4. 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:

image

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.

image

image

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:

image

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

Which Transact-SQL query should you use?

  1. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE

    AS

    IF COLUMNS_UPDATED (HomeNumber, MobileNumber)

  • – Create Audit Records

    1. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE

      AS

      IF EXISTS( SELECT HomeNumber from inserted) OR EXISTS (SELECT MobileNumber FROM inserted)

  • – Create Audit Records

    1. CREATE TRIGGER TrgPhoneNumberChange ON Customers FOR UPDATE

      AS

      IF COLUMNS_CHANGED (HomeNumber, MobileNumber)

  • – Create Audit Records

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

    image

    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?

    1. SELECT OrderId, OrderDate, Amount, Name, Country

      FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1

      FOR XML RAW

    2. SELECT OrderId, OrderDate, Amount, Name, Country

      FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1

      FOR XML RAW, ELEMENTS

    3. SELECT OrderId, OrderDate, Amount, Name, Country

      FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1

      FOR XML AUTO

    4. SELECT OrderId, OrderDate, Amount, Name, Country

      FROM Orders INNER JOIN Customers ON Orders.CustomerId – Customers.CustomerId WHERE Customers.CustomerId = 1

      FOR XML AUTO, ELEMENTS

    5. SELECT Name, Country, OrderId, OrderDate, Amount

      FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1

      FOR XML AUTO

    6. SELECT Name, Country, OrderId, OrderDate, Amount

      FROM Orders INNER JOIN Customers ON Orders.CustomerId= Customers.CustomerId WHERE Customers.CustomerId = 1

      FOR XML AUTO, ELEMENTS

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

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

      image

      image

      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:

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

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

        image

        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:

        image

        You need to choose the appropriate data type for the key value. What should you do?

        1. Use the NEWID function to create a unique identifier.

        2. Use the NEWSEQUENTIALID function to create a unique identifier.

        3. Generate a random value that uses the bigint datatype.

        4. 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:

    image

    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.

    image

    1. Option A

    2. Option B

    3. Option C

    4. 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?

    1. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP

      EXISTING

    2. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

    3. ALTER INDEX ALL ON OrderDetail REBUILD

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

    image

    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

    Leave a Reply

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