Q1. You have a SQL Azure database. 

You execute the following script: 

You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2. 

You plan to deploy an application that will search Column2. 

You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements. 

Which code segment should you execute? 

A. CREATE INDEX IX_Table1 ON Table1 (Column2) WITH FILLFACTOR-0 

B. CREATE INDEX IX_Table1 OK Table1 (Column1) INCLUDE (Column2) 

C. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NULL 

D. CREATE INDEX IX_Table1 ON Table1 (Column2) WHERE Column2 IS NOT NULL 

View Answer

Q2. You plan to migrate an instance of SQL Server 2008 to a new installation of SQL Server 2012. 

You need to migrate alerts and e-mail notifications. 

Which system stored procedures should you use? (Each correct answer presents part of the solution. Choose all that apply.) 

A. sp_syspolicy_create_job 

B. sp_add_operator 

C. sp_audit_write 

D. sp_add_alert 

View Answer

Q3. You plan to create a new table that has the following requirements: 

Uses a GUID data type as the primary key. 

Uses a clustered index as the primary key. 

Minimizes fragmentation. 

You need to recommend which option to include in the CREATE statement. 

Which option should you include? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. NEWID 

B. @@IDENTITY 

C. NEWSEQUENTIALID 

D. IDENTITY 

View Answer

Q4. You need to recommend a solution to improve the performance of usp.UpdateInventory. The solution must minimize the amount of development effort. 

What should you include in the recommendation? 

A. A table variable 

B. A common table expression 

C. A subquery 

D. A cursor 

View Answer

Q5. You have a database that uses the following management views: 

Sys.dm_os_volume_stats 

Sys.dm_db_partition_stats 

Sys.dm_db_file_space_usage 

Sys.fulltext_indexes 

You plan to migrate the database to Microsoft SQL Azure. 

You need to identify which view can be used in SQL Azure. 

Which view should you identify? 

A. sys.fulltext_indexes 

B. sys.dm_db_file_space_usage 

C. sys.dm_os_volume_stats 

D. sys.dm_db_partition_stats 

View Answer

Q6. The database contains a disk-based table named ContentTable that has 1 million rows and a column named Fax. Fax allows null values. 

You need to update Fax to meet the following requirements: 

. Prevent null values from being used. 

. Always use an empty string instead of a null value. 

Which statement or statements should you execute? (Each correct answer presents part of the solution. Choose all that apply.) 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

E. Option E 

View Answer

Q7. You need to write code that will allow the sales force to retrieve data for their reports with the least amount of effort. 

Which code should you use? 

A. Option A 

B. Option B 

C. Option C 

D. Option D 

View Answer

Q8. You need to recommend an isolation level for usp_UpdateOrderDetails. 

Which isolation level should recommend? 

A. Read committed 

B. Repeatable read 

C. Read uncommitted 

D. Serializable 

View Answer

Q9. You plan to execute the following code: 

You need to identify how many rows will be in dbo.Table1 after you execute the code. How many rows should you identify? 

A. 0 

B. 1 

C. 2 

D. 3 

View Answer

Q10. You plan to create a database. 

The database will be used by a Microsoft .NET application for a special event that will last for two days. 

During the event, data must be highly available. 

After the event, the database will be deleted. 

You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications. 

What should you recommend? 

More than one answer choice may achieve the goal. Select the BEST answer. 

A. SQL Server 2014 Enterprise 

B. SQL Server 2014 Standard 

C. SQL Azure 

D. SQL Server 2014 Express with Advanced Services 

View Answer