Select statement take more time in sql Statement 1 : select top 1000 pmch.Columnname1
Statement 2 : select top 1000 pmch.*
Statement 2 executes quicker the  Statement 1.
I know that using &quot;SELECT * &quot; will affect performance so should use column names instead of &quot; * &quot;.
But, here it works differently.
Can anyone suggest under which condition these type of issue occurs?
A large difference in time makes me think there is significantly different IO happening for these two queries.
The second query qualifies for trivial optimization. Likely it is performing a table scan, on the heap or a clustered index.
The first query projects only a single column. If there is an index with that column as a leading key the optimizer is likely to choose to read from that index as doing so will require fewer IOs.
So how do fewer IOs result in longer elapsed times? Let's suppose the base table's pages are sequentially ordered on-disk and can be efficiently scanned, perhaps with pre-fetching. This can be fast. Further, let's suppose the index is badly fragmented on-disk so each new page read requires an on-disk seek. This will be slow.
In summary, statement 1 uses an index scan on a badly fragmented index suffering slow seek times while statement 2 uses efficient sequential disk scan.
To be sure look at the two query plans. If I'm correct the respective objects read will be obvious in each plan. The amount of fragmentation can be found from the documentation. That link also gives the solution. Note that fragmentation need not be a problem, depending on the hardware and workload.
Fragmentation can occur due to rows being added in the middle of the key range. This is a natural part of application usage. It can be mitigated by an appropriate fill factor. It can also happen when database files are shrunk. Don't do that.