Hi guys,
I've been asked to re-write a sql view. The view itself contains
several calls to other views (embedded). Is there a way to get around
using embedded views. I've written the same query up using temp.
tables but obviously temp. tables can't be used in views?
Is there any special things I should be looking for?
(colabus@gmail.com) writes:
> I've been asked to re-write a sql view. The view itself contains
> several calls to other views (embedded). Is there a way to get around
> using embedded views. I've written the same query up using temp.
> tables but obviously temp. tables can't be used in views?
>
> Is there any special things I should be looking for?
You could always incorporate the view definition directly into the
query that composes the view, and take care to exclude columns or
tables that are not relevant for the view. If you feel that temp tables
makes the query more efficient, you could make the view a multi-statement
function.
Without knowledge about the views, and why you have been asked to rewrite
it, it's difficult to give a very exact answer.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
>> I've been asked to re-write a sql view. The view itself contains several calls[sic: invocations?] to other views (embedded). Is there a way to get around using embedded views. I've written the same query up using temp tables but obviously temp. tables can't be used in views? <<
Nesting VIEWs is a good progrmming practice when it is done right. It
can assure that nobody invents their own definition of something, like
how we compute a tricky formula that can send us all to prison.
Temp tables are a baaaaad idea. The SQL Server model is in violation
of ANSI/ISO and most everyone else's model of them. They are usually
a way to fake a "scratch tape" in a procedural solution, where each
step passes the tape to the next step in a process; SQL is declarative
and we want to write that way.
But the real point is that you never said *why* you want to re-write
this unnamed VIEW. Damn to give advise about anything without any
kind of spec at all ..