What is the problem with using wildcards for selecting columns?

I had an old view that was giving out some odd data when I would query it. Two of it's columns, C and D, had a copy of data from columns A and B respectively. So C had a copy of A's data and D had a copy of B's data. When I extracted the query used by the view and ran it standalone everything was fine. Columns A, B, C, and D had the data that I expected to see. When I looked at the view definition I noticed that it had some wildcards (*) for column selection like so:

SELECT
   TableX.*,
   TableY.*
FROM
   X AS TableX INNER JOIN
   Y AS TableY ON TableX.PK = TableY.FK

I was told never to use wildcards in views for various other reasons but I was wondering why it has this effect? I noticed that when I re-create the view and run a select query on the view that everything is fine. One of the senior developers informed开发者_如何学Python me that the problem occurs because of some caching that Sql Server does but I was hoping for a more detailed answer.

One reason it's bad because if TableX and TableY have the same column name, then you will have column name conflicts in your result.

Another reason is because you should only pull the columns you need. If the table has 75 columns and you only need 4, then it's a lot of unnecessary data you're pulling, and that extra data has to be sent over the wire to your client application so it can affect performance.

From Books Online:

If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried.

• is undefined. If you will be relying on a specific column order, you will get unpredictable results.

If new column will be added to the table you will load additional (probably not needed data) column.

1. You can break random code by adding a new column if you pick a name that's already in use.

2. New columns will be automatically be retrieved from the database in random parts of your app just to be discarded. Even if they're a huge BLOB.

3. You have no easy way to find out where in your code certain column is used.