Thursday, March 29, 2012

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 recodsTry 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|||see www.nigelrivett.com
Find gaps in sequence numbers|||see www.nigelrivett.com
Find gaps in sequence numbers|||nigelrivett's query

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 :)

No comments:

Post a Comment