In SQL Server the * wildcard is useful feature when quickly putting together a query.
However there are certain situations where you should avoid using it. Namely you should not use it when you’re returning data from user defined function or a view (and possibly procedures as well). The reason behind it, is that SQL Server stores what a view or function returns (even if you don’t explicitly define it). When you ALTER or CREATE a function, the format of the returned data is defined. Let’s say your UDF executes this query:
And your Orders table contains following columns:
This function will obviously return all the rows in the Orders table and everything is fine. However a problem will arise when you decide to alter the structure of the Orders table. Let’s say we will insert an IsCnancelled column right before IsPaid. So the table will look like this:
The data returned by the UDF will be missing the IsCnancelled column, but that’s not all. The contents of the IsCnancelled column, will be presented as the IsPaid column. Such an error could wreck havoc. If you ALTERR the UDF (no changes are actually required), the structure of the returned data will be updated, but it’s better to avoid using the * wildcard altogether, than trying to manage this mess.
On the other hand, using wildcard in subqueries or CTE inside an UDF should be safe.
All this is probably obvious stuff to most people working with SQL Server and my example is very basic, but I had to fix problems caused by such use of the wildcard in the past and they can get ugly.