Partitinong problem - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

Partitinong problem

Author

Topic

hsb_455@yahoo.com
Starting Member

1 Post

Posted - 2008-12-30 : 02:27:33

Hi Guys

I have a very huge existing table, called 'Transaction_Summary', with 82 columns!!! containing more that 100 mil records and because search queries get really slow
and in order to make the quicker I have to partition table.
The partition boundary will be a non-primary key CHAR(4) field called "Channel"

What I did, I read the stuff I was recommended and I started doing it first I created the file groups and then adding the files to database and then created the partition function and then partition schema and then I created an exact copy of the table I want to partition plus all indexes and PK but I still have a problem as soon as I create the PK tha table is not partitioned anymore and I dont know the reason and couldnt find any thing.
This is What I did:

USE PartitionTestBIG
GO

ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_CARD
GO

ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_CBO
GO

ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_CHEQ
GO

ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_CUST
GO

ALTER DATABASE PartitionTestBIG
ADD FILEGROUP FG_TELL
GO

ALTER DATABASE PartitionTestBIG
ADD FILE
(
NAME = PartitionTestBIG_CBO,
FILENAME = 'F:\SQL SERVER 2005 Data files\PredatorDB\PartitionTestBIG_CBO.ndf',
SIZE = 5MB,
FILEGROWTH = 200MB
) TO FILEGROUP FG_CBO;

GO
ALTER DATABASE PartitionTestBIG
ADD FILE
(
NAME = PartitionTestBIG_CHEQ,
FILENAME = 'F:\SQL SERVER 2005 Data files\PredatorDB\PartitionTestBIG_CHEQ.ndf',
SIZE = 5MB,
FILEGROWTH = 200MB
) TO FILEGROUP FG_CHEQ;

GO
ALTER DATABASE PartitionTestBIG
ADD FILE
(
NAME = PartitionTestBIG_CUST,
FILENAME = 'F:\SQL SERVER 2005 Data files\PredatorDB\PartitionTestBIG_CUST.ndf',
SIZE = 5MB,
FILEGROWTH = 200MB
) TO FILEGROUP FG_CUST;

GO
ALTER DATABASE PartitionTestBIG
ADD FILE
(
NAME = PartitionTestBIG_TELL,
FILENAME = 'F:\SQL SERVER 2005 Data files\PredatorDB\PartitionTestBIG_TELL.ndf',
SIZE = 5MB,
FILEGROWTH = 200MB
) TO FILEGROUP FG_TELL;

GO

CREATE PARTITION FUNCTION TranactionSummary_Channel(nchar(4))
AS
RANGE LEFT FOR VALUES ('CARD', 'CBO', 'CHEQ', 'CUST', 'TELL')
GO

CREATE PARTITION SCHEME TranactionSummary_Schema
AS
PARTITION TranactionSummary_Channel
TO ([FG_CARD], [FG_CBO], [FG_CHEQ], [FG_CUST], [FG_TELL], [Data5])
GO

--The whole table hasnt been mentioned here
CREATE TABLE [dbo].[Transaction_Summary_2](
[Transaction_Serial_No] [bigint] NOT NULL,
[Channel] [nchar](4) NOT NULL,
[Card_Organisation] [nvarchar](11) NULL,
[Card_No] [nvarchar](24) NULL,
[Merchant_No] [nvarchar](19) NULL,
.
.
.
[Ratio_Current_Amount_AVG_3M] [decimal](19, 2) NULL,
[Card_Function_Identifier] [nvarchar](6) NULL
) ON TranactionSummary_Schema (Channel)

CREATE NONCLUSTERED INDEX [IX_ASB_Action_Taken] ON [dbo].[Transaction_Summary_2]
(
[Action_Taken] ASC
)
INCLUDE ( [Card_No],
[Transaction_DateTime]) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_ASB_Card_No] ON [dbo].[Transaction_Summary_2]
(
[Card_No] ASC,
[Transaction_DateTime] DESC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_ASB_Confirmed_Fraud] ON [dbo].[Transaction_Summary_2]
(
[Confirmed_Fraud] ASC
)
INCLUDE ( [Transaction_DateTime],
[Card_No]) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Channel] ON [dbo].[Transaction_Summary_2]
(
[Channel] ASC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Customer_No] ON [dbo].[Transaction_Summary_2]
(
[Customer_No] ASC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Fraud_Alert] ON [dbo].[Transaction_Summary_2]
(
[Fraud_Alert] ASC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Process_Status] ON [dbo].[Transaction_Summary_2]
(
[Process_Status] ASC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Terminal_Id] ON [dbo].[Transaction_Summary_2]
(
[Terminal_Id] ASC,
[Transaction_DateTime] DESC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Transaction_Date] ON [dbo].[Transaction_Summary_2]
(
[Transaction_Date] ASC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB1] ON [dbo].[Transaction_Summary_2]
(
[Account_No_Stem] ASC,
[Account_No_Branch] ASC,
[Account_No_Suffix] ASC,
[Account_No_Bank] ASC,
[Transaction_DateTime] ASC
)
INCLUDE ( [Transaction_Amount],
[Transaction_Date]) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB2] ON [dbo].[Transaction_Summary_2]
(
[Card_Organisation] ASC,
[Merchant_Location] ASC,
[Country_Code] ASC,
[Account_No_Stem] ASC,
[Account_No_Branch] ASC,
[Account_No_Bank] ASC
)
INCLUDE ( [Transaction_DateTime],
[Latitude],
[Longitude]) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB3] ON [dbo].[Transaction_Summary_2]
(
[Workflow_Status] ASC,
[Reschedule_DateTime] ASC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB4] ON [dbo].[Transaction_Summary_2]
(
[Fraud_Case_Number] ASC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB5] ON [dbo].[Transaction_Summary_2]
(
[Merchant_No] ASC
) ON [Index1]

CREATE NONCLUSTERED INDEX [IX_Transaction_Summary_ASB6] ON [dbo].[Transaction_Summary_2]
(
[Assigned_Team] ASC,
[Assigned_User] ASC
)ON [Index1]

CREATE NONCLUSTERED INDEX [IX_TransactionDateTime] ON [dbo].[Transaction_Summary_2]
(
[Transaction_DateTime] ASC
) ON [Index1]

I can create all the indexes without any problem but as soon I do this:

--This is the part that causes problem
ALTER TABLE [dbo].[Transaction_Summary_2] ADD CONSTRAINT [PK_Transaction_Summary_2] PRIMARY KEY CLUSTERED
(
[Transaction_Serial_No] ASC
) ON [Data5] --ON TranactionSummary_Schema(Channel)
--to here

the table won't remain partitioned.
Could you please help me and let me know what you think?

Regards,
Hooman

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-31 : 21:50:00

Did you look at Microsoft Whitepaper in Table Partitioning ? It has really good examples and scenerios.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources