If you don’t use the stored procedure often, the performance difference will not be noticeable if you call a view instead of a table. But if you do call the stored procedure often, then you will indeed be able to see a difference.
Standard views (excluding indexed views in SQL Server 2000 and 2005) are best used as a way to ensure users are only able to see the data from a table that they are supposed to see. Views should be a part of your overall security strategy, and used only for this purpose.
Reader’s Feedback
I agree with this answer when you are designing a new database, but I have also found views very useful when modifying an old database.
I ran into a situation where member data was being stored one way for an old system and another way for a new system. Management wanted to stop making their users enter data twice, so they wanted the old system to start using the new tables.
One option would have been to rewrite all of the old VB code to make it use the new stored procedures that referenced the new tables. Another option would have been to rewrite the old stored procedures to use the new tables. However, a simpler (and cheaper) option was to create views that generated resultsets that looked like the old tables, but used the data from the new tables.
We dropped all of the old tables and gave the views the names of the old tables. By doing this, all of the old stored procedures automatically started using the new data and we did not have to rewrite any stored procedures or VBcode that retrieved member data. We only had to redo the stored procedures and VB code that inserted or updated data.
Paul Lach
Byrne Software Technologies, Inc.