Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 partition information

Author  Topic 

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2014-11-19 : 17:37:01
I've created the partition function and partition schema as below:



CREATE PARTITION FUNCTION ProvUT_MasterData_XXPFN(CHAR(2)) AS
RANGE FOR VALUES
('CA','OH','IL','NM','FL','MC','MI','SC','UT','TX','WA','WI' )

CREATE PARTITION SCHEME ProvUT_MasterData_XXPScheme AS
PARTITION ProvUT_MasterData_XXPFN ALL TO ([Primary])


CREATE TABLE [dbo].[ProvUT_MasterData_XX](
[MemberId] [int] NOT NULL,
[MemId] [char](15) NOT NULL,
[StateId] [char](2) NOT NULL,
[MemberFullName] [varchar](95) NULL,
[MemberFirstName] [varchar](35) NULL,
[MemberLastName] [varchar](60) NULL,
[MemberAddress] [varchar](200) NULL,
[MemberPhone] [varchar](15) NULL,
[MemberDOB] [datetime] NULL,
[MemberAge] [int] NULL,
[MemberCohort] [varchar](10) NULL,
[MemberProduct] [varchar](50) NULL,
[MemberPrdDesc] [varchar](200) NULL,
[MemberActive] [varchar](1) NOT NULL,
[PlanId] [char](15) NULL,
[PlanDescription] [varchar](60) NULL,
[ProgramDesc] [varchar](60) NULL,
[PayToProviderId] [char](15) NOT NULL,
[PayToProviderName] [varchar](95) NULL,
[PCPSiteName] [varchar](95) NULL,
[PCPSiteId] [char](15) NOT NULL,
[PCPProviderId] [char](15) NOT NULL,
[PCPProvideName] [varchar](95) NULL,
[PCPCounty] [varchar](25) NULL,
[PCPSpecialty] [varchar](60) NULL,
[PCMHSite] [varchar](60) NULL,
[QTRPeriod] [varchar](7) NULL,
[QTRPeriodSort] [char](2) NULL,
[ERCnt] [int] NULL,
[CTMRICnt] [int] NULL,
[RadmitCnt] [int] NULL,
[RxVolCnt] [int] NULL,
[RxGenVolCnt] [int] NULL,
[RxConSubVolCnt] [int] NULL,
[RxBrandedVolCnt] [int] NULL,
[RxCost] [money] NULL
) ON ProvUT_MasterData_XXPScheme ([StateId])



Question: Tommorrow if new stateid is coming say for example 'KK' AS stateid. whether dynamically new partition id will be created for this or else it will use any of the partition.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 09:11:21
No new partition id would be created. 'KK' is in between FL and MC so those rows would go to the FL (the left) partition.
Go to Top of Page

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2014-11-20 : 12:49:09
Thanks Gbritton for your reply.

Now my requirment is if wheneve i receive new stateID,i need to create new partition for the new stateID in the stored procedure. so can i do this dynamically?

quote:
Originally posted by gbritton

No new partition id would be created. 'KK' is in between FL and MC so those rows would go to the FL (the left) partition.

Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-20 : 12:52:42
You will have to modify the partition function. To do this dynamically will mean using dynamic sql: build the CREATE PARTITION function (Or ALTER) as a string (nvarchar) and execute it with sp_executesql
Go to Top of Page
   

- Advertisement -