I have a table in a SQL Server 2012 SP1 database which has around a dozen columns that are all searchable. The user using the application can search by one or multiple columns depending upon their needs, with a SQL statement created at runtime.
Because of this, we've setup a dozen indexes, one for each searchable column. We have not included the other columns in the index, since having 12 copies of the same data in each index just seems wrong (and the DBAs keep whining about disk space).
Unfortunately, this leads to a lot of RID/Key lookups, since not one single index covers any query that's executed (the queries run by searches on the application return about 20 of the columns in the table, including the dozen which are searchable).
The table has around 30 million rows to it, but a normal search only returns anywhere from 1-1,500 rows, so the problem isn't (yet) horrible, I'm just trying to be proactive.
So, to try to avoid as many RID/Key lookups as I can, what would be another way to index this table? One index with all the searchable columns doesn't help, since an index isn't used unless the first column is one of the criteria.