Click here to monitor SSC
  • Av rating:
  • Total votes: 169
  • Total comments: 44
Nigel Rivett

Partitioned Tables in SQL Server 2005

16 April 2007

Partitioned Tables in SQL Server 2005

Partitioned tables are a new feature available in SQL Server version 2005, aimed mainly at improving the performance of large database systems. The feature is only available for enterprise and developer edition. For other editions you can get a similar functionality with a partitioned view.

This article focuses on how to create a partitioned table and manipulate the partitions, rather than exploring the performance aspects.

Creating the Partitioned Table

First of all, we will create a partitioned table and prove that it is acting in a partitioned manner, in that the queries will only access the partitions that are required.

To start with, we need to create a partition function. This will define how many partitions exist, and the values contained in partition columns within those partitions.

CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2)

The partition function has been named MyPartitionRange.

The partition column is an int.

'Range left' means that the value is the upper bound for the partition.

The above code will define a partitioned table that contains 3 partitions.

  • Partition 1 – Partition value <= 1
  • Partition 2 – Partition value > 1 and <= 2
  • Partition 3 – Partition value > 2

As the partition column is an integer the partitions will actually be

  • Partition 1 – Partition value <= 1
  • Partition 2 – Partition value =2
  • Partition 3 – Partition value > 2

Note: that the range left / range right determines the partition for data that matches the partition literal value. For integer data this would change the partition number for all the data.

Now we create a partition scheme:

CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY])

The partition scheme is named MyPartitionScheme and references the partition function MyPartitionRange. All of the partitions are to be held on the primary filegroup.

Now we can create the table. All that is needed is to reference the partition scheme naming the partition column in an “on” clause.

CREATE TABLE MyPartitionedTable ( i INT , s CHAR(8000) , PartCol INT ) ON MyPartitionScheme (PartCol)

We can check the table structure via…

SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

…which gives…

partition_id object_id index_id partition_number hobt_id rows
------------------ ----------- --------- ---------------- ------------------ ---- 72057594038714368 229575856 0 1 72057594038714368 0 72057594038779904 229575856 0 2 72057594038779904 0 72057594038845440 229575856 0 3 72057594038845440 0

Now we add some data:

INSERT MyPartitionedTable (i, s, PartCol) SELECT 1, 'a', 1 INSERT MyPartitionedTable (i, s, PartCol) SELECT 2, 'a', 2 INSERT MyPartitionedTable (i, s, PartCol) SELECT 3, 'a', 2 INSERT MyPartitionedTable (i, s, PartCol) SELECT 4, 'a', 3 INSERT MyPartitionedTable (i, s, PartCol) SELECT 5, 'a', 3 INSERT MyPartitionedTable (i, s, PartCol) SELECT 6, 'a', 3 INSERT MyPartitionedTable (i, s, PartCol) SELECT 7, 'a', 4

... and check that the rows have been added to the correct partitions...

SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

… giving as expected…

partition_id object_id index_id partition_number hobt_id rows
------------------- --------- ----------- ---------------- -------------------- ---- 72057594038714368 229575856 0 1 72057594038714368 1 72057594038779904 229575856 0 2 72057594038779904 2 72057594038845440 229575856 0 3 72057594038845440 4

A function, $partition, is available to give the partition number for the data.

SELECT PartitionNo = $partition.MyPartitionRange(PartCol), NumRows = COUNT(*) FROM MyPartitionedTable GROUP BY $partition.MyPartitionRange(PartCol) ORDER BY $partition.MyPartitionRange(PartCol)

The following is a valid statement and shows that a row with partition value 6 would be added to partition 3:

SELECT PartitionNo = $partition.MyPartitionRange(6)

The function gives the row counts for each partition and so can be useful to find which partition holds the data.

Now to test that queries only access the required partition. We make partition 2 very large (this is why we made s a char(8000)).

DECLARE @i INT SELECT @i = 13 WHILE @i > 0 BEGIN SELECT @i = @i - 1 INSERT MyPartitionedTable (i, s, PartCol) SELECT i, s, PartCol FROM MyPartitionedTable WHERE PartCol = 2 END

...and we check the rowcounts ...

SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

…giving as expected:

/* partition_id object_id index_id partition_number hobt_id rows ------------------- ----------- --------- ---------------- -------------------- ----- 72057594041532416 2117582582 0 1 72057594041532416 1 72057594041597952 2117582582 0 2 72057594041597952 16384 72057594041663488 2117582582 0 3 72057594041663488 4 */

On my laptop that is enough to give an appreciable difference in query times – if the first query below is too quick to detect on your machine then increase the number of loops to add rows to the partition.

Now try the queries:

SELECT COUNT(DISTINCT s) FROM MyPartitionedTable WHERE PartCol = 2 SELECT COUNT(DISTINCT s) FROM MyPartitionedTable WHERE PartCol = 1

You should find that the first takes a lot longer than the second because in both cases only one partition is accessed, a single read for partition 1, many reads for partition 2.

Adding and Removing Partitions

In the previous example, we added data to the partition by inserting rows into the partitioned table. It is also possible to populate a table, and then add that table to the partitioned table as a partition. There are many restrictions on the nature of the table and data that can be added.

The command to add the table as a partition is “alter table …. Switch…”. It actually swaps the table with a partition already existing in the partitioned table.

We will now add a new partition to MyPartitionedTable for partition value 3 and then swap it for a new table MyNewPartition.

To add a new partition to MyPartitionedTable, use the split range command on the partition function.

ALTER PARTITION FUNCTION MyPartitionRange () split RANGE (3)

This has added a new partition for partition value 3. The reverse of this is a merge range statement:

ALTER PARTITION FUNCTION MyPartitionRange () merge RANGE (3) SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable') /* partition_id object_id index_id partition_number hobt_id rows -------------------- ----------- --------- ---------------- -------------------- ---- 72057594042056704 322100188 0 1 72057594042056704 1 72057594042122240 322100188 0 2 72057594042122240 2 72057594042187776 322100188 0 4 72057594042187776 1 72057594042253312 322100188 0 3 72057594042253312 3 */

Note that the existing rows for partition value 3 have been moved to partition 3. The row with partition value 4 has been moved to partition 4.

We create a new table to swap with a partition. This table must have the same structure as the partition. It must also include a check constraint to ensure that the partition column values in the table are included in the correct partition. The check constraint must be at least as restrictive as the partition range function for that partition.

CREATE TABLE MyNewPartition ( i INT , s CHAR(8000) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) )

I normally create and populate the table and add the check constraint later. Now we add some data to the new table:

INSERT MyNewPartition SELECT 1, 'a', 3

To perform this operation the partition must be empty, so...

DELETE MyPartitionedTable WHERE PartCol = 3

And we can swap the table with the partition:

ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3

