You have a partitioned table in an Azure Synapse Analytics dedicated SQL pool. You need to design queries to maximize the benefits of partition elimination. What should you include in the Transact-SQL queries?
Maybe this? https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-parallelization
I think I read somewhere in the docs that you cannot apply complex queries on partition filtering, cannot find it though (not much help I guess, but hopefully better than nothing)
Why ?? Because When you add the "WHERE" clause to your T-SQL query it allows the query optimizer accesses only the relevant partitions to satisfy the filter criteria of the query - which is what partition elimination is all about.
To maximize the benefits of partition elimination in Azure Synapse Analytics dedicated SQL pool, you should include the WHERE clause in your Transact-SQL queries.
The WHERE clause allows you to specify conditions that filter the rows returned by a query. When designing queries for partitioned tables, you can include predicates in the WHERE clause that align with the partitioning scheme. By doing so, the query optimizer can leverage partition elimination to exclude unnecessary partitions from the query execution plan.
Partition elimination is the process of excluding partitions from query processing based on the predicates specified in the WHERE clause. By eliminating partitions that do not contain relevant data, the query performance can be significantly improved.
100% Correct. Think of it this way, you have 36 partitions over Month column for a table. You are interested in a specific month. so in WHERE clause of your select statement, you will give specific month to "eliminate" other 35 partitions scan.
A is surely true. But B also. If you have two tables small a and big B and you're joining them on condition a.some_column = b.some_column big table B would be filtered by the values found in a. An if B is partitioned on "some_column" we have the same effect as with the where clause.
what's the difference between distribution and partition? I don't find any doc online to describe it clearly.
• Horizontal partitioning divides a table into multiple tables that contain the same number of columns.
• A distributed table appears as a single table, but the rows are actually stored across 60 distributions.
If a table have both distribution and Horizontal partition, how are data stored in SQL? For example a customer table, hash-distributed by region and Horizontal Partitioned by year of the activation data.
distribution is a generally used technique for Massive Distributed Computing. we explicitly decide which distribution pattern to be used in Azure DWH, while Hadoop/Hive automatically distributes the table when created.
This section is not available anymore. Please use the main Exam Page.DP-203 Exam Questions
Log in to ExamTopics
Sign in:
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.
Upvoting a comment with a selected answer will also increase the vote count towards that answer by one.
So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.
elimey
Highly Voted 3 years, 3 months agoSG1705
Highly Voted 3 years, 4 months agoIgorLacik
3 years, 4 months agookechi
3 years, 4 months agonoranathalie
2 years, 12 months agonoranathalie
2 years, 11 months agolcss27
6 months, 1 week agoimatheushenrique
Most Recent 1 month, 1 week agokkk5566
1 year, 1 month agovctrhugo
1 year, 4 months agoDeeksha1234
2 years, 2 months agodsp17
2 years, 3 months agoploer
2 years, 8 months agokilowd
2 years, 9 months agoCanary_2021
2 years, 10 months agosparkchu
2 years, 6 months agoLucky_me
2 years, 9 months ago