|
Home > Archive > MS SQL Server > November 2006 > A few questions on Table and Index Partitioning in SQL2K5.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
A few questions on Table and Index Partitioning in SQL2K5.
|
|
| ChrisR 2006-11-09, 7:14 pm |
| 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 2006-11-09, 7:14 pm |
| 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,
& #91;TransactionNumbe
r] [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
>
>
| |
| Dan Guzman 2006-11-10, 7:14 pm |
| > 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,
>
> & #91;TransactionNumbe
r] [int] NOT NULL)
>
> ON myRangePS2 (TransactionNumber)
>
>
>
>
>
>
>
> "ChrisR" < noFudgingWay@NoEmail
.com> wrote in message
> news:e7gBSYEBHHA.4212@TK2MSFTNGP02.phx.gbl...
> and
> to
> it,
> them
>
>
| |
| ChrisR 2006-11-10, 7:14 pm |
| 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@microso
ft.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[color=darkred]
>
> 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)[color=darkred]
and[color=darkred]
know[color=darkred]
>
|
|
|
|
|