• ...if I have 100 different favorite types stored in this way, that's going to be a very wide non-clustered index.

    That is a potential design problem - I would consider spreading that data across multiple tables but, if you can't, you can still index in a way that improves performance without slowing other things down too much. If you are stuck with that design I would consider making a copy of that table that is updated regularly using SQL Replication, an SSIS job or the SQL agent to keep the copy up-to-date. Then the original would remain unchanged and the copy could handle non-OLTP reporting types of requests.

    It's common to setup Transactional Replication for this kind of thing where the original has minimal indexes but the copy is well indexed and is used for reporting.

    In the same vein, I have heard that indexed views can seriously affect performance of OLTP systems with a lot of activity...

    Just like tables, Indexes on views have the same pros and cons: they speed up data access queries but slow down data modification queries. To understand the benefits (or lack of) you need to do some testing. Lack of proper indexes can cause queries that would finish in seconds, finish in minutes - when that happens you have more locking/blocking/deadlocking. Correctly designed indexes also reduce I/O and memory pressure. Again - I would do some testing.

    This is where data warehouses really help - separating OLTP operations from reporting.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001