Viewing the partitions:

SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable') /* partition_id object_id index_id partition_number hobt_id rows -------------------- ----------- --------- ---------------- -------------------- ---- 72057594042056704 322100188 0 1 72057594042056704 1 72057594042122240 322100188 0 2 72057594042122240 2 72057594042187776 322100188 0 4 72057594042187776 1 72057594042318848 322100188 0 3 72057594042318848 1 */

We see that the partition has been swapped with the new table.

The advantage of this is that the swap does not move the data – it just updates the metadata so that the table becomes the partition. This means that it is very fast. The table can be created, populated, and then added as a partition, thereby causing minimal impact on the partitioned table.

Switching a populated partition

Of course the partition that is being swapped out would often contain a lot of data – usually this would be used for adding another partition to the right to split up the data. Deleting the data from the partition would not be feasible – nor would the split on a populated partition.

To accomplish this, you would need to first populate the two tables to replace the 'catch all' partition (in our scenario partition 3).

Now create a table MyOldPartition3 – remember this must be the same structure as above. As this is a destination for a switch the check constraint must be less restrictive than that on the partitioned table.

Now switch the partition out:

ALTER TABLE MyPartitionedTable switch PARTITION 3 TO MyOldPartition3

Now the partition split can be carried out on an empty partition and the two partitions switched in without moving any data.

Identities in a partitioned table

Remember that an identity is not guaranteed to be unique or sequential. It just allocates the next value from the current seed. With that in mind nothing that follows should come as a surprise.

For this we will create a new partition function, scheme and table:

CREATE PARTITION FUNCTION MyIdentityPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME MyIdentityPartitionScheme AS PARTITION MyIdentityPartitionRange ALL TO ([PRIMARY]) CREATE TABLE MyIdentityPartitionedTable ( i INT IDENTITY (1,1) , s CHAR(10) , PartCol INT ) ON MyPartitionScheme (PartCol) --And we add some data INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 1 INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'a', 2 INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'b', 1 INSERT MyIdentityPartitionedTable ( s, PartCol) SELECT 'b', 2 SELECT * FROM MyIdentityPartitionedTable

i           s          PartCol
----------- ---------- -----------
1           a          1
3           b          1
2           a          2
4           b          2

Showing that the identity is a property of the partitioned table rather than the partition.

More interesting is what happens when partitions are swapped:

CREATE TABLE MyIdentityPartitionedTableNew ( i INT IDENTITY (1,1) , s CHAR(10 ) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) ) INSERT MyIdentityPartitionedTableNew (s, PartCol) SELECT 'c', 3 INSERT MyIdentityPartitionedTableNew (s, PartCol) SELECT 'd', 3 ALTER TABLE MyIdentityPartitionedTableNew switch TO MyIdentityPartitionedTable PARTITION 3 SELECT * FROM MyIdentityPartitionedTable

i           s          PartCol
----------- ---------- -----------
1           a          1
3           b          1
2           a          2
4           b          2
1           c          3

2           d          3

And we see duplicate identity values from the new partition.

Adding a new row:

INSERT MyIdentityPartitionedTable (s, PartCol) SELECT 'e', 1 SELECT * FROM MyIdentityPartitionedTable

i           s          PartCol
----------- ---------- -----------
1           a          1
3           b          1

5           e          1
2           a          2
4           b          2
1           c          3

2           d          3

We see that the partition swap has not affected the identity seed for the table.

Partitioned tables and Indexes

If the index contains the partitioning column then the index is referred to as being 'aligned' with the table.

If the index uses the same partitioning scheme as the table and is in the same filegroup then the index must be aligned with the table.

For a non-clustered non-unique index the partitioning column can be included to align the index rather than being indexed.

I think it is best to always explicitly include the partitioning column in your indexes.

The following assumes a single filegroup and scheme.

Clustered index

As stated this index must be aligned with the table. If it is not then the partitioning column will be implicitly added as the last column of the index.

CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE MyPartitionedTable ( i INT , j INT , s VARCHAR(MAX) , PartCol INT ) ON MyPartitionScheme (PartCol) CREATE TABLE MyNewPartition ( i INT , j INT , s VARCHAR(MAX) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) ) CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (j) CREATE CLUSTERED INDEX cl_ix ON MyNewPartition (j) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3

…gives the error…

ALTER TABLE SWITCH statement failed. There is no identical index in source table 'tempdb.dbo.MyNewPartition' for the index 'cl_ix' in target table 'tempdb.dbo.MyPartitionedTable' .

Whereas these all succeed:

DROP INDEX MyNewPartition.cl_ix DROP INDEX MyPartitionedTable.cl_ix CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (j, PartCol) CREATE CLUSTERED INDEX cl_ix ON MyNewPartition (j, PartCol) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 DROP INDEX MyNewPartition.cl_ix DROP INDEX MyPartitionedTable.cl_ix CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (PartCol, j) CREATE CLUSTERED indeex cl_ix ON MyNewPartition (PartCol, j) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3

This will also work:

DROP INDEX MyNewPartition.cl_ix DROP INDEX MyPartitionedTable.cl_ix CREATE CLUSTERED INDEX cl_ix ON MyPartitionedTable (j) CREATE CLUSTERED INDEX cl_ix ON MyNewPartition (j, PartCol)

...showing that the partitioning column has been implicitly added to the clustered index in the partitioned table.

Note that this extra column will not be shown by sp_helpindex on the partitioned table nor by scripting the index but it is shown by sys.index_columns

I don’t know why Microsoft decided to add the column to the index automatically. I think it would be less confusing to give an error, thereby forcing the user to add the column explicitly.

Unique index

Unique indexes must contain the partitioning column as an indexed column.

CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE MyPartitionedTable ( i INT , j INT , s VARCHAR(MAX) , PartCol INT ) ON MyPartitionScheme (PartCol) CREATE TABLE MyNewPartition ( i INT , j INT , s VARCHAR(MAX) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) ) CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (j)

...gives the error:

Msg 1908, Level 16, State 1, Line 1 olumn 'PartCol' is partitioning
column of the index 'cl_ix'. Partition columns for a unique index must
be a subset of the index key.

… one of the more explanatory error messages.

The Partitioning column must be part of the index so…

CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (j) include (PartCol)

…will also fail.

Unlike clustered indexes the partitioning column must be explicitly part of the index. These will both work:

CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (j, PartCol) CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (j, PartCol) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (PartCol , j) CREATE UNIQUE INDEX cl_ix ON MyPartitionedTable (PartCol , j) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3

Non-unique index

Non-unique indexes do not need to have the partitioning column as part of the index, it can be an INCLUDE column. If it is not explicitly included, then the column will be automatically added – again this will not appear in sp_helpindex.

CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1,2,3) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE MyPartitionedTable ( i INT , j INT , s VARCHAR(MAX) , PartCol INT ) ON MyPartitionScheme (PartCol) CREATE TABLE MyNewPartition ( i INT , j INT , s VARCHAR(MAX) , PartCol INT CHECK (PartCol = 3 AND PartCol IS NOT NULL) ) CREATE INDEX cl_ix ON MyPartitionedTable (j) CREATE INDEX cl_ix ON MyNewPartition (j) include (PartCol) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3

Is successful, as are…

CREATE INDEX cl_ix ON MyPartitionedTable (j, PartCol) CREATE INDEX cl_ix ON MyNewPartition (j, PartCol) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3 CREATE INDEX cl_ix ON MyPartitionedTable (PartCol, j) CREATE INDEX cl_ix ON MyNewPartition (PartCol, j) ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 3

Scenarios

Partitioning on multiple columns

Although the partitioning column must be a single column, it does not need to be numeric and it can be calculated so that the range can include multiple columns. For instance it is common to partition on datetime data by month. This will work well, because that data is usually in a single column, but what do you do if you have data for multiple companies and you also want to partition by company? For this you could use a computed column for the partitioning column. This will create a computed column using the 'company id' and 'order month' which is then used for the partitions. It will partition three companies for the first three months of 2007.

CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1200701,1200702,1200703,2200701,2200702,2200703,3200701,3200702,3200703) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE CompanyOrders ( Company_id INT , OrderDate datetime , Item_id INT , Quantity INT , OrderValue decimal(19,5) , PartCol AS Company_id * 10000 + CONVERT(VARCHAR( 4),OrderDate,112) persisted ) ON MyPartitionScheme (PartCol)

The computed column must be 'persisted' to form the partitioning column.

We will investigate the maintaining of partitioned data in this table later.

Monthly Data – the sliding range

A common requirement is to partition by month. This means that new month partitions need to be added and possibly old data partitions removed. I will describe the process for the addition of a new partition for later data, to remove an old partition the process is the same except that you swap out two partitions, merge the range and swap in a single table.

We create a partitioned table for data by OrderDate month

CREATE PARTITION FUNCTION MyPartitionRange (datetime) AS RANGE RIGHT FOR VALUES ('20070101', '20070201', '20070301', '20070401') CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE Orders ( OrderDate datetime , Item_id INT , Quantity INT , OrderValue decimal(19,5) ) ON MyPartitionScheme (OrderDate)

This will give four partitions…

OrderDate < '20070101' OrderDate >= '20070101' AND < '20070201' OrderDate >= '20070201' AND < '20070301' OrderDate >= '20070301' AND < '20070401' OrderDate >= '20070401'

Therefore the data will be split intopartitions by month, and we insert some test data:

--insert Orders select '19000101', 1, 1, 1 INSERT Orders SELECT '20070101', 1, 1, 1 INSERT Orders SELECT '20070201', 1, 1, 1 INSERT Orders SELECT '20070301', 1, 1, 1 INSERT Orders SELECT '20070401', 1, 1, 1 INSERT Orders SELECT '20070402', 1, 1, 1 INSERT Orders SELECT '20070501', 1, 1, 1

To add the next month's partition it is possible to just split the range and let the system take care of the data. This though would mean that the data would be off-line for the duration of the operation. It is better to use the experience we have gained in switching partitions to create the new data in separate tables then switch them in. This means that the table would be off-line for a very short time – just while the switch operations are taking place.

If data is being continually added to the partitioned table then a snapshot can be taken, the new tables prepared on this and the switch-in operation will need to take the table off-line, merge the new data with the prepared snapshot data then perform the switch-in. An identity column on the table would help to identify new data added since the snapshot. This would mean longer downtime than for static data but still a lot less than splitting a populated range.

To add a new month's partition of static data:

  1. Create the table containing the new months data.
  2. Create the table containing the data after the new month
  3. Swap out the last month from the partitioned table
  4. Split the (empty) range in the partitioned table
  5. Swap in the new months data
  6. Swap in the table containing the data after the new month
  7. Check the result
  1. Create the table containing the new months data.
  2. In the initial discussion we created a table for this but you might find it easier to create a partitioned table for the operation.

    Note that if you script the existing table and indexes make sure that you remember that not all indexed columns may appear in the index script.

    Also if you take this route you will have to use a new partition function and scheme as you will need to split the range. I prefer to use non-partitioned tables for flexibility.

    To population of the table will depend on where the data resides but wil only affect the production table if you need to read the data from that table.

    CREATE TABLE Orders_200704 ( OrderDate datetime CHECK (OrderDate >= '20070401' AND OrderDate < '20070501' AND OrderDate IS NOT NULL) , Item_id INT , Quantity INT , OrderValue decimal(19,5) ) INSERT Orders_200704 SELECT * FROM Orders WHERE OrderDate >= '20070401 AND OrderDate < '20070501'

  3. Create the table containing the data after the new month
  4. In the same way as the previous table populate with the data that is later than the new month.

    CREATE TABLE Orders_200705 ( OrderDate datetime CHECK (OrderDate >= '20070501' AND OrderDate
    IS NOT NULL) , Item_id INT , Quantity INT , OrderValue decimal(19,5) ) INSERT Orders_200705 SELECT * FROM Orders WHERE OrderDate >= '20070501'

    At this point take the production table off-line for the swap.

  5. Swap out the last month from the partitioned table
  6. For this you will need an empty table to swap the data into. It is tempting again to use a partitioned table for this – if so you will need to create a new partition function and scheme as you would not want to lose the data until after splitting the range on the production table.

    CREATE TABLE Orders_200704_Old ( OrderDate datetime CHECK (OrderDate >= '20070401'
    AND OrderDate IS NOT NULL) , Item_id INT , Quantity INT , OrderValue decimal(19,5) ) ALTER TABLE Orders switch PARTITION 5 TO Orders_200704_Old

  7. Split the (empty) range in the partitioned table
  8. This is done by adding a new value to the partition function.

    ALTER PARTITION FUNCTION MyPartitionRange () split RANGE ('20070501')

    As the partition is empty this should be quick as it just means creating a new empty partition.

  9. Swap in the new month's data
  10. ALTER TABLE Orders_200704 switch TO Orders PARTITION 5

  11. Swap in the table containing the data after the new month
  12. ALTER TABLE Orders_200705 switch TO Orders PARTITION 6

  13. Check the result
  14. SELECT * FROM sys.partitions WHERE OBJECT_ID = OBJECT_ID('Orders')

Gives the expected result:

partition_id         object_id   index_id  partition_number hobt_id              rows
-------------------- ----------- --------- ---------------- -------------------- ----
72057594038845440    213575799   0         1                72057594038845440    1
72057594038910976    213575799   0         2                72057594038910976    1
72057594038976512    213575799   0         3                72057594038976512    1
72057594039042048    213575799   0         4                72057594039042048    1
72057594039173120    213575799   0         5                72057594039173120    2
72057594039238656    213575799   0         6                72057594039238656    1

Now the table Orders_200704_Old can be dropped at your leisure.

Adding a new partition with a computed partition function

We return to the table partitioned by company and month.

CREATE PARTITION FUNCTION MyPartitionRange (INT) AS RANGE LEFT FOR VALUES (1200701,1200702,1200703,2200701,2200702,2200703,3200701,3200702,3200703) CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY]) CREATE TABLE CompanyOrders ( Company_id INT , OrderDate datetime , Item_id INT , Quantity INT , OrderValue decimal(19,5) , PartCol AS Company_id * 10000 + CONVERT(VARCHAR(4),OrderDate,112) persisted ) ON MyPartitionScheme (PartCol)

In order too add a new month to this table, you will need to split each company's range for that month. To add a new company partition means splitting all months for that company.

There is no need to add all the partitions in one process, these operations can be performed one partition at a time. This should not affect the results of queries on the table.

The process of adding the new company or month is the same as for the sliding month data, only with many splits and swaps.

Other uses of partitioned tables

Usually, partitioned tables are used to horizontally, or vertically, partition the data. However, the partitions are sometimes used for different purposes – not partitioning the data at all.

I recently came across a reporting system that was querying a single flat table for aggregated results. The table was about 15Gb in size and could be filtered and grouped on any combination of columns. Indexing by date meant that a report for a year would take about 20 minutes – far too long (performance checked just before the release date of course), in fact anything more than 3 months was unacceptable. Normalising and using a view helped with the retrieval of the filter column data, but the actual report was limited by the amount of data it needed to aggregate.

Due to time constraints and policy, we were not allowed to create a cube and the report could not call a stored procedure. Oddly, there was a lot of flexibility in the query used to extract data but it had to access the single table.

The solution was to create a partitioned table. The first partition (partition value = 1) contained the old data table and would still be slow to access.

The second partition contained aggregated data, aggregated by filter columns that kept the table size less than 200K rows. All partitions have the same structure so those filter columns that were excluded were set to null. Accessing this partition was quick enough for any report.

The report application was then changed to check if the filter/grouping columns were all included in the second partition – if so it appended “and PartCol = 2” to the query otherwise it appended “and PartCol = 1”.

The reporting application could warn the users if they were about to do something that would take a long time.

We then checked how the reports were being used, and selected combinations of columns that could be used for other partitions. The partitions were added to the table (not affecting the system) and then, at a later time, the reporting application changed to use the new partitions.

This would have been easier with a stored procedure as the partitions could have been left as separate tables and the stored procedure could choose which to query, but a stored procedure call was not allowed by the reporting application.

Nigel Rivett

Author profile:

Nigel spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server for about 10 years from v4.2 to v2005, and was awarded Microsoft MVP status in 2003. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

Search for other articles by Nigel Rivett

Rate this article:   Avg rating: from a total of 169 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Nicely done, Nigel
Posted by: Phil Factor (view profile)
Posted on: Tuesday, April 17, 2007 at 7:09 AM
Message: Knowledge gained at the sharp end!

Subject: Nice
Posted by: Anonymous (not signed in)
Posted on: Tuesday, April 17, 2007 at 1:09 PM
Message: Nice article

Subject: Would there be any related DB File Design for Partitioning?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 18, 2007 at 3:01 AM
Message: With the idea to go with the Database Partitioning design, what will be the corresponding db file design should be made, such as file location, backup type available, backup frequency, etc... can be used to streamline the db backup & restore, esp. with the offline become lesser and lesser than before.

Please reply me at brianhou@gmail.com if possible.
Thanks a lot.

Subject: nonunique nonclustered indexes
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 18, 2007 at 5:41 PM
Message: From reading BOL, I understood that non-clustered indexes (unique or otherwise) on partitioned tables do not themselves have to be partitioned, and only on clustered indexes will Microsoft covertly add the partitioning column. (And I agree with you, I'd rather get an error than a system that doesn't work the way I think it does!)

I have not tested this out, since the table we were looking at has an identity column as the clustered primary key--and a dozen child tables with foreign key constraints against it. No way we're adding the partitioning column to all those tables AND the FK definitions! (We're thinking of making the PK non-clustered and not partitioning it, but the project's been tabled while we fight the fires set by the latest round of Production deployments.)

But it's all still new to me. Any informed opinions would be welcome!

Subject: File Groups, backups, et. al.
Posted by: Philip Kelley (view profile)
Posted on: Wednesday, April 18, 2007 at 5:44 PM
Message: I also read Kimberly Tripp's whitepaper on the subject, which focuses more on the file group side of table partitioning. It's at:

http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm


Subject: re - nonunique nonclustered indexes
Posted by: nigelrivett (view profile)
Posted on: Wednesday, April 25, 2007 at 10:43 AM
Message: >> and only on clustered indexes will Microsoft covertly add the partitioning column

That's covered in the article for a single filegroup and function. The for non-clustered non-unique indexes the partitioning column will be included rather than added to the index.
The code given shows this happening.

Subject: General Comment
Posted by: Ron Fowler (not signed in)
Posted on: Wednesday, June 06, 2007 at 12:35 PM
Message: This article is extremely useful to me - a very concise, yet complete, description of partitioned tables - I'm now off to create my own!

Subject: SQL FT Search
Posted by: Vijay (not signed in)
Posted on: Thursday, June 14, 2007 at 3:31 PM
Message: Can we do FT Searching on Partitioned tables?
Can we create different FT catalogs for each partitioned table?

Subject: SQL FT Search
Posted by: Vijay (not signed in)
Posted on: Thursday, June 14, 2007 at 3:54 PM
Message: Can we do FT Searching on Partitioned tables?
Can we create different FT catalogs for each partitioned table?

Subject: Computed Column Partitioning?!
Posted by: cdrkeen (not signed in)
Posted on: Monday, July 09, 2007 at 5:59 AM
Message: Nice idea, but how can I eliminate the one or the other column criteria when selecting from the table?
What I mean is the company, monthly sample.
What if I want to select a particular customer all data, or all data from a particular month and only sometimes a particular customer and particular months or particular customerS and particular Months???
If that now I want packaged in a SP we have a problem right?
It seems the concept hasn't been worked through all the way by MS. Partitioning is a concept of hierarchy which is in this case only one level deep. Next Version I'd expect a tree with many levels. i.e. partitioning on multiple columns and an automatic reorganizing i.e. switching and sliding on a regular basis based on the partition functions' information. In effect we'd all have an easier life and superior performance. For administrative jobs like archive and backup the transaction could include to reorganize and defragment the most current data (that may still reside in the last partition before reorganization has taken place) into their maybe new to be created partitions...
.....

