Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computer Certification > MCSD > 70-229 exam related questions

Reply
Thread Tools

70-229 exam related questions

 
 
Session
Guest
Posts: n/a
 
      04-29-2004
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


 
Reply With Quote
 
 
 
 
Brad Williams
Guest
Posts: n/a
 
      04-29-2004

"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


 
Reply With Quote
 
 
 
 
Yorkie
Guest
Posts: n/a
 
      04-29-2004
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
>
>
>.
>

 
Reply With Quote
 
=?Utf-8?B?V2VzdHk=?=
Guest
Posts: n/a
 
      05-01-2004
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
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
microsoft.public.certification, microsoft.public.cert.exam.mcsa, microsoft.public.cert.exam.mcad, microsoft.public.cert.exam.mcse, microsoft.public.cert.exam.mcsd loyola MCSE 4 11-15-2006 02:40 AM
microsoft.public.certification, microsoft.public.cert.exam.mcsa, microsoft.public.cert.exam.mcad, microsoft.public.cert.exam.mcse, microsoft.public.cert.exam.mcsd loyola Microsoft Certification 3 11-14-2006 05:18 PM
microsoft.public.certification, microsoft.public.cert.exam.mcsa, microsoft.public.cert.exam.mcad, microsoft.public.cert.exam.mcse, microsoft.public.cert.exam.mcsd loyola MCSD 3 11-14-2006 05:18 PM
microsoft.public.certification, microsoft.public.cert.exam.mcsa, microsoft.public.cert.exam.mcad, microsoft.public.cert.exam.mcse, microsoft.public.cert.exam.mcsd loyola MCAD 3 11-14-2006 05:18 PM
microsoft.public.certification, microsoft.public.cert.exam.mcsa, microsoft.public.cert.exam.mcad, microsoft.public.cert.exam.mcse, microsoft.public.cert.exam.mcsd realexxams@yahoo.com Microsoft Certification 0 05-10-2006 02:35 PM



Advertisments
 



1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57