SQL View Question

Question

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

Answer 1

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 ;)

Answer 2

In my experience a view gives you exactly the same performance as if you queried the physical tables directly.

Answer 3

If it's an indexed view it will take up more space in your db and slow down updates to records in the base tables.

Answer 4

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 5

A SELECT on a view (non-indexed) does something like:

SELECT Xyz FROM
(
    SELECT Abc FROM yourbigtable
)

So check the performance of the queries you want to achieve first I'd say.

Try to resolve the problem without views first, then simplify with it.

Mike