Subject: re:Computed Column Partitioning?!
Posted by: nigelrivett (view profile)
Posted on: Saturday, July 28, 2007 at 11:41 PM
Message: Well you could partition on both month and customer (probably merge all the small customers into a single partition).
The computed column would have a means of separating the customer and month so they could be queried separately.
Then it's a matter of the SP calculating the partitioning values to query.

But you should probably look at why you want to do this.
If it's so that you can add customers as well as months then that's probably the way to do it - but you would index the computed column to get the month and customer id to get the customer and rely on the index to make the search quick rather than use individual computed column values.
If it's a performance issue and you don't want to touch unneeded partitions in the search then the SP would have to select the partitioning column values.

Subject: Thank You
Posted by: sgudavalli (not signed in)
Posted on: Wednesday, August 22, 2007 at 7:44 AM
Message: Great Article. Came to know what is Partioning all and how we do that.

Thank you very much.


Subject: problem with Tripp's Sliding Window Scenario...
Posted by: db042188 (view profile)
Posted on: Wednesday, November 21, 2007 at 11:59 AM
Message: Nigel, I'm getting the following error and resulting scheme after following Tripp's instructions as carefully as possible...

ALTER TABLE SWITCH statement failed. index 'xxx.dbo.Switch.IX_TimeTicks' is in filegroup 'FG_xxx_EventArchive00001' and partition 5 of index 'xxx.dbo.EventArchive.IX_TimeTicks' is in filegroup 'PRIMARY’



The surprising “after look” of the partition scheme is:



PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION

[TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001],

[FG_xxx_EventArchive00003], [FG_xxx_EventArchive00004],

[FG_xxx_EventArchive00005], [PRIMARY], [FG_xxx_EventArchive00001])

I expected …00002,…00003,…00004,…00005,…00001,Primary after trying to drop FG 00001 off the left and then trying to squeeze it in between FG 00005 and Primary.

the statement that fails is ALTER TABLE [dbo].[Switch] SWITCH TO [dbo].[EventArchive]
PARTITION 5


any thoughts? The ugly detail in it's entirety follows...

I began with a partition function as follows:

CREATE PARTITION FUNCTION [TimeTicksRangePFN](bigint) AS RANGE
RIGHT FOR VALUES (633294720000000000, 633320640000000000,
633347424000000000, 633374208000000000, 633399264000000000)

These numbers happen to correspond to the dates 11/1/7, 12/1/7,
1/1/8, 2/1/8 and 3/1/8 in ticks respectively.

I began with a partition scheme as follows:

CREATE PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION
[TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001],
[FG_xxx_EventArchive00002], [FG_xxx_EventArchive00003],
[FG_xxx_EventArchive00004], [FG_xxx_EventArchive00005], [PRIMARY])

While running my "sliding window script" , which I hoped would
1) roll off the oldest partition of my EventArchive table and 2) add a
new partition with a tick boundary that equates to 3/5/8, I get an error
related to 1) my switch out table's index, 2) the same table's Filegroup and 3)
Primary.

After getting the error, I scripted the partition function as a
create in mgt studio and got...

CREATE PARTITION FUNCTION [TimeTicksRangePFN](bigint) AS RANGE
RIGHT FOR VALUES (633320640000000000, 633347424000000000,
633374208000000000, 633399264000000000, 633402720000000000)

..which looks like what I had intended cuz the last boundary is
the tick representation of 3/5/8 and the oldest has rolled off

scripting the scheme produced...

CREATE PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION
[TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001],
[FG_xxx_EventArchive00003], [FG_xxx_EventArchive00004],
[FG_xxx_EventArchive00005], [PRIMARY], [FG_xxx_EventArchive00001])

which looks nothing like what I intended, I thought I'd end up
with ...00002,...00003,...00004,...00005,...00001,PRIMARY

the script steps that seem most relevant start at the 5th step
as follows...

5) creates table [dbo].Switch on the switch out filegroup with
columns, PK and indexes matching exactly those of [dbo].EventArchive

6) switches partition 1 of [dbo].EventArchive to [dbo].Switch

7) ALTER PARTITION FUNCTION TimeTicksRangePFN() MERGE RANGE
(633294720000000000) --this was the oldest date corresponding to 11/1/7

8) truncates [dbo].Switch

9) drops all indexes on [dbo].Switch except a clustered index
(IX_TimeTicks), leaves PK constraint alone

10) ships the new data whose values range from 3/1/8 to less
than 3/5/8 to [dbo].Switch and deletes them from their source

11) recreates all non clustered indexes on [dbo].Switch

12)ALTER TABLE [dbo].[Switch] WITH CHECK ADD CONSTRAINT RangeCK
CHECK ([TimeTicks] < the number of ticks represented by 3/5/8)

13)ALTER PARTITION SCHEME TimeTicksRangePScheme NEXT USED
[FG_xxx_EventArchive00001] --fg isnt really hardcoded

14)ALTER PARTITION FUNCTION TimeTicksRangePFN() SPLIT RANGE (the
number of ticks represented by 3/5/8)

15)ALTER TABLE [dbo].[Switch] SWITCH TO [dbo].[EventArchive]
PARTITION 5


Subject: problem with Tripp's Sliding Window Scenario...
Posted by: db042188 (view profile)
Posted on: Wednesday, November 21, 2007 at 4:18 PM
Message: Nigel, I'm getting the following error and resulting scheme after following Tripp's instructions as carefully as possible...

ALTER TABLE SWITCH statement failed. index 'xxx.dbo.Switch.IX_TimeTicks' is in filegroup 'FG_xxx_EventArchive00001' and partition 5 of index 'xxx.dbo.EventArchive.IX_TimeTicks' is in filegroup 'PRIMARY’



The surprising “after look” of the partition scheme is:



PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION

[TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001],

[FG_xxx_EventArchive00003], [FG_xxx_EventArchive00004],

[FG_xxx_EventArchive00005], [PRIMARY], [FG_xxx_EventArchive00001])

I expected …00002,…00003,…00004,…00005,…00001,Primary after trying to drop FG 00001 off the left and then trying to squeeze it in between FG 00005 and Primary.

the statement that fails is ALTER TABLE [dbo].[Switch] SWITCH TO [dbo].[EventArchive]
PARTITION 5


any thoughts? The ugly detail in it's entirety follows...

I began with a partition function as follows:

CREATE PARTITION FUNCTION [TimeTicksRangePFN](bigint) AS RANGE
RIGHT FOR VALUES (633294720000000000, 633320640000000000,
633347424000000000, 633374208000000000, 633399264000000000)

