Blog

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.

↑ Back to top
  1. Kendra,
    Excellent demo and scripts. Thanks for posting it.

    Ameena

  2. 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.

    • 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!

  3. 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

  4. 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.

  5. Kendra,

    Thank you! Excellent work.

  6. Pingback: Four Underutilized Features of SQL Server Enterprise Edition (and One of Standard Edition) | The SQL Herald

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>