Indexing for a table with many large subsets
Does it make sense to put an index on that column and if so which type of index should be used?

We can't answer that for you. It depends on if query performance is good enough without the index. If end user response time, transactions per second, overall server CPU, or whatever metric is the most important to you is fine without the index then you don't need it right now. You may need it in the future if the table grows bigger. With all of that said, the current query and table structure that you have won't give you the best performance. If an improvement is needed then you have three main options:

Create a nonclustered primary key on the id column and a clustered index on Category.
This will give you the best possible performance for the query that you listed in the question. The downside is that changing the clustered index may have negative impacts on other queries. Clustered indexes do not need to be unique. You also do not need a primary key on your table. I just proposed an example schema that had both (I assume that you have a primary key today).

Add a nonclustered index on Category that includes all other columns.
This will also give you the best possible performance for the query that you listed in the question. As a downside it will double the disk space required for the table and will slow down DML operations on the table.

Add a nonclustered index on Category that doesn't include all other columns.
This won't give you the best possible performance. It will improve performance as long as you're filtering on a value that's selective enough and SQL Server chooses to use the index. If you need to help the optimizer along, the techniques in this blog post by Distinguished Answerer Erik Darling may be helpful. There's still a DML impact to this option, but it should be significantly less than option 2.


Ultimately the only way that you can get a satisfactory answer is by looking into it yourself. We don't know what kind of importance that you put on DML performance, total disk usage, the performance of other queries, etc. All of the options that I mentioned above have their own drawbacks. Good luck!