June 12, 2012 at 12:56 pm
I have a table:
CREATE TABLE Orders
(OrderID INT IDENTITY(1,1) NOT NULL,
OrderDate DATETIME NOT NULL,
OrderFreight MONEY NULL,
ProductID INT NULL,
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED
(OrderID ASC, OrderDate ASC)
)
ON [primary]
Inside the table I have data and I want to partition it based on OrderDate.
There are 3000 rows for 2008, 2009, 2010, 2011 and 2012.
I can't partition on multiple columns so I create a separate non clustered index on just OrderDate and attach that to my partition scheme.
This now shows up as:
partition_idobject_idindex_idpartition_numberhobt_idrowsfilestream_filegroup_iddata_compressiondata_compression_desc
720575940401561605357522911720575940401561601500000NONE
72057594040549376535752292172057594040549376300000NONE
72057594040614912535752292272057594040614912300000NONE
72057594040680448535752292372057594040680448300000NONE
72057594040745984535752292472057594040745984300000NONE
72057594040811520535752292572057594040811520300000NONE
(Sorry about the formatting)
As you can see I now have the data partitioned how I want but the PK still shows as 15000 rows on the 1st partition. Is this acceptable?
Whenever that index is used (PK_Orders) won't that defeat the whole purpose of partitioning?
Sorry if this seems like a stupid question... Just looking for some input into it.
Thanks
June 12, 2012 at 1:23 pm
What's your reason for partitioning? 15000 rows is a fairly small table, is massive growth expected?
By creating a nonclustered index only on the partition scheme, what you've done is left the table unpartitioned and just partitioned that one nonclustered index
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 12, 2012 at 1:23 pm
jamie_collins (6/12/2012)
I have a table:
CREATE TABLE Orders(OrderID INT IDENTITY(1,1) NOT NULL,
OrderDate DATETIME NOT NULL,
OrderFreight MONEY NULL,
ProductID INT NULL,
CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED
(OrderID ASC, OrderDate ASC)
)
ON [primary]
Inside the table I have data and I want to partition it based on OrderDate.
There are 3000 rows for 2008, 2009, 2010, 2011 and 2012.
I can't partition on multiple columns so I create a separate non clustered index on just OrderDate and attach that to my partition scheme.
This now shows up as:
partition_idobject_idindex_idpartition_numberhobt_idrowsfilestream_filegroup_iddata_compressiondata_compression_desc720575940401561605357522911720575940401561601500000NONE
72057594040549376535752292172057594040549376300000NONE
72057594040614912535752292272057594040614912300000NONE
72057594040680448535752292372057594040680448300000NONE
72057594040745984535752292472057594040745984300000NONE
72057594040811520535752292572057594040811520300000NONE
(Sorry about the formatting)
As you can see I now have the data partitioned how I want but the PK still shows as 15000 rows on the 1st partition. Is this acceptable?
Whenever that index is used (PK_Orders) won't that defeat the whole purpose of partitioning?
Sorry if this seems like a stupid question... Just looking for some input into it.
Thanks
Why on earth would you partition a table that small?
Jared
CE - Microsoft
June 12, 2012 at 1:31 pm
Sorry maybe I should have elaborated...
The actual table is 800 million + rows...
The example is just a made up table for simplicity...
I am curious as to how to get around the issue of multiple columns in a clustered index and one of the columns I want to partition on is part of that index.
Thanks
June 12, 2012 at 1:37 pm
jamie_collins (6/12/2012)
Sorry maybe I should have elaborated...The actual table is 800 million + rows...
The example is just a made up table for simplicity...
I am curious as to how to get around the issue of multiple columns in a clustered index and one of the columns I want to partition on is part of that index.
Thanks
Personally, I would reverse the order of the columns in the primary key. You probably get a lot of fragmentation because of this to begin with. If it is logically reasonable for you to partition on OrderDate, then it seems logical to me to have your data ordered by that column first.
Jared
CE - Microsoft
June 13, 2012 at 6:31 am
It looks like I can't partition an existing table that has any indexes with more than one column defined for it...
If I need to do this then I would have to create the partition, create a new table and move the data to that new table in order to get it partitioned correctly.
June 13, 2012 at 7:27 am
I'm pretty sure that you can (although I may be wrong). What I do know is that you cannot partition a table on the second column of a clustered index. Think about that... If your clustered index (data) is logically stored in the order of OrderID then OrderDate and you try to partition on OrderDate... You would essentially "break" the logical order of the clustered index (data) by splitting the OrderID. So SQL Server does not let you do that. Does that make sense? (maybe you already understood this, but I don't know)
Jared
CE - Microsoft
June 13, 2012 at 7:55 am
Right.
Create Partition on existing data:
1. Created a partition scheme and function.
2. Created OrdersNew table with Clustered index on OrderDate and OrderID and placed both on partition scheme.
3. Loaded Data from Orders table into OrdersNew table.
4. Verified data and partitions were correct and blew away old Orders table and renamed OrdersNew to Orders.
Thanks for your assistance with this.
June 13, 2012 at 8:04 am
Typically with this kind of setup I would make the OrderDate the clustered index and have the PK be a nonclustered index. Makes things a little easier (not much though)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply