Steve

Indeed, without an ORDER BY clause, you don't know which rows are going to be deleted.  Your code also had no ORDER BY; I wrote mine to be equivalent to...

Are you running the loop within a transaction?  Have you tried substituting your DELETE statement with mine?

John

I don't think you can assign permissions directly on the hidden mssqlsystemresource database.  You'll need to assign a server-level permission to the user - possibly something like VIEW SERVER STATE.

John

Assuming <identityfield> has a primary key or unique constraint on it, I think this will do the same:

DELETE TOP (100)
FROM table;

On the wider question of parallelism, how...

• This reply was modified 5 years, 4 months ago by John Mitchell-245523. Reason: Corrected typo

Right, so are you saying you inserted millions of rows, but now none of them are there?  If that's the case, then everything is working properly - zero rows are...

How do you know that's what happened?  Are there now no rows in your table?  What happens if you run SELECT...

Did you run it in the context of the database the table is in both times?  What happens if you try a DETAILED scan instead of SAMPLED?  Please post table...

What parameters did you supply to dm_db_index_physical_stats?  Please will you post the result set?  Please will you post the DDL for the table and any indexes on it?

John

The rows column in the legacy view sysindexes isn't accurate.  For a more up-to-date, although not documentedly reliable, count, use the rows column in sys.partitions instead.

John

Try partitioning on backup_set_id.

John

Sometimes people are more willing to help if they can see you've made the effort to help yourself.  Why not create another database with the same sort of setup, and...

From the documentation (emphasis mine):

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values...

No, because your WHERE clause only filters out a certain type of registration.  What the question asks is to filter out a certain type of student.  I think you need...

And giving you the correct answer wouldn't help, either.  It wouldn't be right for me to do your homework for you.  If you don't have the sample data, then how...

I think the easiest way to find out is to actually try it.

John