Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2003
    Posts
    1

    Unanswered: getting a deleted ids froma field

    hi i need to get deleted dis froma table say customers id where in we have 1000 records and we have randomly deleted 100 records i want the ids of those randomly deleted recods

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Try http://www.lumigent.com Log explorer
    or if it was numeric column, you can guess IDs by this query
    select
    FreeRange=
    case when (h.ID+1)=(min(l.Id)-1)
    then convert(varchar(20),h.ID+1)
    else isnull(convert(varchar(20),h.ID+1),'?')+'..'+isnul l(convert(varchar(20),min(l.Id)-1),'?')
    end
    ,"Count"=isnull(convert(varchar(20),min(l.Id)-1-(h.ID+1)+1),'?')
    from
    (
    select yt.ID
    from YourTable yt
    where not exists(select 'x' from YourTable ytH where yt.ID=ytH.ID-1)
    ) h
    full join
    (
    select yt.ID
    from YourTable yt
    where not exists(select 'x' from YourTable ytL where yt.ID=ytL.ID+1)
    ) l on h.ID<l.Id
    group by h.ID
    order by h.ID

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    see www.nigelrivett.com
    Find gaps in sequence numbers

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    see www.nigelrivett.com
    Find gaps in sequence numbers

  5. #5
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    nigelrivett's query

    Code:
    
    select convert(varchar(10),imin.ID) + ' - ' +  convert(varchar(10)
    ,
    (
     select min(ID) 
     from 
     (
      select ID 
      from YourTable 
      where not exists 
       (
        select * 
        from YourTable a2 
        where YourTable.ID-1 =  a2.ID
       ) 
       and YourTable.ID <> (select min(ID) from YourTable)) as imax where imax.ID > imin.ID)
      )
     from 
     (
      select ID 
      from YourTable 
      where not exists 
       (
        select * 
        from YourTable a2 
        where YourTable.ID+1 = a2.ID
       ) 
       and YourTable.ID <> (select max(ID) from YourTable)
    ) as imin
    



    But it was a little slow (7s/4000 rows), I added index
    create unique clustered index icx_YourTable on YourTable(ID ASC)
    but it is still about 3s/4000 rows (By the way Index tuning wizard did not recomend any index !)
    My query is about 1s/4000 rows with no index and I expect higher difference for larger dataset.

    You said, you have 1000 rows, so time under 10 s is excelent for you.
    I am speaking to terabyters

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •