Как получить максимальное количество строк таблицы в SQL Server

Вы можете использовать агрегацию:

select top (1) department, count(*)
from t
group by department
order by count(*) desc;

Если у вас могут быть дубликаты — и вы хотите их все — используйте top (1) with ties:

select top (1) with ties department, count(*)
from t
group by department
order by count(*) desc;