Ensurepass

QUESTION 81

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

 

 

QUESTION 82

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

    WITH EXECUTE AS USER = ‘dbo’

    AS

    DELETE FROM Purchases.Suppliers WHERE IsActive = 0

    GO

    GRANT EXECUTE ON Purchases.PurgelnactiveSuppliers TO ContosoUser

C.  GRANT SELECT ON Purchases.Suppliers TO ContosoUser

D.  CREATE PROCEDURE Purchases.PurgeInactiveSuppliers

    AS

    DELETE FROM Purchases.Suppliers WHERE IsActive = 0

    GO

GRANT EXECUTE ON Purchases.PurgeInactiveSuppliers TO ContosoUser

 

Correct Answer: D

 

 

QUESTION 83

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

 

Correct Answer: C

 

 

QUESTION 84

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

 

 

QUESTION 85

You administer a Microsoft SQL Server 2012 database. The database contains a Product table created by using the following definition:

 

clip_image001

 

You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?

 

A.      Convert all indexes to Column Store indexes.

B.      Implement Unicode Compression.

C.      Implement row-level compression.

D.      Implement page-level compression.

 

Correct Answer: D

 

 

QUESTION 86

You generate a daily report according to the following query:

 

clip_image003

 

You need to improve the performance of the query. What should you do?

 

A.  Drop the UDF and rewrite the report query as follows:

 

    WITH cte(CustomerID, LastOrderDate) AS (

      SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]

      FROM Sales.SalesOrder

      GROUP BY CustomerID

    )

    SELECT c.CustomerName

    FROM cte

    INNER JOIN Sales.Customer c

    ON cte.CustomerID = c.CustomerID

    WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())

B.  Drop the UDF and rewrite the report query as follows:

 

    SELECT c.CustomerName

    FROM Sales.Customer c

    WHERE NOT EXISTS (

      SELECT s.OrderDate

      FROM Sales.SalesOrder

      WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())

       AND s.CustomerID = c.CustomerID)

C.  Drop the UDF and rewrite the report query as follows:

 

    SELECT DISTINCT c.CustomerName

    FROM Sales.Customer c

    INNER JOIN Sales.SalesOrder s

    ON c.CustomerID = s.CustomerID

    WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE())

D.  Rewrite the report query as follows:

 

    SELECT c.CustomerName

    FROM Sales.Customer c

    WHERE NOT EXISTS (SELECT OrderDate FROM Sales.ufnGetRecentOrders(c.CustomerID, 90))

 

    Rewrite the UDF as follows:

 

    CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime)

    RETURNS TABLE AS RETURN (

      SELECT OrderDate

      FROM Sales.SalesOrder

      WHERE s.CustomerID = @CustomerID

       AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())

 

Correct Answer: A

 

 

QUESTION 87

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

 

Build List and Reorder:

clip_image005

 

Correct Answer:

clip_image006

 

 

QUESTION 88

You create the following stored procedure. (Line numbers are included for reference only.)

 

clip_image007

 

You need to ensure that the stored procedure performs the following tasks:

 

Ÿ   If a record exists, update the record.

Ÿ   If no record exists, insert a new record.

 

Which four Transact-SQL statements should you insert at line 07? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

 

Build List and Reorder:

clip_image009

 

Correct Answer:

clip_image010

 

 

QUESTION 89

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

 

clip_image012

 

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

 

Build List and Reorder:

clip_image014

 

Correct Answer:

clip_image015

 

 

QUESTION 90

You use Microsoft SQL Server 2012 to develop a database application. You create a table by using the following definition:

 

CREATE TABLE Prices (

 PriceId int IDENTITY(1,1) PRIMARY KEY,

 ActualPrice NUMERIC(16,9),

 PredictedPrice NUMERIC(16,9)

)

You need to create a computed column based on a user-defined function named udf_price_index. You also need to ensure that the column supports an index. Which three 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.)

 

Build List and Reorder:

clip_image017

 

 

 

Correct Answer:

clip_image018

 

Comments are closed.