Suppose you have a table (tblDemo) and a view (vwDemo) over that table that joins tblDemo to another table to retrieve their results.
The view might resemble the following:
CREATE VIEW vwDemo
AS
SELECT d.*, o.OtherField01, o.OtherField02
FROM tblDemo d
INNER JOIN tblOther o ON d.ID = o.ID
While I've never advocated selecting * from anything, it does have its uses; but it turns out there's potentially a nasty gotcha. Personally, I like to err on the side of higher specificity, though that usually requires a bit more manual tweaking as time goes on.
What happens if the schema of tblDemo changes? Well, if you don't update or recreate your view, you may very well get some unexpected side effects.
For instance, if a new field were added to tblDemo (e.g., ALTER TABLE tblDemo ADD NewField int), the view, when inspected in SQL shows the following:
SELECT d.ID, d.Field01, d.Field02, d.Field03, d.NewField AS OtherField01, o.OtherField01 AS OtherField02, o.OtherField02
FROM tblDemo d
INNER JOIN tblOther o ON d.ID = o.ID
Notice that the 'NewField' got aliased with the field name at the new fields position! That field, in turn, received the next field's alias, and so on. Understandably, this can have some very negative side effects in your applications.
For this reason I would strongly recommend AGAINST using SELECT * in a view in SQL Server. Of course, if your view does nothing but SELECT * on a single table or the '*' is the last part of the SELECT clause (such as SELECT o.OtherField01, o.OtherField02, d.* FROM ...) then you circumvent the issue. When used in conjunction with other columns, however, there are indeed repercussions.
I don't know that this is a bug in SQL Server, though it may appear to be at first. I'm not a SQL Server guru (though I'm exceptionally dangerous with it :)), but I suspect that upon building the view, SQL catalogs all of the fields in a pseudo-table. Changing the schema thereof, without updating the view causes a misalignment between the new schema and the pseudo-table that was build previously.
Can anyone confirm this or elaborate on the behavior?