Partition Scheme using a function

[eavery]

I can't seem to find a way to do the following:

create table part_table (
col1 int,
col2 datetime
) on psX (datename(week,col2))

I want to partition based on the week number of a date field.

So if I enter in data like the following in my part_table:

(1, 1/1/2007) should go into partition 1 for week #1
(52, 12/21/2007) should go into partition 52 for week #52 of the year

I tried adding in a computed column, but it says its nondeterministic.

[Erland Sommarskog]

(ea****@cdc.gov) writes:

I can't seem to find a way to do the following:

create table part_table (
col1 int,
col2 datetime
) on psX (datename(week,col2))

I want to partition based on the week number of a date field.

So if I enter in data like the following in my part_table:

(1, 1/1/2007) should go into partition 1 for week #1
(52, 12/21/2007) should go into partition 52 for week #52 of the year

I tried adding in a computed column, but it says its nondeterministic.

You are out of luck, I'm afraid. datename() is listed as entirely
undeterministic in Books Online. The normal choice would be datepart(),
which Books Online says is deterministic - with two exceptions of one
being the week number. This example shows why:

set datefirst 7
go
select datepart(week, '20070218') -- 8
go
set datefirst 1
go
select datepart(week, '20070218') -- 7
go
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[eavery]

Ok, so I tried this:

create table part_table (
col1 int,
col2 datetime
) on psX ((datepart(dayofyear,col2)/7)+1)

and I got "Incorrect syntax near '('. "

select ((datepart(dayofyear,getdate())/7)+1) will give me the correct
week of the year that I want. (first week starts on the first day of
the year this way)

[Erland Sommarskog]

(ea****@cdc.gov) writes:

Ok, so I tried this:

create table part_table (
col1 int,
col2 datetime
) on psX ((datepart(dayofyear,col2)/7)+1)

and I got "Incorrect syntax near '('. "

select ((datepart(dayofyear,getdate())/7)+1) will give me the correct
week of the year that I want. (first week starts on the first day of
the year this way)

Here is something that completes without errors. I canoot vouch for
that it make much sense, though. I have not looked very much into
partitioned tables myself.
CREATE PARTITION FUNCTION partfun(int) AS
RANGE FOR VALUES ()
go
CREATE PARTITION SCHEME psX AS PARTITION partfun TO ([PRIMARY])
go
create table part_table (
col1 int NOT NULL,
col2 datetime NOT NULL,
weekno AS datepart(dayofyear, col2) / 7 + 1 PERSISTED,
PRIMARY KEY(weekno, col1)
) on psX (weekno)
go
drop table part_table
drop partition scheme psX
go
drop partition function partfun


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[eavery]

Yep - that's exactly what I ended up doing....shame to have to add an
extra column (overhead) to the table to do something which should be
so straightforward.
Thanks for your time!