Certification Exam Prep Questions For
Implementing a Microsoft SQL 2016 Data Warehouse (MS-20767)

QuickStart is proud to offer you assessment questions for Implementing a Microsoft SQL 2016 Data Warehouse (MS-20767). Whether you are still deciding which exam to sign up for, or simply want to review and practice the materials necessary to complete certification for this course, We have provided a practice assessment to better aid in guiding you to certification. 100% of the questions are a real questions from a recent version of the test you will have to take for Implementing a Microsoft SQL 2016 Data Warehouse (MS-20767).

Assessment Modules:

1. An Introduction to Database Development

2. Designing and implementing Tables

3. Advanced Table Designs

4. Ensuring Data Integrity Through Constraints:

5. Introduction to Indexes

6. Designing Optimized Index Strategies

7. ColumnStore Indexes

8. Designing and Implementing Views

9. Designing and Implementing Stored Procedures:

10. Designing and Implementing User Defined Functions: Q1

11. Responding to Data Manipulation Via Triggers

12. Responding to Data Manipulation Via Triggers

13. Implementing Managed Code in SQL Server

14. Storing and Querying XML Data in SQL Server

15. Storing and Querying Spatial Data in SQL Server

Module 1

Module 1

Introduction to Data Warehousing

1

What are the components of Data Warehousing Solution?

A. Data Sources: Incorrect.

B. Master Data Management (MDM): Incorrect.

C. Data Models: Incorrect.

D. All of the above: Correct!

2

Column-based index has the following property.

A. Improve performance on row level queries, insert and updates: Incorrect.

B. It is best to use in OLTP database: Incorrect.

C. It is best to use in Data Warehouse: Correct!

D. All the data in a row is processed: Incorrect.

3

Row-based index has the following property

A. Improve performance on queries that scan a table, aggregation and analytical queries: Incorrect.

B. It is best to use in OLTP database: Correct!

C. It is best to use in Data Warehouse: Incorrect.

D. Both rows and columns are processed: Incorrect.

Back To Top

Module 2

Module 2

Planning Data Warehouse Infrastructure

1

How much Data Volume is required for a Small BI System?

A. 10 TB to hundreds of TB: Incorrect.

B. 100 GB Max: Incorrect.

C. Hundreds of GB to 1 TB: Correct!

D. 1 to 10 TB: Incorrect.

2

How much Data Volume is required for a Medium BI System?

A. 10 TB to hundreds of TB: Incorrect.

B. 100 GB Max: Incorrect.

C. Hundreds of GB to 1 TB: Incorrect.

D. 1 to 10 TB: Correct.

3

What is the specific formula for applying cores in a system?

A. ((Average query size in MB ÷ MCR) x Concurrent users) x Target response time: Incorrect.

B. ((Average query size in MB ÷ MCR) x Concurrent users): Incorrect.

C. ((Average query size in MB + MCR) x Concurrent users): Incorrect.

D. ((Average query size in MB ÷ MCR) x Concurrent users) ÷ Target response time: Correct.

Back To Top

Module 3

Module 3

Designing and Implementing a Data Warehouse

1

What properties are true regarding snowflake schema?

A. Each fact table is directly related to its relevant dimension tables: Incorrect.

B. More space is required to store dimension table as compared to start schema: Incorrect.

C. To form a Snowflake schema one or more dimensions may be de-normalized into tables: Incorrect.

D. To form a Snowflake schema one or more dimensions may be normalized into tables: Correct.

2

In dimension table the business key is retained as?

A. Surrogate Key: Incorrect.

B. Alternate Key: Correct!

C. Special Key: Incorrect.

D. BI Key: Incorrect.

3

Which of the following statement is true for tempdb?

A. The tempdb system database can create local temporary tables only: Incorrect.

B. The tempdb system database can create global temporary tables only: Incorrect.

C. tempdb database is used for query processing that requires temporary objects: Correct!

D. tempdb does not work with temporary stored procedures: Incorrect!

Back To Top

Module 3

Module 4

Columnstore Indexes

1

Columnstore indexes can...

A. process data in batches instead of row by row: Incorrect.

B. improve the performance for a typical data warehouse query by up to 10 times: Incorrect.

C. perform well because the columns often store matching data: Incorrect.

D. All of the above: Correct!

2

Which of the following is NOT the characteristic of Clustered Columnstore indexes

A. It must contain all columns: Incorrect.

B. It Optimizes data for storage and performance: Incorrect.

C. It can be filtered: Correct!

D. On top the row bases indexes can be added: Incorrect.

3

What does this statement do “sys.dm_db_index_physical_stats”?

A. The statement is incorrect: Incorrect.

B. Helps to determine the index fragmentation: Correct!

C. The correct statement is dm.sys_db_index_physical_stats: Incorrect.

D. Helps to determine each request that is executing within SQL Server: Incorrect.

Back To Top

Hit button to validate captcha