There’s a secret to learning about SQL Server’s table partitioning feature: you need to get your hands on some code and really play with it in a test environment.
In this tutorial, Kendra will walk you through test scripts that set up a sample table partitioning environment. These scripts demonstrate gotchas to look out for and the cool features that can dramatically change your database’s performance.
Want to run the demos yourself? Download Table Partitioning Tutorial: Scripts.
Part 1: Our Mission
How can you use the demo and scripts from today in the best way possible? Find out here. 3.5 minutes
Part 2: How to create your test database and helper objects
It’s demotime! We create a test database with views that will help us quickly see critical information about out partitioned object. 3.5 minutes
Part 3: How to create partitioned objects
Next up we create a partition function. We create filegroups and files, and use a partition scheme to map the filegroups. We then create a partitioned object and indexes. Our helper views (created in Part 2), help us make sure things went as expected. 15 minutes
Part 4: Switch IN! How to use partition switching to add data to a partitioned table
Now for the cool stuff. In this session we explore how partition switching can allow us to snap a pile of data quickly into a partitioned table– and a major gotcha which can derail the whole process. 12 minutes
Part 5: Switch OUT! How to move data out of a partitioned table and merge boundary points
Deleting data is never fun… unless you’re switching data out of a partitioned table. But make sure to mind those best practices. In this section we show how to remove a lot of data from a partitioned table quickly and safely. 8 minutes
Part 6: Table partitioning takeaways
What are the big things we covered that you need to remember? Make sure you got all the important points here. 3 minutes
Learn More
We’ve got tons more information on table partitioning at http://brentozar.com/go/partitioning
Looking for the scripts? Download Table Partitioning Tutorial: Scripts.
See http://BrentOzar.com/go/eula for our End User Licensing Agreement which governs use of the scripts.
Ameena January 28, 2013 | 1:12 pm
Kendra,
Excellent demo and scripts. Thanks for posting it.
Ameena
Yakov Shlafman January 29, 2013 | 7:47 pm
Hi Kendra,
thanks
Excellent article!
Itzik Ben-Gan uses “Virtual Auxiliary Table of Numbers” in his articles and books.
for example here http://www.sqlmag.com/article/sql-server/virtual-auxiliary-table-of-numbers-103056
I think you have a typo… Create a taily table with 4 million rows… What for? You do not use more than 5,000 rows.
More comments are comming.
Kendra Little January 31, 2013 | 9:20 am
Whenever I load up a numbers table I just throw in a ton of rows in case I want to increase the rowcount by some factor. I love this method of creating the numbers table because it’s so fast!
Chandan Jha January 31, 2013 | 3:38 am
Thanks. I will go through these videos as soon as I can.
FYI,
I could not recognize Kendra as first time I have seen the glasses missing:-)
Regards
Chandan
Kendra Little January 31, 2013 | 9:18 am
Ha! I go through love/hate phases with contact lenses. I’m getting back into the “love” phase, I guess!
Dale Durthaler March 4, 2013 | 1:26 pm
Kendra, I just discoverd you last week and have gone through a number of your videos. I first found you when I was looking for info on SQL DBA jobs and I stumbled across your video “Getting Your First DBA Job”. I use to develope DB systems long ago and in a product called FOCUS. Became a Master in it but it wasn’t Relational. Took another job that exposed me to SQL, DB2 and Relational Database design. I always wanted to work only as a DBA but just never got the opportunity, lost job, struggled to get back into IT, finally landed back on my feet. My current role is ETL Informatica / Cognos but would love an entry level / Mid level DBA job. I’ve been reading all the MS MCITP books related to SQL Server database technology. You’re Video “Getting Your First” you mention that the DBA industry is (((HOT))). I agree, never wanted out of IT, but got caught in the down economy. Now I can’t seem to find anything that a guy that’s been out as long as I, in the DBA areana. I know I can do the job if there is a DBA shop set up in a Team Environment. Thought maybe you could help point me in a direction. I’m willing to relocate for the right $ and Location. Thougths. I’m taking a chance that this gets to you..
Dale.
Alex Kirilin March 15, 2013 | 2:04 am
Kendra,
Thank you! Excellent work.
Pingback: Four Underutilized Features of SQL Server Enterprise Edition (and One of Standard Edition) | The SQL Herald