Accessing Tables vs. Views

  • Any reason why it would be better to have applications access views rather than tables? I recently worked with a DBA who requested that I create a view to mirror each table and access that rather than the table directly for all transaction types. I'm a little concerned about this request as the extra layer of views that are nothing more than select * from tblA are just adding more risk and longer performance time to the execution of my stored procedures. Any thoughts?

  • Hi SueStill,

    the first thing that came to my mind is the security aspect. You have definitely more control on permissions by using procs.

    Accessing data via view inside a proc seems to me like creating unnecessary overhead. Maintenance of the app is also likely to become more complicated.

    Cheers,

    Frank

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Usually it is to add an extra layer of security. However, if you are using in SPs then I would write the code directly to the SP as there is less of a chance changes in the table can cause issues with the SP that way. A view remembers items ordinal position and if you make a change to the table without running sp_recompile then it won't adjust with it. You can no real bennifit from doing a view of the whole table.

  • To be more specific, I use ADO to call SP, which runs against view. I'd like to use ADO to call SP, which runs against table.

    Thanks for the bit about recompile...that adds another risk of using views that mirror tables rather than the actual table iteself.

  • We code procs directly to the table (unless we really need a view), but commonly I do use select * views when I need to grant access for selects outside of a proc - ad hoc searching for example. Good rule is that end users should NEVER access the table directly, going through either a proc or a view.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply