I'm taking a online tutorial/course on Performance tuning. I'm not understanding how a non-clustered index seems to be requiring significantly less logical reads than a query that just designated a where clause.
My understanding is a select query with a where clause will search through an entire specified column and only present the data that matches the requirements of the where clause.
The non-clustered index is created specifying the where clause in the exact same way, but when you run it it will only pull the exact data that was specified in the where clause, without doing a page-scan.
They seem to be functionally written the same, so how is the non-clustered index specifically pulling the data instead of searching the table? My best assumption is that a non-clustered index is running a select when it's generated, saving the data that matches the where clause, and automatically presenting it every time the non-clustered index is called. However it's most likely more technical than that, so if I could get some insight on this, I would appreciate it.
My best assumption is that a non-clustered index is running a select when it's generated, saving the data that matches the where clause
In a sense, that is correct. Whenever you add an non-clustered index to a table, the index stores a copy of the columns you specify as part of the index (or as an include). That copy of the data is then sorted in the way specified by the index to make future look-ups of that data easier.
Essentially, indexes are trading space for time. By using more storage space to store a sorted copy of data, you can search through that data much more efficiently. The down side is that the server must now maintain multiple copies of the data, so updating a column not only modifies the underlying table but also any indexes referencing that column. This is why you need to choose your indexes wisely and not just index everything.