|
Home > Archive > MS SQL Server > November 2006 > Why is the data in Primary instead of my Partition Scheme?
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 |
Why is the data in Primary instead of my Partition Scheme?
|
|
| ChrisR 2006-11-15, 7:14 pm |
| Howdy all. If I create a Primary Function (PF), a Primary Scheme (PS), then
create a table on it and run sp_help, it shows the data_located_on_file
group
as the PS. But then I add a Primary Key (PK) (to the Primary filegroup, I
can't get it to be created anywhere else no matter what I try), run another
sp_help, and now the data_located_on_file
group is Primary. So, is my PS even
going to be used?
CREATE PARTITION FUNCTION & #91;myRangePF2](int)
AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/14/2006
13:41:05 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
--drop table PartitionTest
create TABLE dbo.PartitionTest
(
PTPK int NOT NULL identity(1,1),
salary int NOT NULL
) ON & #91;myRangePS2](sala
ry)
GO
sp_help partitionTest
/*note the data_located_on filegroup location*/
ALTER TABLE dbo.PartitionTest ADD CONSTRAINT
PK_PartitionTest PRIMARY KEY CLUSTERED
(
PTPK
) WITH( STATISTICS_NORECOMPU
TE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [primary]
sp_help partitionTest
/*note the data_located_on new filegroup location*/
TIA, ChrisR
| |
| David Browne 2006-11-15, 7:14 pm |
|
"ChrisR" < noFudgingWay@NoEmail
.com> wrote in message
news:erYL8IOCHHA.4428@TK2MSFTNGP04.phx.gbl...
> Howdy all. If I create a Primary Function (PF), a Primary Scheme (PS),
> then
> create a table on it and run sp_help, it shows the
> data_located_on_file
group
> as the PS. But then I add a Primary Key (PK) (to the Primary filegroup, I
> can't get it to be created anywhere else no matter what I try), run
> another
> sp_help, and now the data_located_on_file
group is Primary. So, is my PS
> even
> going to be used?
>
> CREATE PARTITION FUNCTION & #91;myRangePF2](int)
AS RANGE LEFT FOR VALUES (1,
> 100, 1000)
>
>
>
> /****** Object: PartitionScheme [myRangePS2] Script Date: 11/14/2006
> 13:41:05 ******/
>
> CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
> ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
>
>
>
> --drop table PartitionTest
>
> create TABLE dbo.PartitionTest
>
> (
>
> PTPK int NOT NULL identity(1,1),
>
> salary int NOT NULL
>
> ) ON & #91;myRangePS2](sala
ry)
>
> GO
>
>
>
> sp_help partitionTest
>
> /*note the data_located_on filegroup location*/
>
>
>
> ALTER TABLE dbo.PartitionTest ADD CONSTRAINT
>
> PK_PartitionTest PRIMARY KEY CLUSTERED
>
> (
>
> PTPK
>
> ) WITH( STATISTICS_NORECOMPU
TE = OFF, IGNORE_DUP_KEY = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [primary]
>
If you use a clustered primary key, then the table data is stored as part of
the index. So when you create the clustered index on the primary file
group, you table data is moved from the partition scheme to the primary file
group. You can create a non-clustered index on primary, or use a partition
scheme that includes the primary key column and create the clustered index
on the partition scheme as well.
David
| |
| ChrisR 2006-11-15, 7:14 pm |
| Thanks David! After reading your response I did some testing to make sure I
was reading it right. Strange, it turns out that while I cannot create a PK
on my PS, I can create a Unique Clustered Index on my PS, provided that the
Unique Clustered Index is my partitioned column.
Obviously this is one of those "by design" things, but would anyone know
why? Why would it allow me to create one but not the other?
"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
message news:uZyWbYOCHHA.144@TK2MSFTNGP02.phx.gbl...
>
>
> "ChrisR" < noFudgingWay@NoEmail
.com> wrote in message
> news:erYL8IOCHHA.4428@TK2MSFTNGP04.phx.gbl...
I[color=darkred]
>
> If you use a clustered primary key, then the table data is stored as part
of
> the index. So when you create the clustered index on the primary file
> group, you table data is moved from the partition scheme to the primary
file
> group. You can create a non-clustered index on primary, or use a
partition
> scheme that includes the primary key column and create the clustered index
> on the partition scheme as well.
>
> David
>
| |
| David Browne 2006-11-15, 7:14 pm |
|
"ChrisR" < noFudgingWay@NoEmail
.com> wrote in message
news:umV3OpOCHHA.3604@TK2MSFTNGP03.phx.gbl...
> Thanks David! After reading your response I did some testing to make sure
> I
> was reading it right. Strange, it turns out that while I cannot create a
> PK
> on my PS, I can create a Unique Clustered Index on my PS, provided that
> the
> Unique Clustered Index is my partitioned column.
>
> Obviously this is one of those "by design" things, but would anyone know
> why? Why would it allow me to create one but not the other?
> . . ..
You can create a PK on the partition scheme too:
CREATE PARTITION FUNCTION & #91;myRangePF2](int)
AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/14/2006
13:41:05 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
--drop table PartitionTest
create TABLE dbo.PartitionTest
(
PTPK int NOT NULL identity(1,1),
salary int NOT NULL
) ON [primary]
GO
sp_help partitionTest
/*note the data_located_on filegroup location*/
ALTER TABLE dbo.PartitionTest ADD CONSTRAINT
PK_PartitionTest PRIMARY KEY CLUSTERED
(
PTPK
) WITH( STATISTICS_NORECOMPU
TE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON & #91;myRangePS2](PTPK
)
David
| |
| ChrisR 2006-11-15, 7:14 pm |
| Arggghhhh!!!!
I retract what I said in my last post. I can do pretty much anything I want
in regards to placing PK's on PS's, I just need to make sure and specify the
column name in parenthses after the PS name. Here is what I finally got to
work, and my mistake is commented out for others to learn from. This was a
stupid, time consuming mistake.
CREATE PARTITION FUNCTION & #91;myRangePF2](int)
AS RANGE LEFT FOR VALUES (1,
100, 1000)
/****** Object: PartitionScheme [myRangePS2] Script Date: 11/14/2006
13:41:05 ******/
CREATE PARTITION SCHEME [myRangePS2] AS PARTITION [myRangePF2] TO
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])
--drop table PartitionTest
create TABLE dbo.PartitionTest
(
PTPK int NOT NULL identity(1,1),
salary int NOT NULL
) ON & #91;myRangePS2](sala
ry)
GO
ALTER TABLE dbo.PartitionTest ADD CONSTRAINT
PK_PartitionTest PRIMARY KEY CLUSTERED
(
PTPK
) WITH( STATISTICS_NORECOMPU
TE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [myRangePS2]
/*I was not including the column name below, which was causing all my
problems*/
(ptpk)
sp_help partitionTest
"ChrisR" < noFudgingWay@NoEmail
.com> wrote in message
news:umV3OpOCHHA.3604@TK2MSFTNGP03.phx.gbl...
> Thanks David! After reading your response I did some testing to make sure
I
> was reading it right. Strange, it turns out that while I cannot create a
PK
> on my PS, I can create a Unique Clustered Index on my PS, provided that
the
> Unique Clustered Index is my partitioned column.
>
> Obviously this is one of those "by design" things, but would anyone know
> why? Why would it allow me to create one but not the other?
>
>
> "David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in
> message news:uZyWbYOCHHA.144@TK2MSFTNGP02.phx.gbl...
filegroup,[color=dar
kred]
> I
PS[color=darkred]
(1,[color=darkred]
11/14/2006[color=darkred]
part[color=darkred]
> of
> file
> partition
index[color=darkred]
>
>
|
|
|
|
|