SQL Server Table partitioning – DOs and DON’Ts

When do we get improvement in the performance, and when should we better not choosing partitioning but some other optimization techniques instead? Shall we optimize by using partitioning or yet another index?

Tags: Optimising,Developing,Managing,On-prem,performance,partitioning,indexing,Database engine,optimizing

[marcosoliveira]

I have to split a 380GB table in quarters. This class has being very useful. Thank you.

[aozdemiroglu]

The most explanatory video I've ever seen. Thank you

[ThiagoSantos-grjh]

Congratulations! This video was very pleasant to watch and the concepts were clearly and gradually transmitted.

[DJ_Milky_T]

New Subscription from me. Trying to understand the concepts behind it - very clear explanations coupled with a concise demo. Great work!!!

[sudheeranumalasetti]

Hi Margarita,
Thanks much for your wonderful explanation.

[TheAndad]

Very nicely explained, thank you for sharing, Margarita!

[marcosantonioortega]

Wel Done, Margarita..!
Just very well explained.!
Thanks a lot

[andrewhofmann]

This is good stuff. I wish I had more than 1 thumbs up to give.

[Nikola-ftxi]

Great tutorial! Few questions if you can help:

1) If I am partitioning by year (example 2022, 2023, 2024), is it safe to set new partition 2025 few days before new year? Data inserted before 1.1.2025. will still go to partition 2024 and all data inserted after 31.12.2024 will go to new partition?

2) What is the best way to apply partitioning to already partitioned table with more than million rows and with few constrains and indexes? example from 1st question

3) What is downside of using just [Primary] filegroup for all partitions?

[FeelTheWay]

Nice video ! Thank you for sharing.

In 2024 we still cannot apply a partition on an existing table. I don't understand.

[tomasoyola]

So the last part where you explain the nonclustered index wasn´t clear for me. What is the thing that we need to know before creating a non clustered index (The mappings of the columns ?)? And what is the difference between creating it on primary or on the partitions ?

[vaio]

Thanks ! in case you delete table with partition - do all partitions / functions and indexes connected are deleted ?

[buenrostrobuenrostro]

​
What happens with tables related to other tables?

[Helloimtheshiieet]

Hate to say this but you have a backwards approach to this. You append THEN partition new data literally 100% of the time on smaller tables and the VIEW or TOP table is queried by others. Using an example where you are directly querying a partition isn't a thing and you are completely ignoring the most important fundamental DB rule of NORMALIZATION. You have completely ignored normalization in your video/s making it x9238928493284 more complex. You should be partitioning BASE TABLES. So 2/3 of your steps are really just attempting to solely focus on partitioning as it's somehow the holy grail of SQL when it's not. Especially if you use teradata etc. Partitioning is VERY EASY. You should be doing it on base tables not partitioning top tables. Ever.