How to identify points missing from series

[baltman]

I'd like to identify deleted records from a table. The field I'd like to use is an auto increment. A deleted record would be a hole in the series.

Any tips or triks?

thanks,
Brian

 

[SQLBill]

You can't use an IDENTITY column for this. Let's say I start to insert data...each row gets an IDENTITY value. Before the insert completes something happens and it is rolled back. Those IDENTITY values are still 'used' and the next insert will skip them. Has data been deleted? No, it was never entered, but the 'spot' for them was reserved, discarded, and won't be reused.

You might be better off creating a trigger that will 'fire' on DELETEs and record the deleted value for you.

-SQLBill

Posting advice: FAQ481-4875

 

[baltman]

The information is stored in an audit trail table, but we need to access the information frequently to feed another system and the audit trail table is very large relative to the table we're interested in. I was hoping there was a simple way to select the data with an order by and loop through comparing each value with the one just before it.

 

[gmmastros]

To do this properly, you really should have a numbers table in your database. As long as there are more records in your numbers table than the highest id, you could use this numbers table to get the missing id numbers. The code I show below creates a temporary numbers table. This code isn't very fast, but it should do the trick.

[COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color]([COLOR=#FF00FF]RowId[/color] [COLOR=blue]Int[/color] [COLOR=blue]Identity[/color](1,1), Anything [COLOR=blue]int[/color])

[COLOR=blue]Declare[/color] @Max [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @Min [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @iCount [COLOR=blue]Int[/color]
[COLOR=blue]Declare[/color] @i [COLOR=blue]Int[/color]

[COLOR=blue]Select[/color]  @Max = [COLOR=#FF00FF]Max[/color]([!]Col[/!]),
        @Min = [COLOR=#FF00FF]Min[/color]([!]Col[/!]),
        @i = 1,
        @iCount = 1 + (@Max - @Min) / [COLOR=#FF00FF]Count[/color](*)
[COLOR=blue]From[/color]    [!]Table[/!]

[COLOR=blue]While[/color] @i <= @iCount
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp(Anything)
    [COLOR=blue]Select[/color] NULL
    [COLOR=blue]From[/color]   [!]Table[/!]

    [COLOR=blue]Set[/color] @i = @i + 1
  [COLOR=blue]End[/color]

[COLOR=blue]Select[/color] @i, @Min, @Max

[COLOR=blue]Select[/color] @Min + [COLOR=#FF00FF]RowId[/color] 
[COLOR=blue]From[/color]   @Temp [COLOR=blue]T[/color]
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] [!]Table[/!]
          [COLOR=blue]On[/color] @Min + [COLOR=blue]T[/color].RowId = [!]Table[/!].[!]Col[/!]
[COLOR=blue]Where[/color]  @Min + [COLOR=blue]T[/color].RowId Between @Min And @Max
       And [!]Table[/!].[!]Col[/!] Is NULL

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[baltman]

Thank you. That solution didn't quite do the trick for me. The inserts are just too slow. I decided to go with the AuditTrail table and a Timestamp filter for now.

I'm surprised MS doesn't make this easier. I can run this code in FoxPro and get the results I'm looking for in .05 - .125 seconds including creating the connection.

Thank you

x=SECONDS()
cConnectString="driver={SQL Server};SERVER=xxx,xx;UID=xxx;PWD=xxx;DATABASE=xxx;Trusted_Connection=No"
nHandle=SQLSTRINGCONNECT(cConnectstring)
SQLEXEC(nHandle,"select ID from Package","test")
SELECT MIN(ID) as m1,MAX(id) as m2 FROM test NOWAIT 
nM1=m1
nM2=m2

CREATE TABLE test2 (ID n(5))

FOR i = nM1 TO nM2-1
 INSERT INTO test2 VALUES (i)
ENDFOR

SELECT * FROM test2 WHERE ID NOT in (SELECT ID FROM test) NOWAIT

 

[gmmastros]

If you would create a permanent numbers table in the database like I suggested, this query would be very fast. The slow part here is in creating the numbers table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[baltman]

In this case the database is designed by a vendor and I cannot (contractually) modify the DB schema.

The timestamp where clause makes the AuditTrail query acceptable.

Thank you