A few questions on Table and Index Partitioning in SQL2K5.  
Author Message
Filipa





PostPosted: Thu Nov 09 17:22:00 CST 2006 Top

SQL Server >> A few questions on Table and Index Partitioning in SQL2K5.

Howdy all. I've got a couple questions regarding Partition Function (PF) and
Partition Scheme (PS) and I was wondering if someone could assist?

1. How do I assign a table to a Partition Scheme? I created a test PF and
PS, but how could they really partition my table if the table doesnt know to
use them? I know I can assign the table to a filegroup that has a PS on it,
but didn't know if that alone would cut it?

2. Is there some place I can verify all my settings once I think I have them
right?

3. Would it ever make sense to partition an index without partitioning the
table it resides on?

TIA, ChrisR

SQL Server41  
 
 
ChrisR





PostPosted: Thu Nov 09 17:22:00 CST 2006 Top

SQL Server >> A few questions on Table and Index Partitioning in SQL2K5. I got number 1, but could still use help with 2 and 3. Here is number 1.

CREATE PARTITION FUNCTION myRangePF2 (int)

AS RANGE LEFT FOR VALUES (1, 100, 1000);

GO



CREATE PARTITION SCHEME myRangePS2

AS PARTITION myRangePF2

TO ( fgtest1, fgtest1, fgtest1, fgtest2 );



CREATE TABLE [dbo].[PartitionTest](

[Table1ID] [int] IDENTITY(1,1) NOT NULL,

[TransactionNumber] [int] NOT NULL)

ON myRangePS2 (TransactionNumber)









> Howdy all. I've got a couple questions regarding Partition Function (PF)
and
> Partition Scheme (PS) and I was wondering if someone could assist?
>
> 1. How do I assign a table to a Partition Scheme? I created a test PF and
> PS, but how could they really partition my table if the table doesnt know
to
> use them? I know I can assign the table to a filegroup that has a PS on
it,
> but didn't know if that alone would cut it?
>
> 2. Is there some place I can verify all my settings once I think I have
them
> right?
>
> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?
>
> TIA, ChrisR
>
>


 
 
Dan





PostPosted: Fri Nov 10 08:00:44 CST 2006 Top

SQL Server >> A few questions on Table and Index Partitioning in SQL2K5. > 2. Is there some place I can verify all my settings once I think I have
> them right?

You can test the partition function using the $PARTITION and sample values:

SELECT $PARTITION.myRangePF2(0);
SELECT $PARTITION.myRangePF2(1);
SELECT $PARTITION.myRangePF2(2);

> 3. Would it ever make sense to partition an index without partitioning the
> table it resides on?

Off the top of my head, I guess you might do this if you had a heap (table
with no clustered index) and partitioned only for index manageability. In
most cases you'll have a clustered index and align as well.

--
Hope this helps.

Dan Guzman
SQL Server MVP



>I got number 1, but could still use help with 2 and 3. Here is number 1.
>
> CREATE PARTITION FUNCTION myRangePF2 (int)
>
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
>
> GO
>
>
>
> CREATE PARTITION SCHEME myRangePS2
>
> AS PARTITION myRangePF2
>
> TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
>
>
>
> CREATE TABLE [dbo].[PartitionTest](
>
> [Table1ID] [int] IDENTITY(1,1) NOT NULL,
>
> [TransactionNumber] [int] NOT NULL)
>
> ON myRangePS2 (TransactionNumber)
>
>
>
>
>
>
>


>> Howdy all. I've got a couple questions regarding Partition Function (PF)
> and
>> Partition Scheme (PS) and I was wondering if someone could assist?
>>
>> 1. How do I assign a table to a Partition Scheme? I created a test PF and
>> PS, but how could they really partition my table if the table doesnt know
> to
>> use them? I know I can assign the table to a filegroup that has a PS on
> it,
>> but didn't know if that alone would cut it?
>>
>> 2. Is there some place I can verify all my settings once I think I have
> them
>> right?
>>
>> 3. Would it ever make sense to partition an index without partitioning
>> the
>> table it resides on?
>>
>> TIA, ChrisR
>>
>>
>
>

 
 
ChrisR





PostPosted: Fri Nov 10 09:34:04 CST 2006 Top

SQL Server >> A few questions on Table and Index Partitioning in SQL2K5. Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.




> > 2. Is there some place I can verify all my settings once I think I have
> > them right?
>
> You can test the partition function using the $PARTITION and sample
values:
>
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
>
> > 3. Would it ever make sense to partition an index without partitioning
the
> > table it resides on?
>
> Off the top of my head, I guess you might do this if you had a heap (table
> with no clustered index) and partitioned only for index manageability. In
> most cases you'll have a clustered index and align as well.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>


> >I got number 1, but could still use help with 2 and 3. Here is number 1.
> >
> > CREATE PARTITION FUNCTION myRangePF2 (int)
> >
> > AS RANGE LEFT FOR VALUES (1, 100, 1000);
> >
> > GO
> >
> >
> >
> > CREATE PARTITION SCHEME myRangePS2
> >
> > AS PARTITION myRangePF2
> >
> > TO ( fgtest1, fgtest1, fgtest1, fgtest2 );
> >
> >
> >
> > CREATE TABLE [dbo].[PartitionTest](
> >
> > [Table1ID] [int] IDENTITY(1,1) NOT NULL,
> >
> > [TransactionNumber] [int] NOT NULL)
> >
> > ON myRangePS2 (TransactionNumber)
> >
> >
> >
> >
> >
> >
> >


> >> Howdy all. I've got a couple questions regarding Partition Function
(PF)
> > and
> >> Partition Scheme (PS) and I was wondering if someone could assist?
> >>
> >> 1. How do I assign a table to a Partition Scheme? I created a test PF
and
> >> PS, but how could they really partition my table if the table doesnt
know
> > to
> >> use them? I know I can assign the table to a filegroup that has a PS on
> > it,
> >> but didn't know if that alone would cut it?
> >>
> >> 2. Is there some place I can verify all my settings once I think I have
> > them
> >> right?
> >>
> >> 3. Would it ever make sense to partition an index without partitioning
> >> the
> >> table it resides on?
> >>
> >> TIA, ChrisR
> >>
> >>
> >
> >
>