![]() |
|
|
|
#1 |
|
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 |
|
|
|
|
#2 |
|
Posts: n/a
|
"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 |
|
|
|
#3 |
|
Posts: n/a
|
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 |
|
|
|
#4 |
|
Posts: n/a
|
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=?= |
|
![]() |
| Thread Tools | Search this Thread |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| 70-536 questions and time limit for exam? type of questions? | gravz84 | MCTS | 2 | 11-22-2007 07:57 PM |
| 70-536 questions and time limit for exam? type of questions? | gravz84 | MCTS | 0 | 11-13-2007 05:44 PM |
| 70-536 Details | Gary Gallagher | MCTS | 10 | 06-30-2007 01:08 PM |
| General Questions about the exam | Anne | A+ Certification | 7 | 04-27-2004 11:48 AM |
| Re: Server+ exam afterthoughts | Rick Blythin | A+ Certification | 1 | 07-31-2003 10:54 PM |