XML FeedsTo list all the tables that are partitioned you can use the sys.partitions view. However be aware that all tables and indexes in SQL Server contain at least one partition, whether or not they are explicitly partitioned.
If you were to do the following, you would get back every table
- SELECT partition_number,rows,object_name(object_id)
- FROM sys.partitions
So what can you do? Let's take a look. First we are going to create a partitioned table in case you don't have one so that you can get the same output as me.
- CREATE TABLE SalesPartitioned(YearCol SMALLINT NOT NULL,OrderID INT NOT NULL, SomeData UNIQUEIDENTIFIER DEFAULT newsequentialid())
- GO
We are going to insert some data into the table
- INSERT SalesPartitioned (YearCol,OrderID)
- SELECT 2007,number
- FROM master..spt_values
- WHERE type = 'P'
- UNION ALL
- SELECT 2008,number + 2048
- FROM master..spt_values
- WHERE type = 'P'
- UNION ALL
- SELECT 2009,number + 4096
- FROM master..spt_values
- WHERE type = 'P'
We will now add a primary key
- ALTER TABLE dbo.SalesPartitioned ADD CONSTRAINT
- PK_Sales PRIMARY KEY NONCLUSTERED (YearCol,OrderID)
Now it is time to create our partition function. Here is how we will do it
- CREATE PARTITION FUNCTION pfFiscalYear(SMALLINT)
- AS RANGE RIGHT FOR VALUES(2007,2008,2009)
What that does is actually create 4 partitions, one for 2007, one for 2008, one for everything after 2008, and one for everything before 2006.
<=2006
= 2007
= 2008
>= 2009
You can verify this by using the function $partition
- select 1 AS val,$partition.pfFiscalYear(1) AS partition UNION all
- select 2006,$partition.pfFiscalYear(2006) UNION all
- select 2007,$partition.pfFiscalYear(2007) UNION all
- select 2008,$partition.pfFiscalYear(2008) UNION all
- select 2009,$partition.pfFiscalYear(2009) UNION all
- select 2010,$partition.pfFiscalYear(2010) UNION all
- select 3000,$partition.pfFiscalYear(3000)
And here is the output
val partition ----------- ----------- 1 1 2006 1 2007 2 2008 3 2009 4 2010 4 3000 4
Now that we have the partition function, we need a partition scheme. A partition scheme is used to map boundary values in partition functions to filegroups. You can have one filegroup for each year placed on a different spindle, this way you don't have to wait for the disk if all partitions are on the same spindle. For the sake of simplicity we only have one filegroup. Here is how to create the partition scheme
- CREATE PARTITION SCHEME psFiscalYear
- AS PARTITION pfFiscalYear ALL TO ([PRIMARY])
Partition scheme 'psFiscalYear' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'psFiscalYear'.
Now we will add a clustered index and partition this on the YearCol column, the syntax looks like this
- CREATE CLUSTERED INDEX IX_Sales ON SalesPartitioned(YearCol,OrderID)
- ON psFiscalYear(YearCol)
Now it is time to list all the tables that are partitioned. Here is how you do it
- SELECT partition_number,rows,object_name(object_id)
- FROM sys.partitions s
- WHERE EXISTS(SELECT NULL
- FROM sys.partitions s2
- WHERE s.object_id = s2.object_id
- AND partition_number > 1
- AND s.index_id = s2.index_id)
Here is the output
partition_number rows TableName 1 0 SalesPartitioned 2 2048 SalesPartitioned 3 2048 SalesPartitioned 4 2048 SalesPartitioned
As you can see we used WHERE EXISTS, we checked that the object had a partition_number higher than two and we also matched on index_id since a table can have more than one index
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
Nice article.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.