Share via

The Wizard's Wand to Partitioning! Creating Staging Tables - adroitly simple

  • Article

Making a superb coffee wending machine is great and we can write volumes on how to use it. Trust me, it wouldnt appeal if you dont quickly write about any feature of the machine that solves a day to day problem for a coffee drinker / Seller. This is exactly what this write up on the wizard is about. Creation of staging table is about dealing with lots of TSQL. The complexity of creating a staging table is linearly , or sometimes exponentially proportional to the complexity of the parent table. This wizard will help you generate this T-SQL just 5 clicks away from start. Read ahead ...

Hold on, What is a staging table?

Large tables are partitioned for both access and storage performance. Sql Server supports horizontal slicing of data in a table based on a partitioning key column. It so happens that, for a sliding window scenario to work , you need to effectively switch in and switch out data. This is the operation where you need a staging table. Assume you have a sales table , partitioned on a monthly basis. The user wants to move the data for January out of the table and move in the data for the month of may into the table. In order to achieve this the user needs a table which

  1. Resembles the parent table exactly interms of metadata. ( Same columns , indexes etc).
  2. Has a constraint on the partition key column to restrict range of data that is valid for the selected partition. ( For switch in of last partition, it is the range of data you want to bring into the table from the existing last boundary value)
  3. and the table is created exactly on the filegroup the partition resides. This helps in switch data or into the partition without movement of data since both the table and the partition are on the same filegroup.

The above described table is called a staging table. The figure below will explain the details.

 

Ah, Now I get it ! Ok, I can't wait to know how the wizard helps me create the staging table?

Ok, Here is how you do it.

  • Right click on the partitioned table in Object Explorer -> Reach the storage option -> and select Manage partition.
  • Read the welcome screen , atleast once :-) and click next.
  • Select the Create Staging table option. [Wait a minute .. I see a Manage Sliding Window Scenario option, which is disabled. I cant take my eyes out of that . Tell me why is it so?] Well , that is because you have indexes on your table which are not storage aligned to the tables partition scheme.This will prevent you from performing the sliding window scenario.My next blog will deal with this. ]
  • Coming back, Once you select the staging table option and click next you reach the staging table creation Window.
  • You select the partiton you want to stage. If it is the last partition, you have to provide the new boundary value. The read only filegroup Field will inform you on the target filegroup of the table.
  • Click next , select a execution option and boom , you have your staging table. Just see the script and you will know what great job this wizard does.

Will write more on sliding window scenarios in the future blogs and how this wizard will help you achieve the same. It is adviced you read the books online in MSDN on this wizard. It has a clear UI driven flow explained. That is the reason , I have refrained from putting more screen shots. See you soon! Oh forgot, its bright and sunny even today , and I can see the katmai ice shine across Alaska.

Comments