Query does not use index when selecting all records
You've used SELECT * which, presumably, has more columns than xploc and trandt2. So SQL Server can easily derive the count by traversing the index pages. It can't get the other columns that aren't in the index from there, so it has to go to the table.

Depending on how many columns are in the table (and a host of other factors), SQL Server can usually make one of two choices in a case like this:


Perform a seek (which looks like a range scan in your case), and then perform key/RID lookups to retrieve the other columns not covered by the index.
Suck it up and perform a scan.


In your case there are probably enough rows satisfied by the WHERE clause that it is actually more efficient to perform the scan.

Some ways to make this more efficient:


Stop using SELECT * - just list the columns you need.
Once you have whittled down to the actual columns you need, change the index to INCLUDE the other columns in the actual SELECT list you need.
You could consider making an index filtered by adding a WHERE clause, depending on whether this exact query is one that is run frequently (and if there is a lot of data pre-2012).


As an aside, why is there no clustered index on this table?