These numbers happen to correspond to the dates 11/1/7, 12/1/7,
1/1/8, 2/1/8 and 3/1/8 in ticks respectively.

I began with a partition scheme as follows:

CREATE PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION
[TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001],
[FG_xxx_EventArchive00002], [FG_xxx_EventArchive00003],
[FG_xxx_EventArchive00004], [FG_xxx_EventArchive00005], [PRIMARY])

While running my "sliding window script" , which I hoped would
1) roll off the oldest partition of my EventArchive table and 2) add a
new partition with a tick boundary that equates to 3/5/8, I get an error
related to 1) my switch out table's index, 2) the same table's Filegroup and 3)
Primary.

After getting the error, I scripted the partition function as a
create in mgt studio and got...

CREATE PARTITION FUNCTION [TimeTicksRangePFN](bigint) AS RANGE
RIGHT FOR VALUES (633320640000000000, 633347424000000000,
633374208000000000, 633399264000000000, 633402720000000000)

..which looks like what I had intended cuz the last boundary is
the tick representation of 3/5/8 and the oldest has rolled off

scripting the scheme produced...

CREATE PARTITION SCHEME [TimeTicksRangePScheme] AS PARTITION
[TimeTicksRangePFN] TO ([FG_xxx_EventArchive00001],
[FG_xxx_EventArchive00003], [FG_xxx_EventArchive00004],
[FG_xxx_EventArchive00005], [PRIMARY], [FG_xxx_EventArchive00001])

which looks nothing like what I intended, I thought I'd end up
with ...00002,...00003,...00004,...00005,...00001,PRIMARY

the script steps that seem most relevant start at the 5th step
as follows...

5) creates table [dbo].Switch on the switch out filegroup with
columns, PK and indexes matching exactly those of [dbo].EventArchive

6) switches partition 1 of [dbo].EventArchive to [dbo].Switch

7) ALTER PARTITION FUNCTION TimeTicksRangePFN() MERGE RANGE
(633294720000000000) --this was the oldest date corresponding to 11/1/7

8) truncates [dbo].Switch

9) drops all indexes on [dbo].Switch except a clustered index
(IX_TimeTicks), leaves PK constraint alone

10) ships the new data whose values range from 3/1/8 to less
than 3/5/8 to [dbo].Switch and deletes them from their source

11) recreates all non clustered indexes on [dbo].Switch

12)ALTER TABLE [dbo].[Switch] WITH CHECK ADD CONSTRAINT RangeCK
CHECK ([TimeTicks] < the number of ticks represented by 3/5/8)

13)ALTER PARTITION SCHEME TimeTicksRangePScheme NEXT USED
[FG_xxx_EventArchive00001] --fg isnt really hardcoded

14)ALTER PARTITION FUNCTION TimeTicksRangePFN() SPLIT RANGE (the
number of ticks represented by 3/5/8)

15)ALTER TABLE [dbo].[Switch] SWITCH TO [dbo].[EventArchive]
PARTITION 5


Subject: just posted on MSDN...
Posted by: db042188 (view profile)
Posted on: Friday, November 23, 2007 at 9:34 AM
Message: regarding the "sliding window scenario" only, I think I have the relevant info...



The choice of using a LEFT or RIGHT pfn (partition function) boundary direction not only affects the cutoff of data in each partition but also the behavior of the MERGE, NEXT USED and SPLIT commands. I'm not sure at this point why MS chose to make these 3 ALTER commands so dependent on the choice of pfn direction but I guess that doesnt matter now and it looks like Simon was on the right track for part of the problem.



There are at least 3 great articles out there at http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm , http://msdn2.microsoft.com/en-us/library/aa964122.aspx and http://www.simple-talk.com/sql/sql-server-2005/partitioned-tables-in-sql-server-2005/ , each dealing best with the pfn direction used in its own primary example...but reading only one could lead some novices to wrong conclusions about the opposite pfn direction. When different filegroups are thrown into the mix, bad conclusions can compound themselves.



For performance reasons, each article preaches "sliding thru metadata" instead of voluminous data movement.



So the MERGE command (ALTER PARTITION FUNCTION...MERGE RANGE...), responsible here for collapsing 2 partitions into one seems to be most appropriate only after the oldest data has been "switched out" of the partition table at a meta data level. If the pfn direction chosen was RIGHT, this means collapsing partition 1 (never has data) and partition 2 (just got switched out) into the filegroup that contains partition 1. If the pfn direction chosen was LEFT, this means collapsing partition 1 (justy got switched out) and partition 2 (probably contains data) into the filegroup that contains partition 2.



The NEXT USED command (ALTER PARTITION SCHEME...NEXT USED...) seems to be responsible here for specifying which filegroup will contain the next new (previously unused per best practices), "data containg" partition.



Regardless of what command is used (I think there are a couple of choices including SPLIT) to define this new partition in a pfn direction RIGHT scenario, the resulting rightmost (highest) partition will be contained by the NEXT USED filegroup.



It seems that a SPLIT command (ALTER PARTITION FUNCTION...SPLIT RANGE...) is the only choice one would make for defining this new partition in a pfn direction LEFT scenario. The resulting left "half" of this split will be the partition contained by the NEXT USED filegroup.



Because pfn direction RIGHT (partition's data is less than partition's associated boundary value) is more important to my app than the behavior of MERGE, NEXT USED and SPLIT, I will instead define partition 1 at an impossible boundary from the get go. I will map the PRIMARY filegroup to this partition, which in hindsight is what the authors were saying all along. In initial SCHEME definition, instead of what I've shown in the original post, I believe that I'll get away with shifting the other filegroup mappings (one place to the right) and dropping the previous PRIMARY filegroup mapping off altogether.



Finally, I think I'll get away with using the second filegroup mapping (name is actually file group 1 on 1st iteration, or more precisely FG_xxx_EventArchive00001) to create only one switch (staging) table that serves a dual purpose in both "switching out" and "switching in" tasks. The authors seem to prefer two different tables, I believe for recovery purposes.


Subject: Not exactly on the mark for obsoleting a RightRange but VERY good article
Posted by: Anonymous (not signed in)
Posted on: Monday, December 31, 2007 at 4:11 PM
Message: For Obsoleting the oldest partition data, keep a generic file group called "OlderData".

1. Create new switchout table(s) on the "OlderData" FileGroup.
2. Switchout the data from the "OlderData" partition to new table(s) in the same file group.
3. Delete the new table(s).
4. ALTER PARTITION [PartitionFunctionName] MERGE 'YYYYMM01' -- newer partition month boundary

That's it; the OlderData partition now contains one months newer data, and one months older data is gone.

This can be done with a stored procedure that will run in 20 seconds and scheduled as a monthly job. One parameter: the new YYYYMM01 partition boundary is the only parameter that is needed.

Happy Computing in 2008.

Subject: Moving partitions from Dev to UAT/Live
Posted by: Paul G (not signed in)
Posted on: Wednesday, January 30, 2008 at 3:44 AM
Message: Great article. I am struggling though on what might appear to be a minor obstacle. I have experimented with partitioning but now I want to move to an environment with large data volumes. I cannot find a way of building the partitioning scripts or see how the partition structure can be rebuilt/restored.

Subject: Trying to automate Sliding windows example
Posted by: Sten P (not signed in)
Posted on: Friday, February 01, 2008 at 6:59 AM
Message: Since I have several tables, i would like to query the system catalogs for which tables are on a particular partition_scheme or partition function.
I'm a bit lost in the new system catalogs, is there somewhere an data model overview (graphical) or can someone tell me how to do it?

Subject: unique index
Posted by: Saravanakumar.R (not signed in)
Posted on: Monday, February 25, 2008 at 7:44 AM
Message: kindly clarify more about the below question.

since the table we were looking at has an identity column as the clustered primary key--and a dozen child tables with foreign key constraints against it. No way we're adding the partitioning column to all those tables AND the FK definitions! (We're thinking of making the PK non-clustered and not partitioning it, but the project's been tabled while we fight the fires set by the latest round of Production deployments.)


Subject: Thanks
Posted by: Narendra (not signed in)
Posted on: Friday, February 29, 2008 at 5:42 AM
Message: Great Article. Came to know what is Partioning all and how we do that.

Thank you very much
Narendra

Subject: Thanks
Posted by: Narendra Pipaliya (not signed in)
Posted on: Friday, February 29, 2008 at 5:44 AM
Message: Great Article. Came to know what is Partioning all and how we do that.

Thank you very much
Narendra

Subject: request for free material on sql2005 with examples
Posted by: ankhenatraj@hotmail.com (not signed in)
Posted on: Saturday, April 12, 2008 at 4:26 AM
Message: i have refered you article on partitioning table
and index

it is very useful

request for material for students learning for first time sql2005 with examples

kindly send me more material with examples

Subject: Use variables in where clause
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 16, 2008 at 12:08 PM
Message: Hi, I have a question, how come when I use a variable in the where clause
SELECT * FROM Orders WHERE OrderDate >= @myDate AND OrderDate <=@myOtherDate

The search is done in ALL the partitions!

But when I do it with literals
SELECT * FROM Orders WHERE OrderDate >= '20070101' AND OrderDate <= '20070401'

The execution plan only searches the right partitions.

Any Ideas??

Subject: Use variables in where clause
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 16, 2008 at 12:22 PM
Message: Hi, I have a question, how come when I use a variable in the where clause
SELECT * FROM Orders WHERE OrderDate >= @myDate AND OrderDate <=@myOtherDate

The search is done in ALL the partitions!

But when I do it with literals
SELECT * FROM Orders WHERE OrderDate >= '20070101' AND OrderDate <= '20070401'

The execution plan only searches the right partitions.

Any Ideas??

Subject: backup & recovery
Posted by: Anonymous (not signed in)
Posted on: Friday, April 18, 2008 at 10:11 AM
Message: There are any number of articles, blogs, etc. explaining the hows and whys of table partitioning. We, the user community, are sold on the idea but we need some good, solid info on our backup and recovery options. How about it SQL gurus? Thanks.

Subject: Partition
Posted by: Rajasekhar (not signed in)
Posted on: Thursday, April 24, 2008 at 6:17 AM
Message: Its really very good to understand about the partitions. thanks very much

Subject: This inspired professionals to think in new way.
Posted by: Anonymous (not signed in)
Posted on: Monday, June 09, 2008 at 2:26 AM
Message: It is very nice and healthy article. it provides how actually a partitioning actually worlking professionally and i hope it will solve problems of many others Sql Programmers like me.

Thanks for this article.

Subject: This inspired professionals to think in new way.
Posted by: chandansingh (view profile)
Posted on: Monday, June 09, 2008 at 2:29 AM
Message: It is very nice and healthy article. it provides how actually a partitioning actually worlking professionally and i hope it will solve problems of many others Sql Programmers like me.

Thanks for this article.

Subject: Problem
Posted by: Anonymous (not signed in)
Posted on: Monday, June 16, 2008 at 8:56 AM
Message: How can I partition existing tables in my DB. I don't have the luxury to create my DB from scratch coz its a 3rd party application. Also, one of table has over 30 million rows. I want to partition it based on date created (Record) but the clustered index is based on Objectid (totally random). Please help!!

Subject: Problem
Posted by: Anonymous (not signed in)
Posted on: Monday, June 16, 2008 at 8:56 AM
Message: How can I partition existing tables in my DB. I don't have the luxury to create my DB from scratch coz its a 3rd party application. Also, one of table has over 30 million rows. I want to partition it based on date created (Record) but the clustered index is based on Objectid (totally random). Please help!!

Subject: Problem
Posted by: Anonymous (not signed in)
Posted on: Monday, June 16, 2008 at 8:56 AM
Message: How can I partition existing tables in my DB. I don't have the luxury to create my DB from scratch coz its a 3rd party application. Also, one of table has over 30 million rows. I want to partition it based on date created (Record) but the clustered index is based on Objectid (totally random). Please help!!

Subject: Solution for identity problem
Posted by: Anonymous (not signed in)
Posted on: Wednesday, June 18, 2008 at 1:15 AM
Message: In real life, almost every table got an identity field, and primary key. I have been searching on the web for solution for these two realistic problem. What happen if you have a primary key defined on the identity column, and try to switch?

Subject: sql
Posted by: sabri (not signed in)
Posted on: Wednesday, June 25, 2008 at 5:08 PM
Message: I need learing sql beginning video esey.
paese
thank your dr/


Subject: Partition Table
Posted by: Selvarengan (view profile)
Posted on: Monday, September 08, 2008 at 11:30 AM
Message: I need your help on this. I need to convert normal table to Partition table.

My Scenario is
Partition schema,fuction and table created but the table is not mapped with the partition schema data are inserting in the normal table only. now i need to mapped to partition schema. Please give me step by step to do/Query to find out current one and moved one.


Subject: Partition Table
Posted by: Selvarengan (view profile)
Posted on: Monday, September 08, 2008 at 11:52 AM
Message: I need your help on this. I need to convert normal table to Partition table.

My Scenario is
Partition schema,fuction and table created but the table is not mapped with the partition schema data are inserting in the normal table only. now i need to mapped to partition schema. Please give me step by step to do/Query to find out current one and moved one.


Subject: Problem with Foreign Key on Partitioned Table
Posted by: rosaxena (view profile)
Posted on: Wednesday, October 15, 2008 at 2:24 AM
Message: Hi,
I have two tables and main tableand detail table so mdetail table has the foreign key on main table.and both of the tables are partitioned. I created two new table onsame filegroup.

