Sql-server – Non-Clustered-Index on a temporary table

The problem is that the query has to retrieve 13.7 billion rows from FIELDDATA in aggregate. This is caused by two significant issues with your query. One will be easy to solve, one will be harder.

The easy problem is with the index. As one commented suggested, you need to add a nonclustered index on FIELDDATA(ID, FIELDID). The clustered index scan is pulling 5764 rows each time it's called, then filtering down to the one row it needs to return. The index should allow it to pull only one row each time it's called, reducing the total to around 2.3 million.

The larger, more difficult problem is that you're using subqueries to return column data in the SELECT clause. SQL Server processes the SELECT clause after most of the other clauses in the query. In this case, the FROM (JOIN) clause would be processed, then the WHERE clause would filter certain rows from those returned by JOIN. The SELECT clause is then applied to each of the rows returned by WHERE. In this case, that's 395,939 rows. Each subquery in the SELECT clause is run once for each row - since you have subqueries on 6 columns, you are querying FIELDDATA 2.3 million times each time your query is run.

To reduce that, you need to get those subqueries out of the SELECT clause and into something that can be joined in your FROM clause. If you're using SQL Server 2005 or later, my best suggestion would be to create a common table expression (or subquery) with a PIVOT clause to bring all of the SVALUE values for each ID into one row. If you're using an earlier version, your best bet may be to extract the FIELDDATA data into a temporary table prior to this query, again with one row per ID that contains all of the necessary SVALUE values.

Can i replace the non-clustered indices with one covering index ?

No. Suppose you sometimes find people by LastName and sometimes by FirstName. An index on (LastName, FirstName) won't help you find people by FirstName.

If not, is there any way to get rid of RID Lookup rather than a covering index ?

Not in a way that's particularly useful.

Does each query require a different covering index depending on the columns in the select list and the search conditions ?

Pretty much. Your job is to come up with a compromise.

I have no clustered indexes in the table , does adding a unique column and setting it as a primary key helps in getting rid of RID Lookups ?

A PK doesn't necessarily mean a clustered index. And having a CIX just means your RID Lookups will become Key Lookups, which are potentially worse. But without CIXs you have heaps which can fragment when you change or delete data. So CIXs are fine, but won't improve performance of your Lookups.