asked
Implications of using views to select data instead of sprocs
I am currently designing a security model for the first time on a new database. I have decided to use schemas for each conceptual application so for an example I have created an 'Admin' schema which I have added to a TestTable with Id and Value. I have then create a view and added that to the schema. I then created insert, update and delete sprocs that do the necessary updates on that table. My question now is should I create a select sproc or is it fine to use views for selects of data and sprocs for the physical updating of the tables?
I would say that it is generally a good practice is to have a stored procedure that returns the data as it is needed for each individual requirement. i.e. `stproc_Admin_GetUserLoginDetails`, `stproc_Admin_GetActivityLogEntriesForUser` - or whatever. However, I have in the past made 'general select' procedures, which I made relatively configurable - such that they could return data from any table and handled the paging of data, as well as resolving foreign keys for display. These fit quite well into 'general data' applications where you either don't know what the exact requirements are, or the application requires a basic level of table view / edit / update functionality. A bit more about the 'resolving foreign keys for display' bit... What I have generally done is used a table called 'Candidates' which gives you information on how a user would like to see a row from a particular table represented. So - you might have a table 'Users' which is then pointed to by a column called UserID in a log entry table (for example). So typically, for a user, you would want to see the user's name. So my candidate for the Users table would be something like $0.FirstName + ' ' + $0.LastName - and then the procedure selecting from the log table would return two columns from the log table - UserID (the natural value as it appears in the table) and UserID__UserName (the value as it would want to appear when displayed). I'm not saying that's a good or a bad practice particularly, but it's just a technique I've used quite a lot to ensure: - Paging functionality is in one place for list displays - Foreign key resolution and formatting is centralised - so a change in format for a particular table means a change in one place - not in hundreds of procedures - Data list pages hit the database once and only once while generating the data list
I would say you should create stored procedures for the SELECT's as well. For simplicity, these stored procedures could use the views you have created. A stored procedure will often perform better than querying a view from your application, and a stored procedure helps you isolate/hide the data structure from the application.
I would be very cautious about using all views for this type of data access. It really depends on what you're doing with the views, how complex the queries are, what the queries within the views look like, etc. The more complex the situation becomes, the more likely you are to run into major performance problems. This is because the optimizer doesn't always have adequate time to resolve the views to the point that it can identify which parts of the query that define the view are being used and which are not. It does this very well (the optimizer is a prettying amazing bit of programming), but it doesn't always have adequate time to resolve extremely complex views or extremely complex queries. This is why you see so many people cautioning against joining one view to another. Views are an excellent masking mechanism if you're exposing your system to end-users for ad hoc querying. But if you have control of the access, instead of views, I'd go with stored procedures because you can tune each of them individually, allowing direct access to the underlying tables, but still masking structures from the end-users.
That's an acceptible approach, of course. It's even encouraged. But you have to keep an eye on it because it can grow, fast.
