optimizing queries

Hi,
I am about to start system optimization and I have a general question about
how to proceed.
I am a coldfusion programmer and working on a lagre system with a lot of db
interaction. Recently, our isp upgraded to SQL 2000 and I started using
functions quite extensively for validation and calculations. I have a lot of
large queries that I am planning to transfer from coldfusion scripts to sql.
My question is:
SQL Server has few options for queries: stored procedures (I use them a lot
for complex logic), views and functions. What should I use for queries? For
example views can be indexed same as tables, stored procedures are compiled
and last one is functions (I dont think is such a good idea to store queries
but they can return tables). What are general recommendations?
Thanks in advance,
GeneThe answer: It depends.
If you can take part of your complex operation and make an indexed view, it
is certainly worthwhile, esp. if this view of information is used in more
than one procedure.
If the index will not help and there is no reuse, the procedures are the
best avenue for most work that performs CRUD on one or more tables.
Functions are used to solve problems outside of the data set realm (at
least, in general), like special calculations of aggregations or date
formatting outside of what is natively in SQL Server. For example, we had a
function like:
SELECT OurCustomerDateFormat(DateField) FROM SomeTable
That was a function.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"News" wrote:

> Hi,
> I am about to start system optimization and I have a general question abou
t
> how to proceed.
> I am a coldfusion programmer and working on a lagre system with a lot of d
b
> interaction. Recently, our isp upgraded to SQL 2000 and I started using
> functions quite extensively for validation and calculations. I have a lot
of
> large queries that I am planning to transfer from coldfusion scripts to sq
l.
> My question is:
> SQL Server has few options for queries: stored procedures (I use them a lo
t
> for complex logic), views and functions. What should I use for queries? Fo
r
> example views can be indexed same as tables, stored procedures are compile
d
> and last one is functions (I dont think is such a good idea to store queri
es
> but they can return tables). What are general recommendations?
> Thanks in advance,
> Gene
>
>|||>> I started using functions quite extensively for validation and calculatio
ns. <<
Usually a bad idea for validation. SQL is declarative and you want to
use CHECK() and DEFAULT clauses that apply to an entire column, rather
than triggers, functions and procedures which work on scalars.
I would go with stored procedures and VIEWs for the calculations.
Functions that return tables and indexed views are proprietary.