Introducing The New!
Surepassexam Collection
Get Unlimited Access to all
Surepassexam's PREMIUM files
Q1. You need to modify usp_SelectSpeakersByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectSpeakersByName?
A. A table variable
B. An OFFSET-FETCH clause
C. The ROWNUMBER keyword
D. A recursive common table expression
Answer: B
Q2. You are planning the ManufacturingSteps table.
You need to define the ProductID column in the CREATE TABLE statement.
Which code segment should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q3. You are building a stored procedure for a Windows Azure SQL Database. The procedure will add multiple rows to a table.
You need to design the stored procedure to meet the following requirements:
If any of the new rows violates a table constraint, then no further additions must be attempted and all changes made by the stored procedure must be discarded.
If any errors occur, a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure.
What should you include in the design?
A. An explicit transaction that has XACT_ABORT disabled
B. An implicit transaction that has error handling enabled
C. An explicit transaction that has error handling enabled
D. An implicit transaction that has XACT.ABORT enabled
Answer: C
Q4. You are creating a database that will store usernames and credit card numbers for an application.
You need to recommend a solution to store and reuse the credit card numbers in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Data encryption
B. Transparent Data Encryption (TDE)
C. Encrypting File System (EPS)
D. Data hashing
Answer: B
Q5. You have an index for a table in a SQL Azure database. The database is used for Online Transaction Processing (OLTP).
You discover that many page splits occur when records are inserted or updated in the table.
You need to minimize the number of page splits.
What should you set from the index options?
A. FILLFACTOR = 0
B. STATISTICS_NORECOMPUTE = OFF
C. STATISTICS_NORECOMPUTE = ON
D. FILLFACTOR = 80
Answer: D
Q6. You need to implement security for the restore and audit process. What should you do?
A. Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions.
B. Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions.
C. Grant the COFFECORP\Auditors group ALTER ANY CONNECTION and IMPERSONATE ANY LOGIN permissions. Grant the COFFECORP\StoreAgent group ALTER ANY CONNECTION and SELECT ALL USER SECURABLES permissions.
D. Grant the COFFECORP\Auditors group CONNECT ANY DATABASE and SELECT ALL USER SECURABLES permissions. Grant the COFFECORP\StoreAgent group CONNECT ANY DATABASE and IMPERSONATE ANY LOGIN permissions.
Answer: A
Q7. You need to recommend a solution that addresses the concurrency requirement.
What should you recommend?
A. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.
B. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
C. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
D. Modify the stored procedures to update tables in the same order for all of the stored procedures.
Answer: D
Q8. You need to recommend a solution for Application1 that meets the security requirements. What should you include in the recommendation?
A. Signed stored procedures
B. Certificate Authentication
C. Encrypted columns
D. Secure Socket Layer (SSL)
Answer: A
Q9. You need to implement a solution that addresses the bulk insert requirements. What should you add to line 08 in usp_ImportOrderDetails?
A. LASTROW=0.
B. BATCHSIZE=0.
C. BATCHSIZE=1000.
D. LASTROW = 1000.
Answer: C
Q10. An administrator provides a digital certificate named ServerCert.
You need to implement Transparent Data Encryption (TDE) on ProductsDB.
Which code segment should you use?
A. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE DBCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO B. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = TRIPLE_DES_3KEY ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
C. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE PRODUCTSCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
D. USE PRODUCTSDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE DBCERT; GO ALTER DATABASE PRODUCTSDB SET ENCRYPTION ON; GO
Answer: C
Q11. You create a table named Customers by using the following code segment:
You create a non-clustered index named IX_Name on the name column.
You write the following query to retrieve all of the customers that have a name that starts with the letters SMI:
You discover that the query performs a table scan.
You need to ensure that the query uses the index.
What should you do?
A. Replace LEFT(name,3) = 'smi' by using name like 'smi%'
B. Replace LEFT(name,3) = 'smi' by using substring(name,l,3) = 'smi'
C. Recreate IX_Name as a unique index
D. Recreate IX Name as a clustered index
Answer: A
Q12. You have a database that contains a user-defined function named Schema1.Udf1 and two tables named Schema1.Table1 and Schema1.Table2.
Schema1.Table1 has 1 million rows. The schema for Schema1.Table1 is configured as shown in the following table.
Schema1.Udf1 was defined by using the following code:
You need to write a query that will contain the following columns:
Country CountryID CustomerName
The solution must meet the following requirements:
Rows must be returned only if the function returns data.
The amount of time it takes the query to execute must be minimized.
Which query should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: B
Q13. You execute usp_TestSpeakers.
You discover that usp_SelectSpeakersByName uses inefficient execution plans.
You need to update usp_SelectSpeakersByName to ensure that the most efficient execution plan is used.
What should you add at line 30 of Procedures.sql?
A. OPTION (FORCESCAN)
B. OPTION (FORCESEEK)
C. OPTION (OPTIMIZE FOR UNKNOWN)
D. OPTION (OPTIMIZE FOR (@LastName= 'Anderson'))
Answer: C
Q14. You review a query that runs slowly. The query accesses data in a table named Schema1.Table1.
The following is the relevant portion of the execution plan for the query:
You need to create the missing index.
Which code segment should you execute?
A. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1) INCLUDE (Column4) WHERE Column2 <> Column3
B. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1)
C. CREATE NONCLUSTERED INDEX IX1 on Schema1.Table1 (Column1, Column2, Column3) INCLUDE (Column4)
D. CREATE NONCLUSTERED INDEX IX1 on schema1.Table1 (Column1) INCLUDE (Column4)
Answer: C
Q15. You need to recommend a solution for the deployment of SQL Server 2014. The solution must meet the business requirements.
What should you include in the recommendation?
A. Create a new instance of SQL Server 2014 on the server that hosts the SQL Server 2008 instance.
B. Upgrade the existing SQL Server 2008 instance to SQL Server 2014.
C. Deploy two servers that have SQL Server 2014 installed and implement Failover Clustering.
D. Deploy two servers that have SQL Server 2014 installed and implement database mirroring.
Answer: C
Q16. You have a SQL Azure database. You execute the following code:
The Sales.Customers table will contain 100,000 rows. You expect the FaxNumber column to contain a null value for 70 percent of the rows.
You need to create an index to support Sales.CustomersByFaxNumber. The solution must minimize the disk storage requirements.
Which code segment should you execute?
A. CREATE INDEX IX_Customers ON Customers (FaxNumber) WHERE FaxNumber IS NOT NULL
B. CREATE INDEX IX_Customers ON Customers (FaxNumber) WITH FILLFACTOR=0
C. CREATE INDEX IX_Customers ON Customers (CustomerName) INCLUDE (FaxNumber)
D. CREATE INDEX IX_Customers ON Customers (FaxNumber)
E. CREATE INDEX IX_Customers ON Customers (FaxNumber) WHERE FaxNumber IS NULL
Answer: A