Hi. With great interest I note your approach, and the approach using id = id-1.

I have this but more: we get data for (11) different types, each type has it's own incrementing by 1 id. The volume of data is around 3mill rows for all 11 types in any given day. I use a table, refNum (1-3000000), right joined with a Select max(Num) from Msgs group by ServiceCode to get a Range per ServiceCode, and using this I resolve a list of MsgType, MsgIds missing by using a left join.

Select distinct convert(varchar(10),getdate(),120) as EffectiveDate, ALLPOSS.ServiceCode, Number as BeginSeqNo, Number as EndSeqNo

from MsgsReceived right join (

Select servicecode, number

from (

Select ServiceCode, max(MsgSeqNo) LIMIT

from msgsReceived

group by ServiceCode ) T right join refNums N

on Limit >= Number

where servicecode is not null ) as ALLPOSS

on MsgsReceived.ServiceCode = ALLPOSS.ServiceCode

and MsgsReceived.MsgSeqNo = ALLPOSS.Number

where (MsgSeqNo is null )

This results in a row for each missing number, by servicecode.

Alternatively, to get the gaps in the data, I run:

Select EffectiveDate, ServiceCode, (Select isnull(max(MsgSeqNo),0) + 1

from MsgsReceived M

where M.EffectiveDate = MsgsReceived.EffectiveDate

and M.ServiceCode = MsgsReceived.ServiceCode

and M.MsgSeqNo < MsgsReceived.MsgSeqNo

) as BeginSeqNo , MsgSeqNo - 1 as EndSeqNo

from MsgsReceived

where MsgSeqNo -1 > (Select isnull(max(MsgSeqNo),0)

from MsgsReceived M

where M.EffectiveDate = MsgsReceived.EffectiveDate

and M.ServiceCode = MsgsReceived.ServiceCode

and M.MsgSeqNo < MsgsReceived.MsgSeqNo

)

The problem is, both of these queries, rows and blocks, kill our server.

The next thought was the queries source table's indexes:

CREATE TABLE [dbo].[MsgsReceived] (

[EffectiveDate] [datetime] NOT NULL ,

[ServiceCode] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[MsgSeqNo] [bigint] NOT NULL ,

[IsRequested] [bit] NOT NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[refNums] (

[Number] [bigint] NOT NULL

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MsgsReceived] WITH NOCHECK ADD

CONSTRAINT [PK_MsgsReceived] PRIMARY KEY CLUSTERED

(

[EffectiveDate],

[ServiceCode],

[MsgSeqNo],

[IsRequested]

) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [IX_Number] ON [dbo].[refNums]([Number]) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MsgsReceived] ADD

CONSTRAINT [DF_MsgsReceived_IsRequested] DEFAULT (0) FOR [IsRequested],

CONSTRAINT [IX_MsgsReceived] UNIQUE NONCLUSTERED

(

[EffectiveDate],

[ServiceCode],

[MsgSeqNo]

) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CREATE INDEX [IX_MsgsReceived_1] ON [dbo].[MsgsReceived]([EffectiveDate]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CREATE INDEX [IX_MsgsReceived_2] ON [dbo].[MsgsReceived]([MsgSeqNo]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CREATE INDEX [IX_MsgsReceived_3] ON [dbo].[MsgsReceived]([ServiceCode]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

CREATE INDEX [IX_MsgsReceived_4] ON [dbo].[MsgsReceived]([IsRequested]) WITH FILLFACTOR = 90 ON [PRIMARY]

GO

ALTER TABLE [dbo].[refNums] ADD

CONSTRAINT [PK_refNums] PRIMARY KEY NONCLUSTERED

(

[Number]

) ON [PRIMARY]

GO

Any suggestions on how to get the missing seqno by servicecode, using minimum IO / CPU?

Any help will be greatly appreciated.