Q1. 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:
Q2. You develop a database application. You create four tables. Each table stores different categories of products.
You create a Primary Key field on each table.
You need to ensure that the following requirements are met:
The fields must use the minimum amount of space.
The fields must be an incrementing series of values.
The values must be unique among the four tables.
What should you do?
A. Create a ROWVERSION column.
B. Create a SEQUENCE object that uses the INTEGER data type.
C. Use the INTEGER data type along with IDENTITY
D. Use the UNIQUHDENTTFIER data type along with NEWSEQUENTIALID()
E. Create a TIMESTAMP column.
Answer: D
Q3. You use Microsoft SQL Server 2012 to create a stored procedure as shown in the following code segment. (Line numbers are included for reference only.)
The procedure can be called within other transactions.
You need to ensure that when the DELETE statement from the HumanResourcesJobCandidate table succeeds, the modification is retained even if the insert into the Audit.Log table fails.
Which code segment should you add to line 14?
A. IF @@TRANCOUNT = 0
B. IF (XACT_STATE ( ) ) = 0
C. IF (XACT_STATE ( ) ) = 1
D. IF @@TRANCOUNT = l
Answer: C
Q4. DRAG DROP
You create a view based on the following statement:
You grant the Select permission to User1 for this view.
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 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.)
Answer:
Q5. 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?
A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
C. ALTER INDEX ALL ON OrderDetail REBUILD
D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
Answer: B
Q6. DRAG DROP
You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and Div2Cust.
Each table has columns named DivisionID and CustomerId . None of the rows in Div1Cust exist in Div2Cust.
You need to write a query that meets the following requirements:
The rows in Div1Cust must be combined with the rows in Div2Cust. The result set must have columns named Division and Customer. Duplicates must be retained.
Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)
Answer:
Q7. 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:
Q8. 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
Answer: D
Q9. You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time 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 XACT_ABORT ON
B. SET ARITHABORT ON
C. TRY
D. BEGIN
E. SET ARITHABORT OFF
F. SET XACT_ABORT OFF
Answer: A
Q10. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
. Future modifications to the table definition will not affect the applications' ability to
access data.
. The new object can accommodate data retrieval and data modification.
. You need to achieve this goal by using the minimum amount of changes to the
existing applications.
What should you create for each application?
A. views
B. table partitions
C. table-valued functions
D. stored procedures
Answer: A