CompTIA
Checkpoint
ISC
HP
Dell
EC-Council
EXIN
Fortinet
ITIL®
Juniper
PMI
Microsoft
VMware
Avaya
Google
Salesforce
Amazon
Palo Alto Networks
Certiport
ISC2
All Vendors
  2. Home
  3. Exams
  4. Microsoft
  5. DP-300

Free DP-300 Exam Braindumps (page: 24)

Page 23 of 76
PDF with 369 Questions

You have an Azure SQL database. The database contains a table that uses a columnstore index and is accessed infrequently.
You enable columnstore archival compression.

What are two possible results of the configuration? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.

  1. Queries that use the index will consume more disk I/O.
  2. Queries that use the index will retrieve fewer data pages.
  3. The index will consume more disk space.
  4. The index will consume more memory.
  5. Queries that use the index will consume more CPU resources.

Answer(s): B,E

Explanation:

For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk.

Use columnstore archival compression to further reduce the data size for situations when you can afford extra time and CPU resources to store and retrieve the data.



You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool.
You need to create a surrogate key for the table. The solution must provide the fastest query performance. What should you use for the surrogate key?

  1. an IDENTITY column
  2. a GUID column
  3. a sequence object

Answer(s): A

Explanation:

Dedicated SQL pool supports many, but not all, of the table features offered by other databases. Surrogate keys are not supported. Implement it with an Identity column.


Reference:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-overview



You are designing a star schema for a dataset that contains records of online orders. Each record includes an order date, an order due date, and an order ship date.

You need to ensure that the design provides the fastest query times of the records when querying for arbitrary date ranges and aggregating by fiscal calendar attributes.

Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

  1. Create a date dimension table that has a DateTime key.
  2. Create a date dimension table that has an integer key in the format of YYYYMMDD.
  3. Use built-in SQL functions to extract date attributes.
  4. Use integer columns for the date fields.
  5. Use DateTime columns for the date fields.

Answer(s): B,D

Explanation:

Why use a Date Dimension Table in a Data Warehouse.
The Date dimension is one of these dimension tables related to the Fact. Here is a simple Data Diagram for a Data Mart of Internet Sales information for the
Adventure Works DW database which can be obtained for free from CodePlex or other online sources.



The relationship is created by the surrogate keys columns (integer data type) rather than the date data type.
The query users have to write against a Data Mart are much simpler than against a transaction database. There are less joins because of the one to many relationships between the fact dimension table(s). The dimension tables are confusing to someone who has been normalizing databases as a career. The dimension is a flattened or de-normalized table. This creates cases of duplicate data, but the simplistic query overrides the duplicate data in a dimensional model.


Reference:

https://www.mssqltips.com/sqlservertip/3117/defining-role-playing-dimensions-for-sql-server-analysis-services/
https://community.idera.com/database-tools/blog/b/community_blog/posts/why-use-a-date-dimension-table-in-a-data-warehouse



HOTSPOT (Drag and Drop is not supported)
You are designing an enterprise data warehouse in Azure Synapse Analytics that will store website traffic analytics in a star schema.

You plan to have a fact table for website visits. The table will be approximately 5 GB.

You need to recommend which distribution type and index type to use for the table. The solution must provide the fastest query performance.

What should you recommend? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:


Box 1: Hash
Consider using a hash-distributed table when:

The table size on disk is more than 2 GB.
The table has frequent insert, update, and delete operations.

Box 2: Clustered columnstore
Clustered columnstore tables offer both the highest level of data compression and the best overall query performance.


Reference:

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index






Post your Comments and Discuss Microsoft DP-300 exam with other Community members:

DP-300 Exam Discussions & Posts