SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Friday, July 13, 2007

SQL Views - embedded view work-a-rounds

SQL Apprentice Question
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?


Celko Answers
>> 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 ..

No comments: