Performance of dynamic SQL Server reporting queries? I have to build an application that allows the end user to select what columns they want in their report.

For this I build a dynamic T-SQL stored procedure. However, there are up to 60 columns available that could potentially be selected.

I am worried that this will greatly harm the performance of the query. What are my options? Create indexes for each possible query is not really possible.

Would a columnstore index be beneficial?
Dynamic SQL isn't in and of itself a performance problem. After all, the queries run against SQL Server regardless of how they were assembled, and still generate (and cache) the same execution plans as identical queries not constructed dynamic SQL. In ancient SQL Server versions, things like stored procedures were compiled as a whole, but SQL Server has been able to optimize at the statement level for a long time now.

What you need to be sure of is:


that the rows you are after can be seeked efficiently. You're never going to be able to create a covering index for all 60 columns, but if you can reduce the number of rows, any lookups or other mechanisms to get the columns not in the index shouldn't be too terrible. Hard to get into more specifics here, without any specifics to start with. I'm not sure if a columnstore index is going to give you what you need, especially because in SQL Server 2012 at least, there are a lot of limitations that most shops can't work around. 
another option is to ensure that your index(es) used for searching to locate rows have all the output columns that aren't used as predicates as INCLUDE columns. These don't take up space in the index key but can help prevent lookups without creating a different index for every output combination. The trick here is to simply always return all columns, but move the logic of which columns to show to the reporting tool. Another nice thing about this is that if the user decides they want to add these three columns and hide those other two, the application should be able to do that without making another round-trip and re-querying all the data.
make sure you are using the optimize for ad hoc workloads server setting. This prevents single-use plans from occupying space in the plan cache wastefully.