70-229 exam related questions

Discussion in 'MCSD' started by Session, Apr 29, 2004.

  1. Session

    Session Guest

    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, Apr 29, 2004
    #1
    1. Advertising

  2. "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
    Brad Williams, Apr 29, 2004
    #2
    1. Advertising

  3. Session

    Yorkie Guest

    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
    >
    >
    >.
    >
    Yorkie, Apr 29, 2004
    #3
  4. 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
    =?Utf-8?B?V2VzdHk=?=, May 1, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. mcaissie

    PIX static + dns related questions

    mcaissie, Mar 1, 2005, in forum: Cisco
    Replies:
    2
    Views:
    997
    mcaissie
    Mar 1, 2005
  2. Tina
    Replies:
    1
    Views:
    493
  3. Sir Readalot

    Exam 70-229 or Exam 70-300

    Sir Readalot, Apr 28, 2004, in forum: MCSD
    Replies:
    1
    Views:
    381
    Vipul Patel
    Apr 29, 2004
  4. Joe Boland
    Replies:
    5
    Views:
    502
    Extech
    Feb 14, 2005
  5. Lok-man LEONG

    The exam question for exam 70-229

    Lok-man LEONG, Oct 28, 2006, in forum: MCAD
    Replies:
    1
    Views:
    308
    LarryWestMCSD
    Oct 30, 2006
Loading...

Share This Page