Understand CREATE PARTITION FUNCTION/ SCHEME syntax.

29 Jul 2006 8:16 PM

ChrisR

Howdy all. I was wondering if someone could please (gently) help me to
understand the syntax below? My specific question is that apparently values
for the smalldatetime column below that are NULL will go into the PRIMARY
filegroup. But I don't understand how? I'm not telling it to put NULL's in
that filegroup, so why is it? The way Im reading it, any date < 04/30/06
should be put there, not NULL's.

CREATE PARTITION FUNCTION PF1 (smalldatetime)
AS RANGE LEFT FOR VALUES ('4/30/2006', '5/31/2006', '6/30/2006',
'7/31/2006', '8/31/2006', '9/30/2006');
GO
CREATE PARTITION SCHEME PS1
AS PARTITION PF1
TO ([PRIMARY], FG1, FG2, FG3, FG4, FG5, FG6);

TIA, ChrisR

29 Jul 2006 8:28 PM

Dan Guzman

>But I don't understand how? I'm not telling it to put NULL's in
> that filegroup, so why is it? The way Im reading it, any date < 04/30/06
> should be put there, not NULL's.

Where would you expect NULLs to go? From the Books Online:

<Excerpt href="">

Any rows whose partitioning column has null values are placed in the
left-most partition, unless NULL is specified as a boundary value and RIGHT
is indicated. In this case, the left-most partition is an empty partition,
and NULL values are placed in the following partition.

</Excerpt>

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"ChrisR" <NotACha***@ms.com> wrote in message
news:Ow0YQw0sGHA.1192@TK2MSFTNGP04.phx.gbl...
> Howdy all. I was wondering if someone could please (gently) help me to
> understand the syntax below? My specific question is that apparently
> values
> for the smalldatetime column below that are NULL will go into the PRIMARY
> filegroup. But I don't understand how? I'm not telling it to put NULL's in
> that filegroup, so why is it? The way Im reading it, any date < 04/30/06
> should be put there, not NULL's.
>
> CREATE PARTITION FUNCTION PF1 (smalldatetime)
> AS RANGE LEFT FOR VALUES ('4/30/2006', '5/31/2006', '6/30/2006',
> '7/31/2006', '8/31/2006', '9/30/2006');
> GO
> CREATE PARTITION SCHEME PS1
> AS PARTITION PF1
> TO ([PRIMARY], FG1, FG2, FG3, FG4, FG5, FG6);
>
> TIA, ChrisR
>
>

29 Jul 2006 8:42 PM

ChrisR

From the Books Online:

>
> Any rows whose partitioning column has null values are placed in the
> left-most partition, unless NULL is specified as a boundary value and
RIGHT
> is indicated. In this case, the left-most partition is an empty partition,
> and NULL values are placed in the following partition.

Doh! Waaaay to easy.

Thanks.

Show quote

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:%23cwLA20sGHA.2376@TK2MSFTNGP04.phx.gbl...
> >But I don't understand how? I'm not telling it to put NULL's in
> > that filegroup, so why is it? The way Im reading it, any date < 04/30/06
> > should be put there, not NULL's.
>
> Where would you expect NULLs to go? From the Books Online:
>
> <Excerpt href="">
>
> Any rows whose partitioning column has null values are placed in the
> left-most partition, unless NULL is specified as a boundary value and
RIGHT
> is indicated. In this case, the left-most partition is an empty partition,
> and NULL values are placed in the following partition.
>
> </Excerpt>
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "ChrisR" <NotACha***@ms.com> wrote in message
> news:Ow0YQw0sGHA.1192@TK2MSFTNGP04.phx.gbl...
> > Howdy all. I was wondering if someone could please (gently) help me to
> > understand the syntax below? My specific question is that apparently
> > values
> > for the smalldatetime column below that are NULL will go into the
PRIMARY
> > filegroup. But I don't understand how? I'm not telling it to put NULL's
in
> > that filegroup, so why is it? The way Im reading it, any date < 04/30/06
> > should be put there, not NULL's.
> >
> > CREATE PARTITION FUNCTION PF1 (smalldatetime)
> > AS RANGE LEFT FOR VALUES ('4/30/2006', '5/31/2006', '6/30/2006',
> > '7/31/2006', '8/31/2006', '9/30/2006');
> > GO
> > CREATE PARTITION SCHEME PS1
> > AS PARTITION PF1
> > TO ([PRIMARY], FG1, FG2, FG3, FG4, FG5, FG6);
> >
> > TIA, ChrisR
> >
> >
>
>

29 Jul 2006 8:37 PM

Kalen Delaney

Hi Chris

Every row has to go somewhere, in some partition.
Where are you expecting rows with NULL in the partitioning column to go?

--
HTH
Kalen Delaney, SQL Server MVP

Show quote

"ChrisR" <NotACha***@ms.com> wrote in message
news:Ow0YQw0sGHA.1192@TK2MSFTNGP04.phx.gbl...
> Howdy all. I was wondering if someone could please (gently) help me to
> understand the syntax below? My specific question is that apparently
> values
> for the smalldatetime column below that are NULL will go into the PRIMARY
> filegroup. But I don't understand how? I'm not telling it to put NULL's in
> that filegroup, so why is it? The way Im reading it, any date < 04/30/06
> should be put there, not NULL's.
>
> CREATE PARTITION FUNCTION PF1 (smalldatetime)
> AS RANGE LEFT FOR VALUES ('4/30/2006', '5/31/2006', '6/30/2006',
> '7/31/2006', '8/31/2006', '9/30/2006');
> GO
> CREATE PARTITION SCHEME PS1
> AS PARTITION PF1
> TO ([PRIMARY], FG1, FG2, FG3, FG4, FG5, FG6);
>
> TIA, ChrisR
>
>