January 14, 2008 at 2:41 am
hi all
i have 2 tables
tblinvoiceheader and tblinvoicedetails, with a relation ship between them as InvoiceSequence column being the PK.
the table has data and no modification could be made for thier structure.
i need to partition both tables passed on the SalesmanNumber.
the header could be partitioned like this easly, but am having a problem with who am going to partition the detail.
this is my tables:
CREATE TABLE [tblInvoiceHeader] (
[InvoiceSequence] [int] IDENTITY (1, 1) NOT NULL ,
[InvoiceNumber] [varchar] (20) COLLATE Arabic_CI_AS NOT NULL ,
[InvoiceBarcode] [varchar] (20) COLLATE Arabic_CI_AS NULL ,
[InvoiceTypeID] [tinyint] NOT NULL ,
[InvoiceDateTime] [smalldatetime] NOT NULL ,
[InvoiceEntryDate] [smalldatetime] NOT NULL ,
[SalesmanNumber] [varchar] (15) COLLATE Arabic_CI_AS NOT NULL ,
[CustomerNumber] [varchar] (20) COLLATE Arabic_CI_AS NOT NULL ,
[TotalAmountBeforeTax] [money] NOT NULL ,
[TotalTaxAmount] [money] NULL ,
[TotalPromotionDiscount] [money] NOT NULL ,
[TotalOtherDiscount] [money] NOT NULL ,
[TotalInvoiceAmount] [money] NULL ,
[Printed] [bit] NOT NULL ,
[InvoiceStatus] [char] (1) COLLATE Arabic_CI_AS NOT NULL ,
[Notes] [nvarchar] (50) COLLATE Arabic_CI_AS NULL ,
[DocRefNumber] [varchar] (20) COLLATE Arabic_CI_AS NULL ,
[SupervisorID] [tinyint] NULL ,
[SalesAreaManagerID] [tinyint] NULL ,
[SalesManagerID] [tinyint] NULL ,
[MasterKeyIsUsed] [bit] NULL ,
[befor_tran_status] [char] (10) COLLATE Arabic_CI_AS NOT NULL CONSTRAINT [DF__tblInvoic__befor__054645E6] DEFAULT ('i'),
[after_tran_status] [varchar] (50) COLLATE Arabic_CI_AS NOT NULL CONSTRAINT [DF__tblInvoic__after__063A6A1F] DEFAULT ('n'),
[insert_date] [datetime] NOT NULL CONSTRAINT [DF__tblInvoic__inser__072E8E58] DEFAULT (getdate()),
[update_date] [datetime] NULL CONSTRAINT [DF__tblInvoic__updat__0822B291] DEFAULT (null),
[dts_row_id] [varchar] (50) COLLATE Arabic_CI_AS NOT NULL CONSTRAINT [DF__tblInvoic__dts_r__0916D6CA] DEFAULT (newid()),
[db_name] [varchar] (40) COLLATE Arabic_CI_AS NOT NULL CONSTRAINT [DF__tblInvoic__db_na__0A0AFB03] DEFAULT (db_name()),
CONSTRAINT [PK__tblInvoiceHeader__4E1475DF] PRIMARY KEY CLUSTERED
(
[InvoiceSequence]
) WITH FILLFACTOR = 20 ON [PRIMARY]
) ON [PRIMARY]
GO
---
CREATE TABLE [tblInvoiceDetails] (
[InvoiceSequence] [int] NOT NULL ,
[ItemSequence] [tinyint] NOT NULL ,
[InvoiceNumber] [varchar] (20) COLLATE Arabic_CI_AS NOT NULL ,
[ItemNumber] [varchar] (20) COLLATE Arabic_CI_AS NOT NULL ,
[ItemSerialNumber] [varchar] (30) COLLATE Arabic_CI_AS NULL ,
[ItemBatchLotNumber] [varchar] (10) COLLATE Arabic_CI_AS NULL ,
[InvoiceItemUnitID] [tinyint] NOT NULL ,
[ItemMainQty] [int] NOT NULL ,
[ItemBonusQty] [smallint] NOT NULL ,
[ItemTotalQty] [int] NOT NULL ,
[InvoiceItemPrice] [smallmoney] NOT NULL ,
[InvoiceItemTaxPer] [real] NOT NULL ,
[InvoiceExtraCharge] [smallmoney] NOT NULL ,
[AmountBeforeDiscount] [money] NOT NULL ,
[PromotionDiscount] [smallmoney] NOT NULL ,
[OtherDiscount] [smallmoney] NOT NULL ,
[AllDiscount] [smallmoney] NOT NULL ,
[AmountAfterDiscountBeforeTax] [money] NOT NULL ,
[AllTaxAmount] [smallmoney] NOT NULL ,
[AmountAfterTax_Final] [money] NOT NULL ,
[ReturnedQtyStatus] [char] (1) COLLATE Arabic_CI_AS NULL ,
[befor_tran_status] [char] (10) COLLATE Arabic_CI_AS NOT NULL CONSTRAINT [DF__tblInvoic__befor__137469AC] DEFAULT ('i'),
[after_tran_status] [varchar] (50) COLLATE Arabic_CI_AS NOT NULL CONSTRAINT [DF__tblInvoic__after__14688DE5] DEFAULT ('n'),
[insert_date] [datetime] NOT NULL CONSTRAINT [DF__tblInvoic__inser__155CB21E] DEFAULT (getdate()),
[update_date] [datetime] NULL CONSTRAINT [DF__tblInvoic__updat__1650D657] DEFAULT (null),
[dts_row_id] [varchar] (50) COLLATE Arabic_CI_AS NOT NULL CONSTRAINT [DF__tblInvoic__dts_r__1744FA90] DEFAULT (newid()),
[db_name] [varchar] (40) COLLATE Arabic_CI_AS NOT NULL CONSTRAINT [DF__tblInvoic__db_na__18391EC9] DEFAULT (db_name()),
CONSTRAINT [PK__tblInvoiceDetail__1C3DEE80] PRIMARY KEY CLUSTERED
(
[InvoiceSequence],
[ItemSequence]
) ON [PRIMARY]
) ON [PRIMARY]
GO
----thanks
..>>..
MobashA
January 14, 2008 at 3:32 am
The column on which you are partitioning the data must be part of the table schema. You could denormalize your table if you must partition the dependent table. I assume you have a lot of (several GB) of data in these tables to make partitioning worth.
Regards,
Andras
January 14, 2008 at 3:41 am
denormalizing would require structure changes on the table, which it couldn't be made.
..>>..
MobashA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply