Posted by Dan Guzman on 06/07/07 12:15

Erland, thanks for providing Piero with he partitioned view example.


> To me that sounds like a managability nightmare. While you can query
> the beast in one query, when you need to flush the rows for Orange
> County, you would have to explicitly to go to the CA table to
> switch partitions, which would mean a lot of dynamic SQL.

It's true that Piero's county import process will need to be state-table
aware. Rather than traditional dynamic SQL, an alternative is SQLCMD
scripts with variables executed via SQLCMD.EXE or SSMS in SQLCMD mode.
IMHO, the SQLCMD variable approach is a bit cleaner.

The example below assumes the state tables, partition functions and
partition schemes all have a state code suffix. The archive and staging
tables use the same partitioning scheme as the primary table to simply
things. This ensures those objects are on the same file groups and also
eliminates the need to create a check constraint on the staging table county
column.


> I don't know if there is any catch with partition views over partitioned
> tables

I must admit I had not considered a partitioned view over partitioned tables
before this thread. I did some cursory testing with Piero's state/county
scenario and it seems to work as expected but there might be gochas. My
biggest concern here is with query complexity when joining the partitioned
view. This approach is probably rarely used so Piero should probably test
thoroughly before committing.


> (I really need to find some time to play with partitioned tables
> to learn them!)

I'm fortunate because I had to develop a complex sliding-window partitioning
scheme for one of our applications that gave me the opportunity to learn the
finer points of SQL 2005 partitioning. There's just so many cool features
in the product nowadays that it's hard to find the time to thoroughly learn
most, let alone all. Now if I could only get fully up to speed on the new
features before SQL Server 2008 ;-)


--sample script for county import process

--define and initialize SQLCMD variables
:setvar StateCode WA
:setvar County Spokane

--create archive table for county
IF OBJECT_ID(N'dbo.CountyArchive', 'U') IS NOT NULL
DROP TABLE dbo.CountyArchive
GO
CREATE TABLE dbo.CountyArchive
(
StateCode char(2) NOT NULL,
County varchar(50) NOT NULL,
CountyData varchar(100),
CONSTRAINT PK_CountyArchive
PRIMARY KEY CLUSTERED (StateCode, County)
ON PS_State_$(StateCode)(county)
)
GO

--create staging table for county
IF OBJECT_ID(N'dbo.CountyStaging', 'U') IS NOT NULL
DROP TABLE dbo.CountyStaging
GO

CREATE TABLE dbo.CountyStaging
(
StateCode char(2) NOT NULL,
County varchar(50) NOT NULL,
CountyData varchar(100),
CONSTRAINT PK_CountyStaging
PRIMARY KEY CLUSTERED (StateCode, County)
ON PS_State_$(StateCode)(county)
)
GO

---------------------------------
--load dbo.CountyStaging table here
---------------------------------

--add constraint needed for parttitioned view
ALTER TABLE dbo.CountyStaging
ADD CONSTRAINT CK_CountyStaging_State CHECK (StateCode = '$(StateCode)')

--move old county data to archive table
ALTER TABLE dbo.State_$(StateCode)
SWITCH PARTITION $PARTITION.PF_State_$(StateCode)('$(County)') TO
dbo.CountyArchive PARTITION
$PARTITION.PF_State_$(StateCode)('$(County)')

--move new county data into state table
ALTER TABLE dbo.CountyStaging
SWITCH PARTITION $PARTITION.PF_State_$(StateCode)('$(County)') TO
dbo.State_$(StateCode) PARTITION
$PARTITION.PF_State_$(StateCode)('$(County)')


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns9947EC5A8DAD2Yazorman@127.0.0.1...
> Piero 'Giops' Giorgi (giorgi.piero@gmail.com) writes:
>>> You might consider a hybrid solution with 50 individual state tables
>>> included in a partitioned view, with each state table partitioned by
>>> county. This approach would leverage partitioning to quickly reload
>>> individual counties yet provide a seamless view of the entire country.
>>
>> That is exactly what I want to do, but unfortunately I'm not (YET)
>> able to do it.
>>
>> How can I have a partitioned view of partitioned tables?
>> I have the 50 state tables partitioned by county, but I can't get to
>> the next step.
>>
>> Can someone post a small example of the thing?
>
> To me that sounds like a managability nightmare. While you can query
> the beast in one query, when you need to flush the rows for Orange
> County, you would have to explicitly to go to the CA table to
> switch partitions, which would mean a lot of dynamic SQL.
>
> I don't know if there is any catch with partition views over partitioned
> tables (I really need to find some time to play with partitioned tables
> to learn them!), but in a normal partitioned view you would have:
>
> CREATE TABLE CA (state char(2) DEFAULT 'CA' CHECK (state = 'CA'),
> -- other columns
> PRIMARY KEY (state, county, whatever))
>
> CREATE TABLE RI (state char(2) DEFAULT 'RI' CHECK (state = 'RI'),
> ...
>
> CREATE VIEW thewholebunch AS
> SELECT state, county, .....
> FROM CA
> UNION ALL
> SELECT state, county, .....
> FROM RI
> ....
>
> But personally I would look into make the merging of new files more
> effective than just dropping all existing rows.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]