CODEWITHSUNDEEP
sql-serverperformancetypesgroup-by
richard
richard

Reputation: 12498

SQL Server Performance: GROUP BY int vs GROUP BY VARCHAR

Is there a performance difference when grouping by different data types? For instance, if I group by INT, will I get better performance than if I group by varchar?

Upvotes: 9

Views: 7534

Answers (3)

anon
anon

Reputation:

Are you deciding on a data type based solely on how the data type performs in a GROUP BY? Is it the same data, you're just deciding how to store 123456, as an INT or a VARCHAR? Have you given consideration to other factors, such as the CPU cost of converting between numeric and string types when it might not otherwise be necessary? The extra memory required to hold the whole table in cache? The row overhead for VARCHAR indicating length? What about storage costs (e.g. 1234567890 takes 4 bytes as an INT, but '1234567890' takes 10 bytes + row overhead as a VARCHAR)? How about compression? How will the index on this column be aligned with the clustered index on the table, which can affect how useful "already grouped" will be?

In other words, I wouldn't consider GROUP BY performance in a bubble.

Upvotes: 3

Guffa
Guffa

Reputation: 700322

Grouping on an int would be slightly faster than grouping on a varchar, but what really makes a difference is if there is an index on the field that the database can use for the grouping.

Upvotes: 2

Simon Hughes
Simon Hughes

Reputation: 3574

I would say GROUP BY INT is faster, as only 4 bytes are checked verses n bytes in a varchar field.

Upvotes: 9

Related Questions