Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Partitioning... Expand / Collapse
Author
Message
Posted Sunday, July 18, 2010 11:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:16 PM
Points: 43, Visits: 94
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
Post #954595
Posted Sunday, July 18, 2010 11:26 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 6:23 PM
Points: 69, Visits: 194
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?
Post #954599
Posted Monday, July 19, 2010 3:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
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.)


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #954674
Posted Monday, July 19, 2010 6:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #954762
Posted Monday, July 19, 2010 7:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:16 PM
Points: 43, Visits: 94
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..
Post #954803
Posted Monday, July 19, 2010 7:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 25, 2013 9:16 PM
Points: 43, Visits: 94
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
Post #954805
Posted Monday, July 19, 2010 8:25 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #954841
Posted Monday, July 19, 2010 8:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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.
Post #954844
Posted Monday, July 19, 2010 11:23 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 6:23 PM
Points: 69, Visits: 194
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.
Post #954985
Posted Monday, July 19, 2010 12:30 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
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!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #955021
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse