In SQL Server 2012, (or any version from 2005 up), using
SELECT *... is only a possible performance problem in the top-level SELECT statement of a query.
So it is NOT a problem in Views(*), in subqueries, in EXIST clauses, in CTEs, nor in
SELECT COUNT(*).. etc., etc. Note, that this is probably also true for Oracle, and DB2, and maybe PostGres (not sure), but it is very likely that it is still a problem in a lot of cases for MySql.
To understand why (and why it is still can be a problem in a top-level SELECT), it is helpful to understand why it ever was a problem, which is because using
SELECT *.. means "return ALL of the columns". In general this will return a lot more data than you really want, which obviously can result in lots more IO, both disk and network.
What is less obvious is that this also restricts what indexes and query plans a SQL optimizer can use, because it knows that it must ultimately return all of the data columns. If it could know ahead of time that you only want certain columns, then it often can use more efficient query plans by taking advantage of indexes that only have those columns. Fortunately there is a way for it to know this ahead of time, which is for you to explicitly specify the columns you want in the column list. But when you use "*", you are forgoing this in favor of "just give me everything, I'll figure out what I need."
Yes, there is also additional CPU and memory usage to processing every column, but it is almost always minor compared to these two things: the significant extra disk and network bandwidth required for columns that you don't need, and having to use a less optimized query plan because it has to include every column.
So what changed? Basically, the SQL Optimizers sucessfully incorporated a feature called "Column Optimization" that just means, that they can now figure out in the lower-level sub-queries if you are ever going to actually use a column in the upper levels of the query.
The upshot of this is that it doesn't matter anymore if you use 'SELECT *..' in the lower/inner levels of a query. Instead, what really matters is what is in the column list of the top-level SELECT. Unless you use
SELECT *.. in the top, then it once again, must assume that you want ALL of the columns, and so cannot employ column optimizations effectively.
(* -- note that there is a different, minor binding problem in Views with
* where they do not always register the change in columns lists when "*" is used. There are other ways to address this and it does not affect performance.)