SQL Server non-clustered index

Question

I have two different queries in SQL Server and I want to clarify

1. how the execution plan would be different, and
2. which of them is more efficient

Queries:

SELECT * 
FROM table_name  
WHERE column < 2

and

SELECT column 
FROM table_name 
WHERE column < 2

I have a non-clustered index on column.

I used to use Postgresql and I am not familiar with SQL Server and these kind of indexes.

As I read many questions here I kept two notes:

• When I have a non-clustered index, I need one more step in order to have access to data

• With a non-clustered index I could have a copy of part of the table and I get a quicker response time.

So, I got confused.

One more question is that when I have "SELECT *" which is the influence of a non-clustered index?

Answer 1

1st query :

Depending on the size of the data you might face lookup issues such as Key lookup and RID lookups .

2nd query :

It will be faster because it will not fetch columns that are not part of the index , though i recommend using covering index ..

I recommend you check this blog post