Prev: Change RDL file datsource
Next: Using Machine name in DOS Batch
From: Derek on 29 Jun 2010 21:03
Sorry for the cross post. No one in the other group ever responded so
i'm not sure if it's a lightly read one.

i am using sql server 2008 and i'm new to partition functions

i have 3 tables, all have dates that i want to partition on. is it
considered bad practice to have them
all share the same partition scheme?

Sample:

CREATE PARTITION FUNCTION MY_PARTITION (DATETIME)
AS RANGE RIGHT FOR VALUES('2009-01-01', '2009-07-01', '2010-01-01',
'2010-07-10', '2011-01-01', '2011-07-10')
GO
CREATE PARTITION SCHEME MY_PARTITION_SCHEME AS PARTITION MY_PARTITION
ALL TO ([PRIMARY])
GO
CREATE TABLE TABLE1 (COLUMN1 INT NOT NULL, DATE_COLUMN1 DATETIME) ON
MY_PARTITION_SCHEME(DATE_COLUMN1)
GO
CREATE TABLE TABLE2 (COLUMN2 INT NOT NULL, DATE_COLUMN2 DATETIME) ON
MY_PARTITION_SCHEME(DATE_COLUMN2)
GO
CREATE TABLE TABLE3 (COLUMN3 INT NOT NULL, DATE_COLUMN3 DATETIME) ON
MY_PARTITION_SCHEME(DATE_COLUMN3)
GO

also, if I execute this

ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (COLUMN1);
GO

i received the following error

Msg 1908, Level 16, State 1, Line 3
Column 'DATE_COLUMN1' is partitioning column of the index 'PK_TABLE1'.
Partition columns for a unique index must be a subset of the index
key.

but if I do this

ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (COLUMN1) ON
[PRIMARY];
GO

it works. I don't understand why and I don't know if it's right.

The Books online don't seem to elaborate when discussing multiple
tables using a single scheme.
From: Dan Guzman on 30 Jun 2010 08:21
Also answered in microsoft.public.sqlserver.server.

> ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (COLUMN1);
> GO
>
> i received the following error
>
> Msg 1908, Level 16, State 1, Line 3
> Column 'DATE_COLUMN1' is partitioning column of the index 'PK_TABLE1'.
> Partition columns for a unique index must be a subset of the index
> key.
>

Without the ON clause, the default is that the primary key index is
partitioned the same way as the underlying table. The error is raised
because the partitioning column ((DATE_COLUMN1)) must be part of the key for
unique and clustered indexes when the index is partitioned.

> but if I do this
>
> ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (COLUMN1) ON
> [PRIMARY];
> GO
>
> it works. I don't understand why and I don't know if it's right.

This ON clause specifies that the primary key is not partitioned at all so
there is no requirement that the partitioning column be part of the primary
key. The result is that the primary key index is not aligned with the
underlying table so you cannot use SWITCH to manage individual partitions.

The requirement to include the partitioning column in the clustered index
key as well as unique indexes is a major design consideration with
partitioned tables and indexes.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/


From: Derek on 30 Jun 2010 09:24
On Jun 30, 8:21 am, "Dan Guzman" <guzma...(a)nospam-
online.sbcglobal.net> wrote:
> Also answered in microsoft.public.sqlserver.server.
>
> > ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (COLUMN1);
> > GO
>
> > i received the following error
>
> > Msg 1908, Level 16, State 1, Line 3
> > Column 'DATE_COLUMN1' is partitioning column of the index 'PK_TABLE1'.
> > Partition columns for a unique index must be a subset of the index
> > key.
>
> Without the ON clause, the default is that the primary key index is
> partitioned the same way as the underlying table.  The error is raised
> because the partitioning column ((DATE_COLUMN1)) must be part of the key for
> unique and clustered indexes when the index is partitioned.
>
> > but if I do this
>
> > ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (COLUMN1) ON
> > [PRIMARY];
> > GO
>
> > it works.  I don't understand why and I don't know if it's right.
>
> This ON clause specifies that the primary key is not partitioned at all so
> there is no requirement that the partitioning column be part of the primary
> key.  The result is that the primary key index is not aligned with the
> underlying table so you cannot use SWITCH to manage individual partitions..
>
> The requirement to include the partitioning column in the clustered index
> key as well as unique indexes is a major design consideration with
> partitioned tables and indexes.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVPhttp://weblogs.sqlteam.com/dang/

Thank you Mr Guzman

follow up question if i may

Does sharing multiple tables with the same partition scheme present a
problem?

From: Dan Guzman on 30 Jun 2010 20:55
> Does sharing multiple tables with the same partition scheme present a
> problem?

Sharing the same partition scheme or function is problematic only if you
want to manage the tables independently. MERGE and SPLIT will apply to all
of the partition schemes that use the altered function, which will in turn
affect all of the tables and indexes that use the schemes.. This can be a
good or bad thing depending on what you want to accomplish. The partition
scheme filegroup mapping will of course be the same for all of the
referencing tables and indexes too.



--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Derek" <gepetto_2000(a)yahoo.com> wrote in message
news:96648a90-6a3c-4707-b4c9-d233f46378ad(a)g19g2000yqc.googlegroups.com...
> On Jun 30, 8:21 am, "Dan Guzman" <guzma...(a)nospam-
> online.sbcglobal.net> wrote:
>> Also answered in microsoft.public.sqlserver.server.
>>
>> > ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (COLUMN1);
>> > GO
>>
>> > i received the following error
>>
>> > Msg 1908, Level 16, State 1, Line 3
>> > Column 'DATE_COLUMN1' is partitioning column of the index 'PK_TABLE1'.
>> > Partition columns for a unique index must be a subset of the index
>> > key.
>>
>> Without the ON clause, the default is that the primary key index is
>> partitioned the same way as the underlying table. The error is raised
>> because the partitioning column ((DATE_COLUMN1)) must be part of the key
>> for
>> unique and clustered indexes when the index is partitioned.
>>
>> > but if I do this
>>
>> > ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (COLUMN1) ON
>> > [PRIMARY];
>> > GO
>>
>> > it works. I don't understand why and I don't know if it's right.
>>
>> This ON clause specifies that the primary key is not partitioned at all
>> so
>> there is no requirement that the partitioning column be part of the
>> primary
>> key. The result is that the primary key index is not aligned with the
>> underlying table so you cannot use SWITCH to manage individual
>> partitions.
>>
>> The requirement to include the partitioning column in the clustered index
>> key as well as unique indexes is a major design consideration with
>> partitioned tables and indexes.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
> Thank you Mr Guzman
>
> follow up question if i may
>
> Does sharing multiple tables with the same partition scheme present a
> problem?
>
 | 
Pages: 1
Prev: Change RDL file datsource
Next: Using Machine name in DOS Batch