Set-based processing treats rows as a collection and uses relational operations instead of iterating through individual rows. Application developers accustomed to C# or Visual Basic may find looping natural, but row-by-row logic can cause serious SQL performance problems. section 5.0
A set-based operation is not automatically a presentation operation. Relational results do not require an order, so sorting should be added only when the consumer actually needs ordered output; unnecessary sorting can make a query substantially slower. section 5.4
MagicChoc customer contacts
Return the required customer-contact columns without ordering them when presentation does not require a sequence. Adding ORDER BY CustomerContactEmail introduces a sort; one measured comparison took 27,415 ms, 25% longer than the unordered query. section 5.4
Listing 5.8 Returning required data from the CustomerContacts table
SET STATISTICS TIME ON ;
SELECT
CustomerContactFirstName
, CustomerContactLastName
, CustomerContactEmail
FROM dbo.CustomerContacts ;
Use this query as the minimal set-based retrieval example and compare its timing with the ordered version. section 5.4
Listing 5.9 Returning data from CustomerContacts ordered by email
SET STATISTICS TIME ON ;
SELECT
CustomerContactFirstName
, CustomerContactLastName
, CustomerContactEmail
FROM dbo.CustomerContacts
ORDER BY CustomerContactEmail ;
Use this query to inspect the cost of adding presentation ordering to an otherwise set-based retrieval. section 5.4
