SQL View Question

Question

Are there any negative affects of creating Views, specifically large ones (50+ columns) on the database?

Answer 1

From a performance perspective I would say there isn't really a negative effect per se. If the underlying query is inefficient and likely to causing undesirable locking, the view will inherit those problems, though somewhat mitigated by the fact it is more likely to be cached than an arbitrary statement. I'd say where the risk for negative effects comes in is that by creating a view and abstracting the complexity of a query you run the risk of it being used without a firm understanding of what the characteristics of the underlying query are. Then on the positive side views also open up some different optimization possibilities, (indexed views, etc) and there are times when being able to de-normalize a set of data can significantly reduce the need to write redundant queries.

So, as with most tools, whether they help or harm is in the hands of the implementer.

Answer 2

Depends if it's 1 column from 50 inner-joined tables or 50 columns from 1 table.
To be honest, either is fine, as long as you aren't using lots of scalar functions in them.

Come to think of it, this is a very subjective question. Paste some code ;)