«

Hello,

I have used SQL multiple times in the past just to
maintain relatively simple databases. I purchased and
read microsoft press's review book for the 70-229 exam,
and there are many things on the exam (which I failed by 3
questions) which are not covered in the book. Could
anyone shed some light on the following:

- What should be used when querries to a database need to
merge multiple tables with joins: a view, a stored
procedure, or a user defined function?

- If some stored procedures (but not all) are working more
slowly than before, what should be done to improve their
performance: call UPDATE STATISTICS or use DBCC REINDEX
with the tables called in the slow stored procedures.

- Can someone give me an overview of the major differences
between stored procedures and user defined functions,
besides the fact that a stored procedure can't be used
within a SELECT query clause? what are the advantages of
a user defined function?

- Does the order of tables in a join statement affect the
optimizer's choice of an execution plan?

- If we have a table with a great deal of rows (2 million
or more), what is the best solution to improve performance
with parralel IO access: add indexes on the table, or
split the table horizontally and use a view to access all
of the data?

Thank you for the help.

Session

"Session" <> wrote in message
news:626801c42e0d$e6c92150$...
> - Can someone give me an overview of the major differences
> between stored procedures and user defined functions,
> besides the fact that a stored procedure can't be used
> within a SELECT query clause? what are the advantages of
> a user defined function?

They aren't similar enough that you can say one has advantages over the
other: they get used in different situations for different purposes.

udf can act as a "virtual table" for other TSQL code, sp cannot.
udf can act as a computed scalar value (afterall, it's a function!) in other
TSQL code, sp cannot.
sp can insert and update tabel data, udf cannot (it's merely a function *of*
other stuff)
sp can do DDL, udf cannot.

Brad Williams

Honest advice: ditch the MS book for 70-229 and get the
Que one by Thomas Moore. I started off with the MS one
and it was difficult to get through. The Que one is
superb and helped me pass 70-229 where I would have
probably failed if I had only read the MS one. The MS
one didn't even go into Replication and I had 2 or 3
questions on it!

The test software is pretty good with the Que book as
well. I thought the test exam with the MS book wasn't
representative of the actual exam at all, so what's the
point ?


>-----Original Message-----
>Hello,
>
> I have used SQL multiple times in the past just to
>maintain relatively simple databases. I purchased and
>read microsoft press's review book for the 70-229 exam,
>and there are many things on the exam (which I failed by
3
>questions) which are not covered in the book. Could
>anyone shed some light on the following:
>
>- What should be used when querries to a database need
to
>merge multiple tables with joins: a view, a stored
>procedure, or a user defined function?
>
>- If some stored procedures (but not all) are working
more
>slowly than before, what should be done to improve their
>performance: call UPDATE STATISTICS or use DBCC REINDEX
>with the tables called in the slow stored procedures.
>
>- Can someone give me an overview of the major
differences
>between stored procedures and user defined functions,
>besides the fact that a stored procedure can't be used
>within a SELECT query clause? what are the advantages
of
>a user defined function?
>
>- Does the order of tables in a join statement affect
the
>optimizer's choice of an execution plan?
>
>- If we have a table with a great deal of rows (2
million
>or more), what is the best solution to improve
performance
>with parralel IO access: add indexes on the table, or
>split the table horizontally and use a view to access
all
>of the data?
>
> Thank you for the help.
>
>Session
>
>
>.
>

some guidelines that I used when doing the 70-229 - second question - I am pretty sure you would use Update Statistics as some stored procedures are running slowly which indicates that the execution plan is now out of sync and needs to be refreshed (normally happens when a table has a large volume of updates/inserts/deletes etc) - i don't think you would dbcc reindex anyway as you would now use dbcc indexdefrag (i think thats what it is),

third question - I would say horizontally partition the data and place in views (as the question states with parallel I/O access), if you place an index then the system still has to go through 2 million rows but through an index scan instead, far quicker to parallel process 2000 rows at a time simulataneously which is what partitioning doe

ht

Mark