I figured an indexing question would best be answered by folks who focus on performance tuning, if there was a better forum for this please let me know.
Anyway I was looking at: http://msdn.microsoft.com/en-us/library/ms177484(SQL.90).aspx and an article talking about indexing.
With included columns you can add them to a NONclustered index so that when a query uses that index additional columns the query SELECTs can be "included columns" and returned as part of the INDEX SEEK instead of having to hop over to the table, lookup the table row, and then scan that row of the table to get whatever columns are needed.
nonclustered index on: FirstName, LastName.
included columns: EmailAddress, PhoneNumber
The table would also of course have a PK which is part of the clustered index.
The clustered index might have other columns, maybe LastName or "whatever".
It sorts the table and is used to quickly locate records.
Here is what I don't get:
I do my query: SELECT FirstName, LastName, EmailAddress, PhoneNumber
WHERE FirstName = 'John' AND LastName = 'Doe'
It then uses my NONclustered index to perform my query AND it would then locate the record(s) that match my criteria.
What happens then?
It looks like it would then have a pointer to the clustered index which it would use to locate the record in the table, and retrieve the data.
Except in this case it is a COVERING INDEX... and it has Email and Phone stored in the NONclustered index's leaf level.
So... what does it do?
Does it grab Email and Phone and THEN still jump to the clustered index to locate the row in the table so it can pull out First and Last Name?
What I'm getting at is this:
CLUSTERED indexes store the actual table at their leaf level, is that correct?
NONclustered stores pointers to the clustered index (the table) or to artificial keys in a heap (tables with no clustered index), right?
So... how does the included column on a NONclustered index allow the query to avoid having to lookup the data in the table itself?
That's the part I don't get.
It seems like it grabs Phone and Email from its leaf level, but still has to jump to the clustered index to get the Fname and Lname since it doesn't actually store those data values in the index, just pointers to them...
So what am I not following/understanding?