sql - Is it a good idea to index every column if the users can filter by any column. -

in application users can create custom tables 3 column types. text, numeric , date can have 20 columns. create sql table based on schema using nvarchar(430) text, decimal(38,6) numeric , datetime. along identity id column.

there potential many of these tables created different users , data might updated users uploading new csv files. best performance during upload of user data truncate table rid of existing data batches of bulk insert.

the user can make selection based on filter build can include number of columns. issue tables lot of rows have poor performance during selection. combat thought adding indexes don't know columns included in condition have index every column.

for example on local sql server 1 table on million rows , condition on 6 of columns take around 8 seconds first time runs under 1 second subsequent runs. indexes on every column run in under 1 second first time query ran. performance issue amplified when test on sql azure database same query take on minute first time ran , not improve on subsequent runs, indexes takes 1 second.

so suitable solution add index on every column when user creates column or there better solution.

yes, it's idea given model. there of course more overhead maintaining indexes on insert, if there no predictable common set of columns in queries, don't have lot of choices.

if 'updated frequently' mean data added via uploads rather existing records being modified, might consider 1 of various non-sql databases (like apache lucene or variants) allow efficient querying on combination of data. reading huge 'flat' data sets, astonishingly fast.