Using Views 4

[CP60]

Is it alot more efficient to use a View and base a dynamic query on this, (SELECT * FROM MyView WHERE SomeField = 1) than using a complete dynamic query (SELECT * FROM SomeTable WHERE SomeField = 1), because it is then all already prepared, except for the WHERE clause?

 

[mrdenny]

Unless you have an index on your view, it is no more efficient to use a view over a table.

When you use a view without an index on it (which is how most views are used) the only data being stored is the design of the view.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[CP60]

Thanks.

Then, why use a view?

 

[Welshbird]

Chilipepper;

Its a bit like having stored a query in Access - it just means you can have some quite complicated logic and joins, and get to the result set as if it were a table. Also, because you haven't built it as another table then you get dynamic updates and no duplication of data.

(I'm sure someone will have a more technical answer that that!)

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[CP60]

But, couldn't that be done just the same using dynamic sql or using Stored Proceedures?

 

[Welshbird]

But a view behaves exactly as if it were a table.

Fee

"The cure for anything is salt water – sweat, tears, or the sea." Isak Dinesen

 

[CP60]

So I could just use a dynamic sql off the table.
What you are saying is that a View is just a SQL design and maintenance helper, not having to hard code an SQL statement into an application? That makes sense.

 

[SqueakinSweep]

And then you can write a Stored Proc which works off a view!

I originally had this scenario in 2000, and I ended up with serious speed issues which I resolved using temporary tables for my "inner select". Not tested the SP based on a view scenario in 2005 however, simply because I avoid using views at all costs.




Sweep
...if it works, you know the rest..
Always remember that Google is your friend

 

[CP60]

Thanks for your input SqueakinSweep. That confirms it even more.

I think it boils down to the fact that a View always returns all records based on the filter built into the View, but any filters applied externally (dynamic SQL, Stored Proceedure, from Crystal Reports, etc.) will get applied after the View is opened.

Therefore, I think a view is only good, if you are willing for SQL Server to first open all, or most, records that a View is built to show, when there are many records in the underlying table(s). If that, or if there are only a few records, then a View seems to be a practical thing to use, otherwise it is apparently a ressource killer.

 

[danvlas]

Dynamic SQL is open to SQL Injection, whereas 'Select WhateverField From WhateverView' is not.

Simply because the user has the right to open the view and filter it, but does not have any rights on the underlying tables.

From what I tested in an Access project linked to an SQL Server 2000,

Select [Top 100 Percent] * From View Where SomeField = SomeCriteria
(the red part may be generally ommitted)

runs much faster than

Select * From View

which says that the server asseses the filter somehow before sending the result. And that may be good enough, depending on what you need.

Of course, it's often slower than a stored procedure, but if you have a 100 field resultset that should be filtered by the end-user according to his/her wishes, you will end up with a view or an in-line function that can be filtered from the client program.

Bottom line: you should never use Dynamic SQL if you care about your data at all. Instead, use views, in-line functions or stored procs.

Whatever you use, is far better than dynamic sql. I know a few guys who learnt it the hard way...




Daniel Vlas
Systems Consultant

 

[CP60]

Thanks for the info danvlas.

What is an "in-line function" and how is this called from the client?
Can a filter be applied to it?

 

[danvlas]

From Books-On-Line:
"
Inline User-Defined Functions
Inline user-defined functions are a subset of user-defined functions that return a table. Inline functions can be used to achieve the functionality of parameterized views.

...
Users can then get the data for their specific store by selecting from the inline function:

SELECT *
FROM fn_QuarterlySalesByStore( 14432 )
where yourcondition = something
"
(I added the red part)

In SQL 2005, you find them as 'Table-Valued Functions"

Actually, an in-line function is a 'combination' between a stored proc and a view.

It allows parameters and 'first-hand' filtering as well as client filtering, but it does not allow some features specific to stored procs...

Books-On-Line explains everything pretty well about them...




Daniel Vlas
Systems Consultant

 

[CP60]

I think THAT'S what I need!