partition information - SQL Server Forums

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.

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources