How many rows have been deleted? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How many rows have been deleted?

Author

Topic

rmesa
Starting Member

4 Posts

Posted - 2003-07-03 : 15:31:09

Please advise,

I need a SQL query that will tell me how many rows have been deleted from the table. What assumptions do I need to make, if any, about the data for the query to be valid?

The SQL Server database contains a table called FileUploads with the following columns:

UploadId, FileName, UploadTime

UploadId is an identity column and is the primary key for the table. Each record that is inserted into the table is automatically assigned an UploadId that is one greater than the UploadId of the previous record.

TIA,
rmesa

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-03 : 15:45:46

>> UploadId is an identity column
>> Each record that is inserted into the table is automatically assigned an UploadId that is one greater than the UploadId of the previous record

How do you ensure that?
Use identity insert to insert the next value?
Use checkident to set the seed before the insert?

I'm trying to point out that an identity doesn't guarantee that values will be sequential - just that the next value will be based on the current seed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-03 : 16:31:55

select max(UploadId)-count(UploadId) as DeletedRowsNumber
from FileUploads

This accurate enough ;)

- Vit

rmesa
Starting Member

4 Posts

Posted - 2003-07-03 : 17:08:28

quote:

select max(UploadId)-count(UploadId) as DeletedRowsNumber
from FileUploads

This accurate enough ;)

- Vit

Thanks, I will test this out

(I'm sure it will work).

Question would it be possible to take this effort a step further?

Building a query that will return the UploadIds of all the missing records. For the purpose of this question you may assume that no two records with consecutive IDs have been deleted (e.g. if record with UploadId = N is deleted then you can assume that the record with UploadID = N+1 will be present).

TIA,
rmesa

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-03 : 18:32:00

select z.UploadId - 1 as Gaps from FileUploads z
where not exists
(select 0 from FileUploads zz where zz.UploadId=z.UploadId - 1)

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-03 : 18:55:19

Better:

select z.UploadId - 1 as Gaps from FileUploads z
where z.UploadId > 1 and not exists
(select 0 from FileUploads zz where zz.UploadId=z.UploadId - 1)

- Vit

rmesa
Starting Member

4 Posts

Posted - 2003-07-03 : 19:29:27

quote:

Better:

select z.UploadId - 1 as Gaps from FileUploads z
where z.UploadId > 1 and not exists
(select 0 from FileUploads zz where zz.UploadId=z.UploadId - 1)

- Vit

Vit,

Thanks again, your SQL knowledge is amazing. However I think I need to study sub-queries more to turly understand your code better.

Thanks,
RMESA

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-03 : 19:43:57

quote:
Vit,

Thanks again, your SQL knowledge is amazing.

LOL!!! Hehehehe... You see what I mean... ;)
Thank you, TIA!

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-03 : 20:58:10

Just in case...
-----------
n
-----------
3
5
9

select
(select isnull(max(n)+1,1) from t t2 where t2.n<t.n) as GAP1, t.n-1 as GAP2
from t where t.n>1 and not exists(select 0 from t t1 where t1.n=t.n-1)


GAP1  GAP2
----- ----- 
1     2
4     4
6     8

- Vit

Edited by - Stoad on 07/04/2003 18:42:22

rmesa
Starting Member

4 Posts

Posted - 2003-07-04 : 01:50:43

quote:

Just in case...

- Vit

Now you did it ...just when I was starting to understand your code

Thanks again,
RMESA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources