I recently came across a comment on a web page that said something to the effect of..
"I don't use views because of temporary tables, table variables and UDF's."
Well tickle me pink.. but what the hell does that mean? I suspect that either the author has only ever worked on a single front-end/single database/single security account scenarios or has absolutely no clue what views are for.
Besides the security goodness, abstraction of underlying complexity and its data integrity features, I suppose you could say they are useless.... :-)
Usually I ignore these comments but some current application changes screamed for the use of views and thought I would post a real world example.
Some of our data is collected from an external data source and this data is used across multiple applications. All of sudden the schema of the data source is changing and one of the 4 applications is going to be updated to accommodate this new data. I won't bore you with the legalese but only one copy of the data is ever allowed to be maintained (besides an agreed backup). So that puts an end to simply creating a new set of tables.
When running sp_depends against the offending tables reveals nearly 20 procedures relying on this data, the unknowing BA's factor in lots of time and testing for the change and the impact on the other applications. Don't despair BA, a good old fashioned view will do the trick. By dropping the old tables and creating views with the same "interface", we accommodate the change with no impact on any of the other applications. We can now concentrate on incorporating the new data requirements into the changing application
Could some one explain how temporary tables, table variables and UDF's could help here? :-)
| posted on Thursday, April 08, 2004 4:19 PM