SQL Server Performance Forum – Threads Archive

2005 Table Partition Help

I have a table partitioned on a column on which i have nonclustered index.
I have clustered index on another column. In this scenario, how the clustered data is physically stored? Will the clustered data also stored as different partition wrt to partitioned column? Thanks for help

maybe you can have a look on this:
http://www.quest-pipelines.com/newsletter-v4/1103_B.htm

Thanks for that . My scenario is different.My script is like this ****************************************************************************
CREATE PARTITION FUNCTION pFN_PolicyEffectiveDt(datetime)
AS
RANGE RIGHT FOR VALUES (‘20010101’, ‘20020101’, ‘20030101’, ‘20040101’)
GO
CREATE PARTITION SCHEME PScheme_PolicyEffectiveDt
AS
PARTITION pFN_PolicyEffectiveDt
ALL TO ([PRIMARY])
GO CREATE TABLE dbo.TrnCession (
CessionID bigint IDENTITY,
CessionType char(1) NOT NULL,
CompanyCode int NOT NULL,
PolicyNumber varchar(16) NOT NULL,
PolicyEffectiveDate [datetime] NOT NULL,
PRIMARY KEY NONClustered(PolicyEffectiveDate,CessionID ASC),
CessionEffectiveDate smalldatetime NOT NULL,
StateCode char(2) NOT NULL,
)
ON PScheme_PolicyEffectiveDt (PolicyEffectiveDate)
************************************************************************* My Partition column is PolicyEffectiveDate.
Since PolicyEffectiveDate is in nonclustered index, the nonclustered index pages
will be partitioned wrt Years (2000,2001,..) as given in the definition
Now I create a clustered index on different column
*************************************************************************
Create clustered index idx on TrnCession(CompanyCode,PolicyNumber)
************************************************************************* I want to know how my clustered data will be stored?

http://sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk fyi and may suit for yoru requirement. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>