Take the 2-minute tour ×

I have a database that contains department and employee tables, and I need to return the number of employees for each department. I write the following query:

SELECT d.ID, d.Name, COUNT(*) EmployeeCount
  FROM dbo.Departments d
       INNER JOIN dbo.Personnel p 
       ON p.DepartmentID = d.ID
GROUP BY d.ID, d.Name;

The problem is, I'm worried that the GROUP BY operator is going to be slow when working on a VARCHAR column. I'd like to be able to write

SELECT d.ID, ANY(d.Name), COUNT(*)  EmployeeCount
  FROM dbo.Departments d
       INNER JOIN dbo.Personnel p 
       ON p.DepartmentID = d.ID

I could probably use the MIN/MAX aggregate functions, but they would likely make the query even slower. I know that, in MySQL, you can select a column that doesn't appear in the GROUP BY clause, and the DB engine will return any row value at random for that column. Is something like that doable in T-SQL?

share|improve this question
I don't believe Postgres does that. You may have it confused with MySQL. –  rfusca Aug 7 '12 at 15:21
I may very well have -- I don't have much experience with other DBMSes. I'll edit my post, thanks. –  Mihai Aug 7 '12 at 15:23
If you don't care which d.Name value is there, why do you care if the column is there at all? Did you try the MIN/MAX syntax, and did you confirm that it is slower? There is no reason to desire to go outside of supported syntax if you haven't even confirmed there is a performance problem. I would in fact suggest that random would be slower - MIN could at least use the first value in an index, if a supporting index exists. –  Aaron Bertrand Aug 7 '12 at 15:45
You don't need it. MySQL's implementation is dangerous and stupid. See stackoverflow.com/q/6060241/27535 –  gbn Aug 7 '12 at 19:54
It is possible. See Undocumented Query Plans: The ANY Aggregate. In this case though you don't need an aggregate just use the first query. Assuming id is backed up by a unique constraint SQL Server will recognise that name is functionally dependant on id anyway. –  Martin Smith Aug 8 '12 at 6:28
add comment

1 Answer

up vote 8 down vote accepted

I think you are trying to optimize a performance problem that doesn't exist. I don't know why you think a GROUP BY is going to be problematic here. Conceptually there isn't really much difference between what you wrote and the following:

SELECT d.Id, d.Name, s.c
  FROM dbo.Departments AS d
    SELECT ID = DepartmentID, c = COUNT(*)
    FROM dbo.Personnel 
    GROUP BY DepartmentID
  ) AS s
  ON d.ID = s.ID;

So this query doesn't do the GROUP BY on the Name column, but in my testing this is actually less efficient than your version unless you create a non-clustered index on Personnel.DepartmentID (in which case the plans are exactly the same). I suggest optimizing performance when you actually observe a performance problem, not based on hunches. In your case the grouping essentially comes along for free because it's part of the same clustered index scan that has to read all the data anyway. I'm not sure how forcing SQL Server to pick a random name (which is going to be in the same row as the ID you already read anyway) can possibly make anything faster...

share|improve this answer
add comment

Your Answer


