Let's say you have a result set with 5000 records but you want to display only 100 of them and in the same time to display the count of ALL records (5000).
Using next script you cand get these within ONE SINGLE STEP but not two as:
SELECT COUNT(*) FROM X
SELECT TOP 100 * FROM X
ONE STEP solution:
Let's say you have a table, ContactProperty where a Contact can have more properties:
So, the next script returns the top 100 UNIQUE contactidIds.
This is what you get:
The value of RowNo from the first record represents the COUNT(*) of the whole result set, in this case I have 54977 rows but this returns only 100 of them.
This is a particular case but you can remove the "GROUP BY".