3

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?

Ahmad.Tr
  • 756
  • 7
  • 22
pelopid
  • 39
  • 5
  • 2
    What are you really asking? This question is extremely vague and it just wanders all over the place. – Sean Lange Feb 23 '18 at 21:48
  • 1
    Query #2 is going to be faster - just because you're selecting only a single column, instead of all columns (assuming of course that your table does have more than just that one `column` ....) and thus you need to transfer a lot less data. Also, for query #2, you can "satisfy" the query (fetch all columns needed) directly from the nonclustered index, while for query #1, you'd have to do a *key lookup* for each row found (or quite possibly, SQL Server would opt to ignore the NCI and just do a table scan on the table) – marc_s Feb 23 '18 at 21:57
  • Insightful question for a newcomer to SQL queries. The concept that your question and marc_s are describing is called a “covering index”. The index covers or satisfies the query. – Sql Surfer Feb 23 '18 at 22:14
  • Did you try looking at the query plans? – paparazzo Feb 23 '18 at 22:37

2 Answers2

2

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

Ahmad.Tr
  • 756
  • 7
  • 22
1

The first select will use the non-clustered index to find the clustering key [clustered index exists] or page and slot [no clustered index]. Then that will be used to get the row. The query plan will be different depending on your STATS (the data).

The second query is "covered" by the non-clustered index. What that means is that the non-clustered index contains all of the data that you are selecting. The clustering key is not needed, and the clustered index and/or heap is not needed to provide data to the select list.

kalaolani
  • 323
  • 1
  • 16
  • One way to think like the query processor or relational engine to always know by looking at the SQL what will happen is to remember processing order... 1. FROM 2. ON 3. OUTER 4. WHERE 5. GROUP BY 6. CUBE | ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10 ORDER BY 11. TOP – kalaolani Feb 24 '18 at 17:23