SELECT

TABLE1.field1,

TABLE1.field2,

TABLE2.field1,

count(distinct(field4)) as Counts,

(count(distinct(TABLE2.field2)))

FROM TABLE1 LEFT OUTER JOIN TABLE2

on TABLE1.field1 = TABLE2.field1

WHERE

TABLE1.field4 >= 'dd/mm/yyyy'

and TABLE1.field4 <= 'dd/mm/yyyy'

and TABLE1.field1 like 'CloudSA%'

group by TABLE1.field1,

TABLE1.field2,

TABLE2.field1

order by TABLE1.field1 asc

Field1nvarchar100

Field2varchar5

Field3datetime8

Field4nvarchar100

Field5nvarchar100

Field6nvarchar100

Field7datetime8

Field8datetime8

Field9nvarchar100

Field10nvarchar100

Field11nvarchar200

Field12nvarchar100

Field13datetime8

Field14nvarchar200

Table2

Field1varchar50

Field2varchar50

Field3varchar50

Field4varchar50

Field5varchar50

Field6varchar150

Field7varchar50

Field8varchar50

Field9varchar50

Field10nvarchar300

Field11varchar50

Field12varchar50

Field13varchar50

Field14varchar50

Field15varchar50

Field16varchar50

Field17varchar500

Field18varchar50

Field19varchar100

Field20varchar100

Field21varchar100

Field22varchar100

Field23varchar100

Field24varchar100

Field25varchar100

Field26varchar100

Field27varchar100

Field28varchar100

Field29varchar100

Field30varchar100

Field31varchar100

Field32varchar100

Field33varchar100

Field34varchar100

Field35varchar100

Field36varchar100

Field37varchar100

Field38varchar100

Field39varchar50

Field40varchar50

Field41varchar50

Field42varchar50

Field43varchar50

Field44varchar50

Field45varchar100

Estimated plan shows the clustered index on table2 is 93% and is scanning. Clustered index is set on a column. that column is not used in the select query.