• No, a view is not "recompiled" every time it's called. A view is just a stored Select statement. Queries of a view will store their execution plans as per usual plan-caching in SQL Server.

    A proc that's just "SELECT * FROM dbo.MyView" is just a waste of code, really. It has the single advantage that you can rewrite the proc to do more, and so long as the inputs (you didn't list any) and outputs (the dataset) are the same, code calling it doesn't have to be rewritten. But without anything beyond a select from a view in it, your opportunities for that are pretty much the same as they would be for rewriting the view and calling it directly.

    There is no automatic performance benefit to procs over views. There are functionality benefits, in terms of what you can do with a proc vs what you can do with a view. Things like input and output parameters, error-handling, flow control, logging, etc., in a proc, are advantages for a proc, functionality-wise. Things like being able to Select From, Insert Into, Delete From, and Update, are advantages to a view (if the view is written to allow those things, or has the necessary Instead Of triggers on it to make those things possible even if the view violates the usual rules). Views can also (in some cases) be indexed, while procs can't. But there is no inherent speed difference between the two types of objects.

    The idea that views are recompiled every run while procs are "precompiled" is an old "urban legend" in the SQL Server world. Lots of developers believe it, but lots of people believe in alligators in the sewers, Santa Clause, and that Elvis is still alive. Same thing.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon