RE: Sequence-numbering groups

SSC Eights!

Points: 988

October 21, 2010 at 10:22 am

LutzM (10/21/2010)How many rows does your table have? And what indexes?
Please post complete DDL for the table in question.
Edit: the actual execution plan would be great, too...

This table currently has almost 414,000 rows. Here is the DDL for the table:

USE [MyDatabase]

/****** Object: Table [dbo].[MyTable] Script Date: 10/21/2010 08:48:43 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[MyTable](

[AcctFundCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctFundId] DEFAULT (''),

[ACHTrans] [bit] NOT NULL CONSTRAINT [DF_MyTable_ACHTrans] DEFAULT (0),

[AddrCity] [varchar](22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrCity] DEFAULT (''),

[AddrForeignStateCode] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrCountry] DEFAULT (''),

[AddrName] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorName] DEFAULT (''),

[AddrStateCode] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorState] DEFAULT (''),

[AddrStreet] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorStreet] DEFAULT (''),

[AddrZip] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorZip] DEFAULT (''),

[AmtCheck] [money] NOT NULL CONSTRAINT [DF_MyTable_AmtTotal] DEFAULT (0),

[BankAcctId] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BankAcctId] DEFAULT (''),

[BankCheckId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_CheckId] DEFAULT (''),

[BankCheckSort] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AddrSortValue] DEFAULT (''),

[DateCancelled] [datetime] NULL,

[DateCheck] [datetime] NULL,

[DateTimeCreated] [datetime] NOT NULL CONSTRAINT [DF_MyTable_DateTimeCreated] DEFAULT (getdate()),

[DateTimeEdited] [datetime] NULL,

[DateTimePrinted] [datetime] NULL,

[EmpId] [int] NOT NULL CONSTRAINT [DF_MyTable_PersonId] DEFAULT (0),

[OrgId] [smallint] NOT NULL CONSTRAINT [DF_MyTable_OrgId] DEFAULT (0),

[PayeeId] [char](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_StudentId] DEFAULT (''),

[RegisterId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_RegisterId] DEFAULT (''),

[RegisterIdCancel] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_RegisterIdCancel] DEFAULT (''),

[Status] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_ReqStatusCode] DEFAULT (''),

[UserIdCreated] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdCreated] DEFAULT (''),

[UserIdEdited] [char](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdEdited] DEFAULT (''),

[UserIdPrinted] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_UserIdPrinted] DEFAULT (''),

[VendorAddrId] [tinyint] NOT NULL CONSTRAINT [DF_MyTable_VendorAddrId] DEFAULT (0),

[VendorId] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_VendorId_1] DEFAULT (''),

[VendorPayrollCheckType] [tinyint] NOT NULL CONSTRAINT [DF_MyTable_VendorPayrollCheckType] DEFAULT (0),

[PrintedCheckNum] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[BatchId] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BatchId] DEFAULT (''),

[DateCleared] [datetime] NULL,

[AcctResourceCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctResourceCode] DEFAULT (''),

[AcctProjectYearCode] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_AcctProjectYearCode] DEFAULT (''),

[BatchIdCancel] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MyTable_BatchIdCancel] DEFAULT (''),

CONSTRAINT [PK_MyTable_CheckId] PRIMARY KEY NONCLUSTERED

(

[BankCheckId] ASC,

[OrgId] ASC,

[BankAcctId] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [MyDatabaseData4]

) ON [MyDatabaseData4]

SET ANSI_PADDING OFF

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Fund Id when only single funded checks are allowed' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctFundCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'True if this was part of an ACH Bank Transaction' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'ACHTrans'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee City Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrCity'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Country Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrForeignStateCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Name' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrName'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee State Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrStateCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Street Address' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrStreet'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Zip Code' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AddrZip'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total Check Amount' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AmtCheck'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Bank Account Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankAcctId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankCheckId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Sort value used when printing checks' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BankCheckSort'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date this check was cancelled' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCancelled'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Date' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCheck'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation Date and Time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimeCreated'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last changed Date and Time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimeEdited'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Print date and time' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateTimePrinted'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Employee Id value when this check is associated with an Employee' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'EmpId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Organization''s Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'OrgId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Payee Id value when this check is associated with a one time vendor' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'PayeeId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Register this appeared on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'RegisterId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Cancel Register Id this check appeared on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'RegisterIdCancel'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Check Status Code value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'Status'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Creation User Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdCreated'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Last changed by User Id' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdEdited'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'User Id of the user who printed the check' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'UserIdPrinted'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Address Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorAddrId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Id value' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Vendor Payroll Check Type (1 - Deduction, 2 - Contribution, 3 - Both Deduction & Contribution, 4 - ACH Check to cover direct deposits)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'VendorPayrollCheckType'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a check # that some foreign system printed for our check record' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'PrintedCheckNum'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Batch Id value associated with the printing of checks' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BatchId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Datetime the check cleared the bank' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'DateCleared'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Resource Code that paid the most for this check' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctResourceCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Account Project Year component value that paid the most in combination with the AcctFundCode, AcctResourceCode' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'AcctProjectYearCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'BatchId that this check was cancelled on' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'MyTable', @level2type=N'COLUMN', @level2name=N'BatchIdCancel'

Here are the indexes:

USE [MyDatabase]

CREATE CLUSTERED INDEX [IX_MyTable_BankAcctId] ON [dbo].[MyTable]

(

[BankAcctId] ASC,

[OrgId] ASC,

[DateCheck] DESC,

[BankCheckId] DESC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_BatchId] ON [dbo].[MyTable]

(

[BatchId] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_BatchIdCancel] ON [dbo].[MyTable]

(

[BatchIdCancel] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_CheckNum] ON [dbo].[MyTable]

(

[PrintedCheckNum] ASC,

[DateTimeCreated] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_Status] ON [dbo].[MyTable]

(

[BankAcctId] ASC,

[Status] ASC,

[OrgId] ASC,

[DateCancelled] ASC,

[DateCheck] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

CREATE NONCLUSTERED INDEX [IX_MyTable_VendorId] ON [dbo].[MyTable]

(

[OrgId] ASC,

[VendorId] ASC

)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable_CheckId] PRIMARY KEY NONCLUSTERED

(

[BankCheckId] ASC,

[OrgId] ASC,

[BankAcctId] ASC

)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [MyDatabaseData4]

Here is the current execution plan:

DECLARE @tmpChecks TABLE

(

OrgCheck char(15),

OrgId smallint,

CheckNum int,

Status char(1),

AmtCheck money,

CheckDate char(10),

RegisterId char(12)

)

INSERT INTO @tmpChecks

SELECT CAST(OrgId as char(3))+PrintedCheckNum as OrgCheck,

OrgId, PrintedCheckNum as CheckNum, Status,

AmtCheck = CASE WHEN Status = 'V' THEN 0 ELSE AmtCheck END,

CONVERT(char(10), DateCheck,101) as CheckDate, RegisterId

from MyTable WITH (NOLOCK)

WHERE

ISNUMERIC(PrintedCheckNum) = 1

order by 1

;WITH cte1 AS -- row number per group

(

SELECT *,

ROW_NUMBER() OVER(ORDER BY OrgCheck) AS CkRow,

ROW_NUMBER() OVER(PARTITION BY Status ORDER BY OrgCheck ) AS StatRow,

ROW_NUMBER() OVER(ORDER BY OrgCheck)-

ROW_NUMBER() OVER(PARTITION BY Status ORDER BY OrgCheck ) AS DiffRow

FROM @TmpChecks

),cte2 AS -- row number per group range, ordered by OrgCheck

(

SELECT MIN(CkRow) as MinRow,MAX(CkRow) as MaxRow, Status, DiffRow,

ROW_NUMBER() OVER(ORDER BY MIN(CkRow)) AS CheckGroup

FROM cte1

GROUP BY Status, DiffRow

)

SELECT cte1.OrgCheck, cte1.OrgId, cte1.CheckNum, cte1.Status, cte1.AmtCheck,

cte1.CheckDate, cte1.RegisterId, cte2.CheckGroup

FROM cte1

INNER JOIN cte2 ON cte1.CkRow>=cte2.MinRow AND cte1.CkRow<=cte2.MaxRow

ORDER BY cte1.OrgCheck

The general idea is that the accounting application that uses this table currently allows users to run reports that were written using Crystal Reports. I have written one custom report for internal control of the checks which lists series of open checks and series of void checks. The report relies on a stored procedure which uses values provided by the user to select the checks for the report. I have written this stored procedure which is similar to the execution plan shown above but which also includes fairly complex WHERE clauses to handle the user-provided values. When I connect the stored procedure to the Crystal Report, I need it to run through all rows in MyTable once so that when it is run later by the users, none of their user-provided values will have been excluded by the initial connection of the stored procedure to the Crystal Report.

I don't mean for this to turn into a large task for anyone on the forum. Thank you again for any suggestions.

gmrose