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, ChrisRI 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)
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> 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
>|||> 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
"ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
>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)
>
>
>
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:e7gBSYEBHHA.4212@.TK2MSFTNGP02.phx.gbl...
> and
> to
> it,
> them
>|||Thanks Dan. To piggyback on your idea, I also just stumbled on the
sys.partition_* functions that are proving to be useful.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:56080A38-716B-4144-8694-616FA7B84903@.microsoft.com...
> You can test the partition function using the $PARTITION and sample
values:
> SELECT $PARTITION.myRangePF2(0);
> SELECT $PARTITION.myRangePF2(1);
> SELECT $PARTITION.myRangePF2(2);
>
the[vbcol=seagreen]
> 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
> "ChrisR" <noFudgingWay@.NoEmail.com> wrote in message
> news:eqSA72EBHHA.4864@.TK2MSFTNGP04.phx.gbl...
(PF)[vbcol=seagreen]
and[vbcol=seagreen]
know[vbcol=seagreen]
>