October 24, 2011 at 7:29 am
I'm design a new database and looking at archiving strategies. So far, I'm starting to get familiar with table partitioning. I think I've got it straighten out for one of my tables (an inventory transaction history table). I'm attemtping to do the same with my product table. This table however contains an XML column. I started out with the SerialId being the primary key clustered along with a primary XML index and secondary XML index. When I started looking to partition it, I created a clustered index on the created date in order to the align the partition when I come to find out the throws my XML index completely out. So I decided to only partition the archive, I'm just not sure how to go about creating my clustered/non clustered/xml indexes.
I must admit, I really don't understand this from the SQL books online:
Primary key of the base table. The primary key of the base table is duplicated in the primary XML index for back join with the base table, and the maximum number of columns in primary key of the based table is limited to 15.
...but this is the error I get. The primary key is a user-defined type from varchar(18) and created date is a datetime.
Thanks,
Ryan
October 24, 2011 at 9:15 am
I think I may have gotten it..but if anyone has any comments, the T-SQL code is below
CREATE PARTITION FUNCTION [ProductArchivePF2]
(
datetime
)
AS RANGE RIGHT
FOR VALUES ('12/1/2010')
CREATE PARTITION SCHEME [ProductArchivePS2]
AS PARTITION [ProductArchivePF2]
ALL TO ([HistoryArchive])
CREATE TABLE [Production].[ProductArchive]
(
[SerialId][dbo].[Serial] NOT NULL,
[ModelId]INT NOT NULL,
[Status]VARCHAR(2) NULL,
[SupplierFlag][dbo].[Flag] NULL,
[Detail]XML(CONTENT [Production].[ProductDetailsSchemaCollection]) NULL,
[CreatedDate]DATETIME NOT NULL,
[ModifiedDate]DATETIME NULL
) ON [ProductArchivePS2] (CreatedDate)
ALTER TABLE [Production].[ProductArchive]
ADD CONSTRAINT [PK_ProductArchive_CreatedDate]
PRIMARY KEY CLUSTERED (CreatedDate, SerialId)
ON [ProductArchivePS2] (CreatedDate)
CREATE PRIMARY XML INDEX [PXML_ProductArchive_Detail]
ON [Production].[ProductArchive] (Detail)
WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE XML INDEX [XML_IX_ProductArchive_Detail_Value]
ON [Production].[ProductArchive]
(Detail) USING XML INDEX [PXML_ProductArchive_Detail]
FOR VALUE WITH (STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply