July 18, 2010 at 11:16 pm
hello to all
i would like to ask help to all of you who knows and knows what the cause of still slowing down the query for the particular partitioned table.. ok here's the scenario i have a script for creating a daily basis table for daily transaction, now i created a partition function pointed to one column which has an identity seed/Increment i used left ranges which each partition should contain 100000 records for each filegroups, and created a partition scheme pointed to my 3 filegroups so i attached the partition scheme on the script for creating tables..here's the script kindly check if i point out on the wrong direction on putting the scheme..
I tried to run this script its successs and i try also to insert and check if the the filegroups contains records define on the ranges and i found it has record the only problem is when we try to query its run about 2 minutes and 41 seconds the records inserted is 600,000, and we try to query the unpartitioned table its only got 1 minutes and 41 seconds..so what was the cause of this partition table please help i'm a new to this method..thanks in advance
please email me through this emial add Jolan.mahinay@myclicktech.com
ALTER PROCEDURE [dbo].[spu_tblDTLCAR_CreateCopy]
@fcTransDate CHAR(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
-- Param input to @fcTransDate = 'mm/dd/yyyy' as string
SET NOCOUNT ON;
DECLARE @fcTableName CHAR(50)
DECLARE @fcTransDateTemp CHAR(8)
SET @fcTransDateTemp=REPLACE(@fcTransDate,'/','')
SET @fcTransDateTemp=LTRIM(RTRIM(@fcTransDateTemp))
SET @fcTableName ='tblDTLCAR_' + @fcTransDateTemp
IF NOT EXISTS(Select id From sysobjects
Where name = @fcTableName AND xtype = 'U')
BEGIN
-- CREATE THE TABLE
DECLARE @strSQL NVARCHAR(MAX)
DECLARE @strSQL2 NVARCHAR(MAX)
DECLARE @strSQL4 NVARCHAR(MAX)
DECLARE @strSQL5 NVARCHAR(MAX)
DECLARE @strSQL6 NVARCHAR(MAX)
SET @strSQL ='CREATE TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '](' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxDTLCARKey] [bigint] IDENTITY(1,1) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcMsgNumber] [decimal](19,0) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcPAN] [char](19) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcPCode] [decimal](19,0) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxMemberKeyISS] [char](6) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcMnemonicISS] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxNetworkKeyISS] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcNetworkDescISS] [nchar](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxMemCategoryKeyISS] [char](3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcMemCategoryNameISS] [nchar](15) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fbStatusISS] [bit] NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxMemberKeyACR] [char](6) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcMnemonicACR] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxNetworkKeyACR] [char](6) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcNetworkDescACR] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxMemCategoryKeyACR] [char](3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcMemCategoryNameACR] [nchar](15) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fbStatusACR] [bit] NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxMemberKeyTRF] [char](6) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcMnemonicTRF] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxNetworkKeyTRF] [char](6) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcNetworkDescTRF] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxMemCategoryKeyTRF] [char](3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcMemCategoryNameTRF] [nchar](15) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fbStatusTRF] [bit] NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxTerminalKey] [datetime2](7) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTerminalNumber] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnCardNumber] [char](20) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdTransDate] [date] NOT NULL,'+ CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdTransTime] [time](7) NOT NULL,'+ CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxTransTypeKey] [nchar](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcTransTypeChar] [nchar](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnAccountNumFrom] [char](50) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnAccountNumTo] [char](50) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransAmount] [decimal](19,3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnAmountReverse] [decimal](19,3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnAmountDispense] [decimal](19,3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnAmountRecon] [decimal](19,3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcAmountUnit] [char](6) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxTransFeeKey] [datetime2](7) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeISS] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeACQ] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeTRF] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeML] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeCISS] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeCACQ] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeCTRF] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeCML] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeOISS] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeOACQ] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeOTRF] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTransFeeOML] [decimal](10, 3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxTransStatusKey] [char](3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxDTLRemarksKey] [nchar](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxChannelKey] [nchar](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcChannelDesc] [nvarchar](50) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxTAGStatusKey] [char](3) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcAccountSCode] [nchar](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcAttachmentPath] [nchar](200) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcAttachmentPath_UNR] [nchar](200) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTraceNumACQ] [decimal](19,0) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTraceNumISS] [decimal](19,0) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnTraceNumTRF] [decimal](19,0) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxISTResponseKey_Res] [char](2) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxISTResponseKey_Rev] [char](2) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnDisp_Amt] [decimal](25,4) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnSHCError] [decimal](19,0) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fnPOS_Condit] [decimal](19,0) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcAgentCodeACQ] [char](11) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcAgentCodeISS] [char](11) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcAgentCodeTRF] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcIssuer_Dat] [nchar](128) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcAcquirer_Dat] [char](128) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fcMerchantName] [char](10) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdSettlementDate] [datetime] NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdDateTimeCreated] [smalldatetime] NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdDateTimeModified] [smalldatetime] NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxUserKeyCreatedBy] [char](6) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxUserKeyModifiedBy] [char](6) NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdDateTimeTag] [datetime] NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxUserKeyTagBy] [char](6) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdDateTimeCommit] [datetime] NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxUserKeyCommitedby] [char](6) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdDateTimeCommit_COM_Rec] [datetime] NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxUserKeyCommitedby_COM_Rec] [char](6) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdDateTimeCommit_UNR] [datetime] NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxUserKeyCommitedby_UNR] [char](6) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fdDateTimePosted] [datetime] NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxUserKeyPosted] [char](6) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxResponse_Remarks] [char](3) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxResponse_Remarks_UNR] [char](200) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxResponse_Remarks_ML] [char](200) NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fbExceptionInclude] [bit] NOT NULL,' + CHAR(13)
SET @strSQL = LTRIM(RTRIM(@strSQL)) + '[fxDTLExceptKey] [datetime2](7) NULL,' + CHAR(13)
SET @strSQL4=''
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + '[fbTag] [bit] NOT NULL,' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + '[fbCommit] [bit] NOT NULL,' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + '[fbCommit_UNR] [bit] NOT NULL,' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + '[fcReason] [char](500) NULL,' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + '[fnGoodTransact] [smallint] NULL,' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + '[fbPIS] [bit] NULL,' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + ' CONSTRAINT [PK_tblDTLCAR_' +LTRIM(RTRIM(@fcTableName)) +'] PRIMARY KEY CLUSTERED' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + ' (' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + '[fxDTLCARKey] ASC' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + ' )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + ')ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--SET @strSQL4= LTRIM(RTRIM(@strSQL4)) + ')ON [PRIMARY]' + CHAR(13)
--SET @strSQL = LTRIM(RTRIM(@strSQL)) + 'GO' + CHAR(13)
SET @strSQL2 =''
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'SET ANSI_PADDING OFF' + CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcMsgNumber] DEFAULT ((0)) FOR [fcMsgNumber]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcPAN] DEFAULT ('''') FOR [fcPAN]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcPCode] DEFAULT ((0)) FOR [fcPCode]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxMemberKeyISS] DEFAULT ('''') FOR [fxMemberKeyISS]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcMnemonicISS] DEFAULT ('''') FOR [fcMnemonicISS]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxNetworkKeyISS] DEFAULT ('''') FOR [fxNetworkKeyISS]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcNetworkDescISS] DEFAULT ('''') FOR [fcNetworkDescISS]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxMemCategoryKeyISS] DEFAULT ('''') FOR [fxMemCategoryKeyISS]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcMemCategoryNameISS] DEFAULT ('''') FOR [fcMemCategoryNameISS]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fbStatusISS] DEFAULT ((0)) FOR [fbStatusISS]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxMemberKeyACR] DEFAULT ('''') FOR [fxMemberKeyACR]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcMnemonicACR] DEFAULT ('''') FOR [fcMnemonicACR]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxNetworkKeyACR] DEFAULT ('''') FOR [fxNetworkKeyACR]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcNetworkDescACR] DEFAULT ('''') FOR [fcNetworkDescACR]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxMemCategoryKeyACR] DEFAULT ('''') FOR [fxMemCategoryKeyACR]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcMemCategoryNameACR] DEFAULT ('''') FOR [fcMemCategoryNameACR]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fbStatusACR] DEFAULT ((0)) FOR [fbStatusACR]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxMemberKeyTRF] DEFAULT ('''') FOR [fxMemberKeyTRF]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcMnemonicTRF] DEFAULT ('''') FOR [fcMnemonicTRF]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxNetworkKeyTRF] DEFAULT ('''') FOR [fxNetworkKeyTRF]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcNetworkDescTRF] DEFAULT ('''') FOR [fcNetworkDescTRF]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxMemCategoryKeyTRF] DEFAULT ('''') FOR [fxMemCategoryKeyTRF]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcMemCategoryNameTRF] DEFAULT ('''') FOR [fcMemCategoryNameTRF]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fbStatusTRF] DEFAULT ((0)) FOR [fbStatusTRF]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTerminalNumber] DEFAULT ('''') FOR [fnTerminalNumber]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxTransTypeKey] DEFAULT ('''') FOR [fxTransTypeKey]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcTransTypeChar] DEFAULT ('''') FOR [fcTransTypeChar]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnAccountNumFrom] DEFAULT ('''') FOR [fnAccountNumFrom]'+ CHAR(13)
SET @strSQL2 = LTRIM(RTRIM(@strSQL2)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnAccountNumTo] DEFAULT ('''') FOR [fnAccountNumTo]'+ CHAR(13)
SET @strSQL5=''
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransAmount] DEFAULT ((0)) FOR [fnTransAmount]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnAmountReverse] DEFAULT ((0)) FOR [fnAmountReverse]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnAmountDispense] DEFAULT ((0)) FOR [fnAmountDispense]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnAmountRecon] DEFAULT ((0)) FOR [fnAmountRecon]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcAmountUnit] DEFAULT ('''') FOR [fcAmountUnit]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeISS] DEFAULT ((0)) FOR [fnTransFeeISS]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeACQ] DEFAULT ((0)) FOR [fnTransFeeACQ]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeTRF] DEFAULT ((0)) FOR [fnTransFeeTRF]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeML] DEFAULT ((0)) FOR [fnTransFeeML]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeCISS] DEFAULT ((0)) FOR [fnTransFeeCISS]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeCACQ] DEFAULT ((0)) FOR [fnTransFeeCACQ]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeCTRF] DEFAULT ((0)) FOR [fnTransFeeCTRF]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeCML] DEFAULT ((0)) FOR [fnTransFeeCML]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeOISS] DEFAULT ((0)) FOR [fnTransFeeOISS]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeOACQ] DEFAULT ((0)) FOR [fnTransFeeOACQ]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeOTRF] DEFAULT ((0)) FOR [fnTransFeeOTRF]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTransFeeOML] DEFAULT ((0)) FOR [fnTransFeeOML]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxTransStatusKey] DEFAULT ('''') FOR [fxTransStatusKey]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxDTLRemarksKey] DEFAULT ('''') FOR [fxDTLRemarksKey]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxChannelKey] DEFAULT ('''') FOR [fxChannelKey]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcChannelDesc] DEFAULT ('''') FOR [fcChannelDesc]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxTAGStatusKey] DEFAULT ('''') FOR [fxTAGStatusKey]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcAccountSCode] DEFAULT ('''') FOR [fcAccountSCode]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcAttachmentPath] DEFAULT ('''') FOR [fcAttachmentPath]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcAttachmentPath_UNR] DEFAULT ('''') FOR [fcAttachmentPath_UNR]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTraceNumACQ] DEFAULT ((0)) FOR [fnTraceNumACQ]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTraceNumISS] DEFAULT ((0)) FOR [fnTraceNumISS]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnTraceNumTRF] DEFAULT ((0)) FOR [fnTraceNumTRF]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxISTResponseKey_Res] DEFAULT ('''') FOR [fxISTResponseKey_Res]'+ CHAR(13)
SET @strSQL5 = LTRIM(RTRIM(@strSQL5)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxISTResponseKey_Rev] DEFAULT ('''') FOR [fxISTResponseKey_Rev]'+ CHAR(13)
---
SET @strSQL6=''
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnDisp_Amt] DEFAULT ((0)) FOR [fnDisp_Amt]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnSHCError] DEFAULT ((0)) FOR [fnSHCError]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnPOS_Condit] DEFAULT ((0)) FOR [fnPOS_Condit]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcAgentCodeACQ] DEFAULT ('''') FOR [fcAgentCodeACQ]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcAgentCodeISS] DEFAULT ('''') FOR [fcAgentCodeISS]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcAgentCodeTRF] DEFAULT ('''') FOR [fcAgentCodeTRF]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcIssuer_Dat] DEFAULT ('''') FOR [fcIssuer_Dat]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcAcquirer_Dat] DEFAULT ('''') FOR [fcAcquirer_Dat]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fcMerchant] DEFAULT ('''') FOR [fcMerchantName]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fdDateTimeCreated] DEFAULT (getdate()) FOR [fdDateTimeCreated]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fdDateTimeModified] DEFAULT (getdate()) FOR [fdDateTimeModified]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxUserKeyCreatedBy] DEFAULT ('''') FOR [fxUserKeyCreatedBy]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fxUserKeyModifiedBy] DEFAULT ('''') FOR [fxUserKeyModifiedBy]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fbExceptionInclude] DEFAULT ((0)) FOR [fbExceptionInclude]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fbTag] DEFAULT ((0)) FOR [fbTag]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fbCommit] DEFAULT ((0)) FOR [fbCommit]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fbCommit_UNR] DEFAULT ((0)) FOR [fbCommit_UNR]'+ CHAR(13)
SET @strSQL6 = LTRIM(RTRIM(@strSQL6)) + 'ALTER TABLE [dbo].[' + LTRIM(RTRIM(@fcTableName)) + '] ADD CONSTRAINT [DF_' + LTRIM(RTRIM(@fcTableName)) + '_fnGoodTransact] DEFAULT ((0)) FOR [fnGoodTransact]'+ CHAR(13)
--PRINT @strSQL
--PRINT @strSQL4
--PRINT @strSQL2
--PRINT @strSQL5
--PRINT @strSQL6
EXEC (@strSQL + ' ' + @strSQL4)
EXEC (@strSQL2)
EXEC (@strSQL5)
EXEC (@strSQL6)
DECLARE @strSQL3 NVARCHAR(MAX)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_fcPan] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fcPAN] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
--SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_memberACR] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyACR] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fcMnemonicACR] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_memberISS] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyISS] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fcMnemonicISS] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_memberTRF] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyTRF] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fcMnemonicTRF] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_memberKey] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyACR] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyISS] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyTRF] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_MsgNum] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fcMsgNumber] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_PCODE] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fcPCode] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13) -- THIS IS MY SCHEME..
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_query1] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxTransTypeKey] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxChannelKey] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxNetworkKeyACR] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxNetworkKeyISS] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxNetworkKeyTRF] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemCategoryKeyACR] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemCategoryKeyISS] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemCategoryKeyTRF] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fcMsgNumber] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxISTResponseKey_Res] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxISTResponseKey_Rev] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_TerminalNum] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fnTerminalNumber] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_traceNum] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fnTraceNumISS] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fnTraceNumACQ] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fnTraceNumTRF] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
SET @strSQL3=''
SET @strSQL3= 'CREATE NONCLUSTERED INDEX [IX_' + LTRIM(RTRIM(@fcTableName)) + '_transDate] ON [dbo].[' + LTRIM(RTRIM(@fcTableName)) + ']' + CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '(' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fdTransDate] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyISS] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyACR] ASC,' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + '[fxMemberKeyTRF] ASC' +CHAR(13)
SET @strSQL3= LTRIM(RTRIM(@strSQL3)) + ')WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON DTLCAR_SCHEME(fxDTLCARKey)' + CHAR(13)
--PRINT @strSQL3
EXEC(@strSQL3)
END
ELSE
BEGIN
-- DELETE DATA
---EXECUTE ('TRUNCATE TABLE ' + @fcTableName)
PRINT 'TABLE ALREADY EXISTS'
END
END
July 18, 2010 at 11:26 pm
What's the query look like that you are running? A table with a few hundred thousand records shouldn't take long to run, and shouldn't need to be partitioned. I've got tables with hundreds of millions of records with no partitioning running with no problem.
Can you post the execution plan for your query which isn't running correctly?
July 19, 2010 at 3:41 am
mrdenny (7/18/2010)
What's the query look like that you are running? A table with a few hundred thousand records shouldn't take long to run, and shouldn't need to be partitioned. I've got tables with hundreds of millions of records with no partitioning running with no problem.Can you post the execution plan for your query which isn't running correctly?
Hi sir!
Can you explain little bit how you are doing this "I've got tables with hundreds of millions of records with no partitioning running with no problem" - I know that here speaks the SQL Server MVP and one of the SQL Masters! I would like to know in which version of SQL Server and some few infos about (Hardware/Software config.)
July 19, 2010 at 6:08 am
In general I go for partitioning only for tables expected to hold in excess of 100M rows, partitions 100K rows in size really does not make a lot of sense.
For some reason a lot of people believes partitioning will improve performance in some magical way - let me break some news here, in general(*) table partitioning does not improves performance but helps - if well designed - during administrative tasks like archiving and purging.
(*) an exception meaning, a scenario where table partitioning improves performance would be to serve queries that have to return several millions of granular data level rows. In this particular case a well designed partitioning strategy will make cheaper to full scan a particular partition rather than doing index access. Please note that this only works when partition key is the main filtering condition on query's predicate.
Going back to the original question. Is there any indexing strategy in place? Did you trace the query to see what it is doing?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 19, 2010 at 7:24 am
thanks for the reply ...our table holds about 1M per daily transaction per table, now our client experience a slow down during filtering on records on the web application(asp.net) they experience about 3 hours for only one filter transaction, so they heard about partitioning in sqlserver 2008 as our database so they give this task to search and test this method, so i read it and found out its good coz it help a lot for performance, but then when i test the unpartitioned table contains about 600 000 rows and a query given to me to test it, its takes about 2 minutes and 50+ seconds, so i tried now the partitioned table to expect that it will goes lower that the other one, now the query time goes to higher it takes about 3 minutes and 42 seconds for the partitioned table..i was confuse about this thing.. please advise or if you have some method to do a good retrieval performance..thanks in advance..
July 19, 2010 at 7:27 am
sir mrdenny how many minutes did your query goes for a hundreds of millions records? what was your method or way to accomplish a good performance please help
July 19, 2010 at 8:25 am
jolan.mahinay (7/19/2010)
sir mrdenny how many minutes did your query goes for a hundreds of millions records? what was your method or way to accomplish a good performance please help
One thing is to issue a query against a 100M rows table and other thing is to process 100M rows.
In our shop we have complex queries referencing billion rows table returning in a couple of seconds - indexing strategy is the key - provided business rules are not asking to actually read 100M rows.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 19, 2010 at 8:30 am
jolan.mahinay (7/19/2010)unpartitioned table contains about 600 000 rows and a query given to me to test it, its takes about 2 minutes and 50+ seconds, so i tried now the partitioned table to expect that it will goes lower that the other one, now the query time goes to higher it takes about 3 minutes and 42 seconds for the partitioned table.
This is not unusual - partitioning only added overhead in this particular case.
Please trace both queries and check where space is being wasted.
How many rows out of 600K have to be actually accessed to serve the query?
Is there any indexing strategy serving this particular query?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 19, 2010 at 11:23 am
It's a single table in the default file group. The file group has two files, each of which is on it's own LUN. The LUNs are each created on their own 5 disk RAID 5 array on the EMC SAN. The server is a 16 core (4x4) Dell server with 64 Gigs of RAM.
We rarely hit the disks for reads, as we have a 99.98% buffer cache hit ratio.
Today the table has 162 Million records in it. The data in the table is 213 Gigs, and the indexes are 112 Gigs. Query response time on the table is typically 1-2 seconds. Query run time can go as high as 4-5 seconds, or can be as little as .2 seconds. During any single minute we are writing tens of thousands of rows into the table via the .NET bulk insert provider.
All read access to the table is done via stored procedures which use dynamic SQL to create the smallest possible query to hit the table. While this increases CPU load do to additional compiles, the benefit of reduced execution times do to simpler queries out ways the costs.
If you'd like to know about our setup I'd be happy to tell you.
July 19, 2010 at 12:30 pm
Thank you sir! As always explanations from the SQL Masters are welcome and valuable, I guess, better if you blog something on this case! However why you are not syndicate your blog here in SSC!
July 19, 2010 at 12:38 pm
My blog is owned by Tech Target as it is hosted on their site. I can ask them about syndicating here as well, but as SSC is a competing site to Tech Target's site, they probably won't go for it. It was hard enough to get them to syndicate on SQLServerPedia and technet.
July 19, 2010 at 2:06 pm
I just propose for syndication if it' possible, even though I'm following your posts from your blog! Good job Denny!
July 19, 2010 at 10:35 pm
hello mrdenny thank you so much for the info. i love to know more on how you set up your database tables and indexing, if you dont mind sir, what are columns to be index? and hows powerful is indexing? our set up in our server is 400 gigz hardisk, 12 gigz of ram and quad cpu (4x4). is this enough? i tried again yesterday and simple select for single table holding a 900,000k rows and goes to 3 minutes is there a problem on the table or indexing? please help.thanks in advance.
July 19, 2010 at 10:44 pm
thank your mrdenny for your valuable info. our set up in our server is 400 gigz harddisk,12 gigz of ram and quad cpu(4x4), sir i love to know more on how you set up your database table and indexing, is indexing is needed? how is it powerful in terms of retrieval plus aggragation inside the query? i tried again yesterday a very simple query for a 900,000k rows retrieval without aggragation on it and ends up to 3 minutes, is there a problem on the table or indexing? please help..thanks in advance
July 19, 2010 at 11:00 pm
Sounds like there is a problem with the indexes on the table. Can you post the execution plan?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply