Looking for a bit of advice re: Indexing strategy for speeding queries up on a particular table / structure we have in our system.
We have a table (call it Orders) which has 60 columns of core data in effectively a de-normalized state (mostly nvarchar columns) which contains a lot of data driving a Grid on a UI. This grid gives the users the ability to filter (with come criteria such as equals, or like) and sort by (up to) 5 columns currently, dynamically in accordance to their own preferences. There is a huge product incentive to allow this flexible sorting/filtering rather than making it statically defined operations via procedures and functions based on fixed columns.
We need to ensure the performance of retrieval of the data retains its performance, and this would normally indicate having indexes on the table columns most frequently used in the queries - but in this case the columns used will be flexible based on user preferences and they could look to select any 5 of these columns.
I'm told you shouldn't put an index on every column as it is detrimental to the insert/update query performance.
What is the typical strategy used here on the database side? Should we have a single index covering all of the nvarchar columns?
Appreciate any advice here!