Querying a view that is defined as "select * from" I am trying to understand how columns/indexes are used when querying a view that is defined as

select * from someTable where someColumn=someValue


Will using this view always be bad, or will SQL Server take into account the actual columns selected and use appropriate indexes in a query like

select column1, column2 from theView
I think this would actually be a good exercise for you.  Create a table with a bunch of columns.  Create several different indexes (including a clustered index) on the table.  Try several variations of SELECT's against the table and tell SSMS to include the actual execution plan.  You will be able to see first-hand what indexes (if any) are used and what columns are returned.  Then, you won't have to take someone's word for it. 

As far as SELECT * in general, there is some excellent information, courtesy of Dave Markle, here.

To summarize the linked post:


  Here are really three major reasons why SELECT * can be problematic:



Inefficiency in moving data to the consumer. When you SELECT *, you're often retrieving more columns from the database than your application really needs to function. This causes more data to move from the database server to the client, slowing access and increasing load on your machines, as well as taking more time to travel across the network. This is especially true when someone adds new columns to underlying tables that didn't exist and weren't needed when the original consumers coded their data access.
Indexing issues. Consider a scenario where you want to tune a query to a high level of performance. If you were to use *, and it returned more columns than you actually needed, the server would often have to perform more expensive methods to retrieve your data than it otherwise might. For example, you wouldn't be able to create an index which simply covered the columns in your SELECT list, and even if you did (including all columns [shudder]), the next guy who came around and added a column to the underlying table would cause the optimizer to ignore your optimized covering index, and you'd likely find that the performance of your query would drop substantially for no readily apparent reason.
Binding Problems. When you SELECT *, it's possible to retrieve two columns of the same name from two different tables. This can often crash your data consumer. Imagine a query that joins two tables, both of which contain a column called "ID". How would a consumer know which was which? SELECT * can also confuse views (at least in some versions SQL Server) when underlying table structures change -- the view is not rebuilt, and the data which comes back can be nonsense. And the worst part of it is that you can take care to name your columns whatever you want, but the next guy who comes along might have no way of knowing that he has to worry about adding a column which will collide with your already-developed names.


But it's not all bad for SELECT *. It is often used it liberally for these use cases:


Ad-hoc queries. When trying to debug something, especially off a narrow table I might not be familiar with, SELECT * is often my best friend. It helps me just see what's going on without having to do a boatload of research as to what the underlying column names are. This gets to be a bigger "plus" the longer the column names get.
When * means "a row". In the following use cases, SELECT * is just fine, and rumors that it's a performance killer are just urban legends which may have had some validity many years ago, but don't now: