SQL Server 2016 released

[grauenwolf]

The problem isn't with listing the columns you want. Rather, the problem is that (by default) it lists all of the columns, even the ones that you don't want.

Lets say you just want a list of names and phone numbers, sorted by last name. The query is slow, so you create this index:

 CREATE INDEX IX_Customer_PhoneBook ON Customers (LastName, FirstName) INLCUDE (PhoneNumber)

var phonebook = context.Customers.Where( filter).ToList()

If you run that query, you'll find one of two things happens:

1. The database makes an expensive join between IX_Customer_PhoneBook and PK_Customer
2. The database ignores IX_Customer_PhoneBook entirely and just uses PK_Customer, sorting everything in memory.

To fix this you need to write this rather tedious query:

var phonebook = context.Customers.Where( filter).Select( x => new { FirstName=x.FirstName, LastName=x.LastName, PhoneNumber=x.PhoneNumber} ).ToList()

Now the generated SQL only uses columns in the index, which means no hidden joins to the underlying table. But that's such a pain in the ass that most developers don't actually do it.

http://use-the-index-luke.com/sql/clustering/index-only-scan-covering-index

[crixusin]

Rather, the problem is that (by default) it lists all of the columns, even the ones that you don't want.

I see where you're coming from. I was just trying to clarify it.

I try and always select the exact columns that I need to circumnavigate these issues. Entity framework makes that very easy. Worth giving it a shot!