Quite old but relevant, people fail to understand the concept of partition and indexing and most of the time mismatch it and create lots of confusion, I`ll give this link as a POC how these are different concepts.
You can partition existing tables. While its not possible to have two columns in the Partition Key the same effect can be achieved by concatenating the columns... for example if you have Year | Month | OrderDate 2024 | 04 | 2024-01-01 2024 | 03 | 2023-03-03 the same can be partitioned by Year and Month by adding a new column as shown below Year | Month | OrderDate | YearMon 2024 | 04 | 2024-01-01 | 202404 2024 | 03 | 2023-03-03 | 202403 Please remember that this logic partitions well only because numbers are order correctly Year | Month | OrderDate | YearMon 2024 | 04 | 2024-01-01 | 042024 2024 | 03 | 2023-03-03 | 032024 will result in a totally different partitioning happening
For weekly partition you can use a datepart function I'm SQL server and create a new column called weekno. This should be then be concatenated with year so that first week of 2022 will look like 202201
Hi CalitranoN I have created a brand new video with better audio , a ready to use script and a simpler example below ruclips.net/video/Z9bq7i2yp1Y/видео.html Please have a look at the description to checkout the script. Hope this helped
Hi check out this shorter video which explains the whole thing with scripts that you can try your self. ruclips.net/video/Z9bq7i2yp1Y/видео.html The main performance improvement comes from partition elimination as explained in the above link. It summarize every thing from all four videos into one single 20 min video.
Hello Kurup, I've read some of the discussion in the indicated forum and looks like a hot topic, I want perform some tests in real data but it will be great if you provide your scripts used in this video. Could you please? Thank you in advance! AA
Hi The script can be found here. www.enabledbusinesssolutions.com/blogs/sample-script-for-partitioning/ The video was posted a long time back so the script might need some tweaking if you trying to recreate the example from the video. If you simply trying to implement partitioning then the script should work fine. Jayanth
Thank you so much to explanation of partition and un-partitioned table practically..
Regards
Mohan
im just going to go ahead and.... say thank you this was very helpful :)
Haha ... Go ahead
Quite old but relevant, people fail to understand the concept of partition and indexing and most of the time mismatch it and create lots of confusion, I`ll give this link as a POC how these are different concepts.
VERY NICE VIDEO! It's really help me!
Alfeu Duran thank you glad you liked it
hi Javed ,I am able to view the video, could you let me know what your seeing on your screen and I'll try to help fix it.
How can I create Patition key on a existing table ? Can we create 2 partition keys on a single table ?
You can partition existing tables. While its not possible to have two columns in the Partition Key the same effect can be achieved by concatenating the columns... for example if you have
Year | Month | OrderDate
2024 | 04 | 2024-01-01
2024 | 03 | 2023-03-03
the same can be partitioned by Year and Month by adding a new column as shown below
Year | Month | OrderDate | YearMon
2024 | 04 | 2024-01-01 | 202404
2024 | 03 | 2023-03-03 | 202403
Please remember that this logic partitions well only because numbers are order correctly
Year | Month | OrderDate | YearMon
2024 | 04 | 2024-01-01 | 042024
2024 | 03 | 2023-03-03 | 032024
will result in a totally different partitioning happening
I was trying to creat new partition on new table..Can tell me weekly partition Statment query.
For weekly partition you can use a datepart function I'm SQL server and create a new column called weekno. This should be then be concatenated with year so that first week of 2022 will look like 202201
thank you is there a link to the samples... this was really good i want to try it...!!!
Hi CalitranoN
I have created a brand new video with better audio , a ready to use script and a simpler example below
ruclips.net/video/Z9bq7i2yp1Y/видео.html
Please have a look at the description to checkout the script.
Hope this helped
What is the if not able to see the screen.. please check.
Switch to the highest quality - HD
Why adding a clustered index caused delay in count (*) query as compared to the Heap table
Hi check out this shorter video which explains the whole thing with scripts that you can try your self.
ruclips.net/video/Z9bq7i2yp1Y/видео.html
The main performance improvement comes from partition elimination as explained in the above link.
It summarize every thing from all four videos into one single 20 min video.
Adding clustered index results in additional btree pages that need to be scanned as well.
Hello Kurup,
I've read some of the discussion in the indicated forum and looks like a hot topic, I want perform some tests in real data but it will be great if you provide your scripts used in this video.
Could you please?
Thank you in advance!
AA
Hi Andreson ..hows the testing going ? Just following up if you found anything interesting on SQL 2019
@@JayanthKurup How can i get the scripts from the video? please advise
Hi
The script can be found here.
www.enabledbusinesssolutions.com/blogs/sample-script-for-partitioning/
The video was posted a long time back so the script might need some tweaking if you trying to recreate the example from the video. If you simply trying to implement partitioning then the script should work fine.
Jayanth
Hello you forgot to add the link as promised. Do add this link here,please.
www.enabledbusinesssolutions.com/blogs/sample-script-for-partitioning/
Pls send sample data used in demo
Here is the link with update video and script in description
ruclips.net/video/Z9bq7i2yp1Y/видео.html