When i run Switch on any of the table. System throw me a message for foreign key issue.

If I delete the foreign key attribute in all the tables.Switch works fine.

Please help

thanks
Rohit

Subject: order by clause in partition table
Posted by: asyed (view profile)
Posted on: Monday, April 27, 2009 at 6:05 PM
Message: It's a good article. I have partition table when I run statement with order by clause using partition column it cost 82% in sort and without order by clause its works fine but if I use same statement withorder by clause on the identicle non partition table it doesnot do sort, both table has similar cluster index.

use mydb
go

declare @P1 char(9)
set @P1=' '

SELECT top 1000
a.t_akcd,a.t_bkyn,a.t_bppr,a.t_bptx,a.t_cact,a.t_cbrn
FROM dbo.ttdsls450900_par a
WHERE (a.t_orno > @P1) ORDER BY 45,75 OPTION (FAST 5). Any comments

Subject: select query on a Partition
Posted by: Dhirendra Trivedi (view profile)
Posted on: Tuesday, May 12, 2009 at 9:13 AM
Message: This is a very good article. It helps us in understanding the concepts quickly.
I have a query

Is there a way to read the records from a single Partion say I want to read record from the first Partition of Orders table?

Do we have anything like Select * from Orders.Partion[1].

Regards,
Dhirendra

Subject: Relation between Table Partition and Cluster Index
Posted by: baghel_ramdas (view profile)
Posted on: Wednesday, July 01, 2009 at 4:43 AM
Message: Hi!
I have a table SO_Orders and need to reduce Read I/O from this table. So I have decide table partitioning on multiple disks (Multiple file groups).
My question is that what will happen when I create cluster index on my partitioned table. How data pages & index pages will be arrange on disk. Or may I create partition on clustered index?
What is the best way table partitioning or index partitioning or both?

Subject: Relation between Table Partition and Cluster Index
Posted by: baghel_ramdas (view profile)
Posted on: Wednesday, July 01, 2009 at 5:35 AM
Message: Hi!
I have a table SO_Orders and need to reduce Read I/O from this table. So I have decide table partitioning on multiple disks (Multiple file groups).
My question is that what will happen when I create cluster index on my partitioned table. How data pages & index pages will be arrange on disk. Or may I create partition on clustered index?
What is the best way table partitioning or index partitioning or both?

Subject: query
Posted by: sunit_82 (view profile)
Posted on: Thursday, November 19, 2009 at 4:46 AM
Message: i want to ask a question.
SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('sometable')

in above query for any table can there be two partition with same partition_number?

Thanx,
sunit.

Subject: Query optimizer bug
Posted by: Kasper Bengtsen (view profile)
Posted on: Thursday, January 06, 2011 at 1:23 AM
Message: Be aware that under certain conditions partitioning will not be an option due to the following bug in the query optimizer:

http://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance

The bug results in certain queries performing a complete index sort, rather than just using the known order of an index to determine the result.
This is very unfortunate if your table contains billions of rows.

Examples of simple queries affected by the bug:
A. "SELECT MAX(ColumnN) FROM PartitionedTable" (with a non-clustered aligned index on ColumnN)
B. "SELECT TOP 10 ColumnX, ColumnY, ColumnZ FROM PartitionedTable ORDER BY ColumnX, ColumnY, ColumnZ" (with a clustered aligned index on ColumnX, ColumnY, ColumnZ)

In our case the above queries resulted in a difference in execution duration of milliseconds vs. hours, for the exact same queries, on non-partitioned and partitioned tables respectively. :(


Microsoft has no plans to fix this bug in current releases of SQL Server, and has no plans to fix it in the next major either.

So for an enterprise solution that allows for you to:
-prioritize data in the same table on physical storage (because all data has to be available online, but only some is frequently queried)
-keep the physical prioritization transparent to the layers above the database (because you already have existing applications using the database, and/or is using ORMs in the data access layer)
-keep your indexes aligned to utilize PARTITION SWITCH (because your maintenance window for moving less relevant data to low priority storage is limited)
-use TOP N/ORDER BY style queries (because who doesn't :) )


... you will have to look elsewhere.

Subject: jonathan_helen@ymail.com
Posted by: helen4love (view profile)
Posted on: Sunday, February 17, 2013 at 5:19 PM
Message:
hello dear
Nice to meet you My name is miss Helen. am a young girl I was impressed when i saw your profile today and i will like to establish a long lasting relationship with you. In addition, i will like you to reply me through my e-mail address(jonathan_helen@ymail.com) so that i will give you my picture of you to know whom i am, please i will like to tell you how much interested i am in knowing more about you, i think we can start from here and share our feelings together as one. please contact me back with my mail address Thanks waiting to hear from you dear.yours new friend (jonathan_helen@ymail.com)

Subject: Computed Columns in WHERE not fetching the proper partitions
Posted by: saurabh.sharma313 (view profile)
Posted on: Sunday, June 02, 2013 at 3:09 PM
Message: Thanks Nigel - This is a great learning , though I have a question -

I have a table and I am partitioning it on the Persisted field as shown in example above (added here as well below)

Even though I have a partiton defined on CompanyId , Orderdate the execution plan does not shows the exact partition when I only take Orderdate in my where condition .
If I take Partcol in where condition - I see a Seek Predicate with the partition from where the record gets fetched .

Please have a look below with Execution Plan -

CREATE PARTITION FUNCTION MyPartitionRange (INT)
AS RANGE LEFT FOR VALUES (1200701,1200702,1200703,2200701,2200702,2200703,3200701,3200702,3200703)

CREATE PARTITION SCHEME MyPartitionScheme AS PARTITION MyPartitionRange ALL TO ([PRIMARY])

CREATE TABLE CompanyOrders ( Company_id INT , OrderDate datetime , Item_id INT ,
Quantity INT , OrderValue decimal(19,5) ,
PartCol AS ((Company_id * 1000000) + Cast(Replace(CONVERT(VARCHAR(6),Orderdate,112),'-','') as int)) PERSISTED)
ON MyPartitionScheme (PartCol)

-- No Partiton Information is being fetched --

Select * from CompanyOrders where Year(Orderdate) = 2007 and DATENAME(quarter,Orderdate) = 1

--Partiton Seek Predicate Shows up in query plan

Select * from CompanyOrders where Year(Orderdate) = 2007 and DATENAME(quarter,Orderdate) = 1 and partcol = '1200701'

We are also planning to use the Persisted field for partitioning our tables and the end user will be using the queries with companyid Or Orderdate in their where condition - The table is huge and because Partcol field will not be used by the user - If Orderdate alone does not picks the proper partition - we will be in trouble as the query will not perform well .

Please advice

--Saurabh

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.