Im halfway through this video and it is amazing amd well detailed, thank you! I am well versed in old-school on-prem SQL Server (2005-2012 and some 2016-2019). I now work for a very large company and am part of a project building Azure DW on top of our new Data Lake to replace an old BYOD system. We started with serverless SQL Pool and are now building pipelines to pump data from the Lake into a Dedicated SQL Pool and testing performance. I have so far learned about distribution groups and am using a stored procedure with a CTAS statement to create a new table with only the transaction data from one company in, to speed up future querying. I am using Hash method with the field we use to join in most our queries, and am applying relevant indexes to this table. The next step was learning about partitioning to see if i can speed up the querying of this new table even more. So far you have given me a lot of information on this and i think it will be useful. Its the most interesting project i have been a part of in a long time!
Man, your explanation was amazing! Thank you! Range left looks better for me, range right defines an end and the last partition will full when the new data arrive, so I think we should create new partitions every time
Hello Arshad, I went through many documentation though all were helpful, here at one place, all queries that I had were explained in very simple language with illustrations, it is very helpful.
Its well explained. Thanks for this video. I have a question. Here we are giving boundaries as hardcoded value. Do we have any option to give it dynamically..?
I believe this is a way. This may help, please take a look. docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition#table-partitioning-source-control
Thanks for sharing!, so for a table with 120 million rows, 2 partitions are enough considering the already 60 nodes? This would be nearly 1 million rows per node/partition.
Thanks for wonderful video sir. Its really very informative and easy to understand. I was just trying to see whether synapse support multi column partitions or not As of now it seems it's not there ..just range partition facility I can see... Do you have any recent updates on multi column partitions?? Please
Thanks Somnath! Please have a look at this official documentation: docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition#sizing-partitions
Great tutorial Arshad! I have few unpartitioned tables in azure dedicated SQL pool with nearly 4 Billion records. When I am trying to load the data in the partitioned table using CTAS it is taking so much time. Is there any other option to load the data fast from the unpartitioned to the partitioned table? Thank you in advanced
I have implemented Table Partitioning in Azure Sql Server and my table is working fine but sometimes It takes time to retrieve data like in 1 min it retrieves more than 1 lakh records and sometimes i get only 15000 .Please help me on this
Hi Sir, whatever you explained sql statements, can I use those queries in AZURE SQL DB tables. ?? ... please kindly reply. I will be waiting your valuable reply.
It depends, while they both fall into SQL family of products these are two different engines (SMP vs MPP). Please refer this video for more details: th-cam.com/video/eqVX_Y0ar1M/w-d-xo.html
Im halfway through this video and it is amazing amd well detailed, thank you!
I am well versed in old-school on-prem SQL Server (2005-2012 and some 2016-2019). I now work for a very large company and am part of a project building Azure DW on top of our new Data Lake to replace an old BYOD system.
We started with serverless SQL Pool and are now building pipelines to pump data from the Lake into a Dedicated SQL Pool and testing performance. I have so far learned about distribution groups and am using a stored procedure with a CTAS statement to create a new table with only the transaction data from one company in, to speed up future querying. I am using Hash method with the field we use to join in most our queries, and am applying relevant indexes to this table.
The next step was learning about partitioning to see if i can speed up the querying of this new table even more. So far you have given me a lot of information on this and i think it will be useful.
Its the most interesting project i have been a part of in a long time!
Man, your explanation was amazing! Thank you!
Range left looks better for me, range right defines an end and the last partition will full when the new data arrive, so I think we should create new partitions every time
wow wow Just amazing. You have
done a great job explaining the concepts .
Loved the video, its amazing information.
Awesome explanation.
wonderful explanation as always
This is the best explanation ever! Thank you for sharing.
This was really helpful in implementing sliding window of partition. Thank you
Thankyou sir for the explanation had dilemma regd left and right range partioning but it's now understood
Hello Arshad, I went through many documentation though all were helpful, here at one place, all queries that I had were explained in very simple language with illustrations, it is very helpful.
Thanks Mohan for your feedback! I am happy this was helpful!
Awsome
It was a great session. Can you cover a video on Serverless Performance
Its well explained. Thanks for this video. I have a question. Here we are giving boundaries as hardcoded value. Do we have any option to give it dynamically..?
I believe this is a way. This may help, please take a look. docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition#table-partitioning-source-control
Thanks for sharing. Have one question , do we have to specify every value inside the range right for values () ?
Thanks for sharing!, so for a table with 120 million rows, 2 partitions are enough considering the already 60 nodes? This would be nearly 1 million rows per node/partition.
Thanks for wonderful video sir.
Its really very informative and easy to understand.
I was just trying to see whether synapse support multi column partitions or not
As of now it seems it's not there ..just range partition facility I can see...
Do you have any recent updates on multi column partitions?? Please
Thanks Somnath! Please have a look at this official documentation:
docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition#sizing-partitions
Will it create partition for future dates/years automatically? Please do reply.
Great tutorial Arshad! I have few unpartitioned tables in azure dedicated SQL pool with nearly 4 Billion records. When I am trying to load the data in the partitioned table using CTAS it is taking so much time. Is there any other option to load the data fast from the unpartitioned to the partitioned table? Thank you in advanced
Thanks Krushna for your kind words, glad it was helpful! In this video, I talked about partition switch-in, please try that out.
I have implemented Table Partitioning in Azure Sql Server and my table is working fine but sometimes It takes time to retrieve data like in 1 min it retrieves more than 1 lakh records and sometimes i get only 15000 .Please help me on this
Can we remove partition from table ?
Good video, but it would be better if the background music is turned off. It is very disturbing
Hi Sir, whatever you explained sql statements, can I use those queries in AZURE SQL DB tables. ?? ... please kindly reply. I will be waiting your valuable reply.
It depends, while they both fall into SQL family of products these are two different engines (SMP vs MPP). Please refer this video for more details: th-cam.com/video/eqVX_Y0ar1M/w-d-xo.html