Pass Your Microsoft 70-451 Exam Easy!

Get 100% Real Exam Questions, Accurate & Verified Answers By IT Experts

Fast Updates & Instant Download!

Certification Exam: 70-451 (PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008)

Download Free 70-451 Exam Questions

Exam 70-451 - PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
Size: 2.26 MB
Posted Date: Wednesday, August 29, 2012
# of downloads: 29
Free Download:
  Download Free 70-451 Exam Questions
Exam
70-451 - PRO: Designing Database Solutions and Data Access Using Microsoft SQL Server 2008
Size
2.26 MB
Posted Date
Wednesday, August 29, 2012
# of downloads
29
Comments
* The most recent comment are at the top
  • omar
  • Mexico
  • Feb 22, 2013

I passed exam this thursday, all questions come from MAXBOXX dumps

  • Feb 22, 2013
  • ama
  • United States
  • Feb 15, 2013

I passed exam this Monday 838/1000. 49 out of 50 questions are from the two maxboxx dumps. As long as you have both dumps, you should not have problem to pass the exam. By the way, the answers posted here are not totally correct since I only got 838. Anyway, thanks everybody's help here.

  • Feb 15, 2013
  • filezdown
  • United States
  • Feb 15, 2013

congrats, Adam. all the questions from these uploads?

  • Feb 15, 2013
  • Adam
  • Ireland
  • Feb 14, 2013

passed today with 723. XML questions may be worth studying in more detail

  • Feb 14, 2013
  • filezdown
  • United States
  • Feb 14, 2013

@Salid from United States - Q19: D - this is correct. not only "heterogeneous", but search by keyword.. I'm really curious to see how can you search by keyword a varbinary (max)..

  • Feb 14, 2013
  • Adam
  • Ireland
  • Feb 14, 2013

Dennis Questions - Q24 - Changed to A (sp_preparedocument) as the exists() and query() don't return tabular results sets either.

  • Feb 14, 2013
  • NAB
  • United Kingdom
  • Feb 13, 2013

Thanks Adam (maxbox first https://www.dropbox.com/s/biu14aqlthng8dm/70-451%20Dennis.zip) - good stuff
BUT - Q24 - surely cant be D - FOR XML PATH - this would turn relational data into xml, but question requirement is to turn XML Col to 'tabular result set. Can anyone please xplain ?'

  • Feb 13, 2013
  • Adam
  • Ireland
  • Feb 13, 2013

Thanks filezdown.

Updated screenshots:
https://www.dropbox.com/s/biu14aqlthng8dm/70-451%20Dennis.zip

  • Feb 13, 2013
  • filezdown
  • United States
  • Feb 13, 2013

Adam from Ireland: https://www.dropbox.com/s/biu14aqlthng8dm/70-451%20Dennis.zip - as far as I can see, answer for Q2 should be A; you need to define a RANGE RIGHT, and, because you need values from 1 January 2008 to date, you need to specify that as well, because it's the lowest value in the last range

  • Feb 13, 2013
  • filezdown
  • United States
  • Feb 13, 2013

Q2, first maxbox tests (05 Nov 2012): You simply cannot risk creating a RANGE LEFT on a date data type column. http://msdn.microsoft.com/en-us/library/ms187802.aspx - it clearly states: C. Creating a RANGE RIGHT partition function on a datetime column - so it really is RANGE RIGHT. I hope it helps

  • Feb 13, 2013
  • Adam
  • Ireland
  • Feb 13, 2013

These are my answers to the latest Dennis questions, I've edited the screenshot images to include answers to make it easier to study.

https://www.dropbox.com/s/biu14aqlthng8dm/70-451%20Dennis.zip

1. B
2. D
3. C
4. C
5. A
6. A
7. A
8. B
9. A
10. n/a
11. D
12. D
13. D
14. D
15. B
16. D
17. C
18. C
19. C
20. A
21. B
22. B
23. B
24. D
25. A
26. C
27. C
28. B
29. A
30. B
31. C
32. D
33. C
34. C
35. B
36. D
37. C
38. D
39. A
40. C
41. B
42. B
43. D
44. B
45. B
46. C
47. A
48. D
49. B
50. D

  • Feb 13, 2013
  • Adam
  • Ireland
  • Feb 12, 2013

These are my answers to the latest MAXBOX questions, I've edited the screenshot images to include answers to make it easier to study.

https://www.dropbox.com/s/s15hnzylhljiyp2/70-451.zip

1.c
2.a
3.a
4.a
5.b
6.a
7.b
8 c
9.a
10.d
11 n/a
12 a
13.b
14 n/a
15 d
16.a
17 c
18 c
19 a
20 d
21 d
22 d
23 b
24 b
25 b,c
26 d
27 c
28 c
29 a
30 a
31 n/a
32 n/a
33 a
34 a
35 a
36 d
37 a
38 a
39 d
40 d
41 b
42 b
43 b
44 b
45 b
46 c
47 d
48 a
49 c
50 a
Let me know if you guys think any answers are wrong. Cheers

  • Feb 12, 2013
  • Salid
  • United States
  • Feb 12, 2013

My comments to latest maxboxx upload:
9 - RID doesn't mean heap, like someone mentioned. You can have RID lookup on tables with clustered index as well. Covering index is the answer.
7 - D doesnt make sense, it doubles existing index. B is correct (column order matters)
17 - not an xml expert, but simple test shows xml auto makes most sense. If in doubt try select from some test table using each option.
18 - why c not b? both work with root. Can't see any keyword in the question which would make me choose one over another.
19 - my gut feeling is D. It's bet on "heterogeneus" keyword in the question. Filestream just doesn't feel right in this context.

  • Feb 12, 2013
  • sharafat
  • United States
  • Feb 11, 2013

Congratulation eslam , brother let me know - what you study and their answers please

  • Feb 11, 2013
  • eslam
  • Saudi Arabia
  • Feb 09, 2013

passed

  • Feb 09, 2013
  • NAB
  • United Kingdom
  • Feb 04, 2013

Q 17 (on latest MAXBOXX )- Is a bit ambiguous, but I think that this should be C) FOR XML AUTO rather than b) XML Path. XML AUTO is element-centric with the 'ELEMENTS' option, is well-formed, supports the ROOT directive.
Most importantly, the question says 'the data must contain the parent-table name'
- FOR XML AUTO does this automatically.
What do others think ?

  • Feb 04, 2013
  • res74
  • Canada
  • Feb 03, 2013

I will have one up shortly. Been working all weekend on one and with the answers from NZ there will be one up that includes the latest MaxDrop.

But DO NOT thank me, thank NZ and eslam for their assistance. Great job NZ!

  • Feb 03, 2013
  • sharafat
  • United States
  • Feb 03, 2013

Congrats NZ, let us know your answers please

  • Feb 03, 2013
  • res74
  • Canada
  • Feb 02, 2013

Sorry to hear eslam and congrats nz!
I just bought an ExamKing one that I will be converting to VCE soon

  • Feb 02, 2013
  • eslam
  • Saudi Arabia
  • Feb 02, 2013

congratulation NZ
and i will try again next week

  • Feb 02, 2013
  • nz
  • United Kingdom
  • Feb 02, 2013

i passed with 930. sorry to hear that eslam. Yeah almost all questions are from latest maxxbox. I used my answers posted here with maybe three changes. Question 9 changed to covering index. Another asking reporting from central office while accepting updates from other countries,answer changed to database replication. Another one asking to count employee holidays changed to using let and count.

  • Feb 02, 2013
  • eslam
  • Saudi Arabia
  • Feb 02, 2013

all question come from max box old question but i did not pass may be the answer here is incorrect

  • Feb 02, 2013
  • res74
  • Canada
  • Feb 02, 2013

I'm here too. Just now creating the new MaxBox drop to a VCE based on your answers. I am starting this project this weekend so will hopefully post a new VCE by the end of the weekend for all.

  • Feb 02, 2013
  • nz
  • United Kingdom
  • Feb 01, 2013

Hi eslam, question 9 mentioned RID lookup, so it's talking about a heap, that's why I chose c. Because c will use a key lookup. The question is not very clear. because if the query retrieves lots of continuous rows then clustered index will help IO. But I agree that covering index will reduce IO as well. That's why I am putting a question mark.

  • Feb 01, 2013
  • eslam
  • Saudi Arabia
  • Feb 01, 2013

first of all thanks NZ for your answers that help me more.
the question that may be not accepted it with you no 9 may be answer a

please if you have any advise to me to study any thing else tell me

  • Feb 01, 2013
  • nz
  • United Kingdom
  • Feb 01, 2013

Hi Islam, it seems just us here. Any other questions you don't agree with me?

  • Feb 01, 2013
  • islam
  • Saudi Arabia
  • Feb 01, 2013

Q 10 is D

http://msdn.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx

  • Feb 01, 2013
  • islam
  • Saudi Arabia
  • Feb 01, 2013

any body here advise me to study last upload question from maxbox only or any another issue can i study please replay me because i' ll enter to exam tomorrow

  • Feb 01, 2013
  • nz
  • United Kingdom
  • Feb 01, 2013

i will change 7 to d. I did some experiments.

  • Feb 01, 2013
  • Alex
  • Russian Federation
  • Jan 31, 2013

Hello, guys! Does anyone have comments on nz answers (thanks nz, by the way)? In general most of them seem to be correct however there are some doubts concerning questions with indexes for example..

Could anyone older and more experienced share his knowledge to help us unswer these questions?

  • Jan 31, 2013
  • nz
  • United Kingdom
  • Jan 30, 2013

This is my answer to the latest MAXBOX questions. Will also have the test on 2.2
1.c
Temp table will be rolled back.

2. a

3.a
The estimated execution plan is generated based solely on the statistics

4.a

5.b
6. a
7.c ?
8 c
9.c ?
10?
11 c
http://msdn.microsoft.com/en-us/library/ms175511(v=sql.105).aspx
12 a
Filtered index
13. b
14 n/a
15 d
16 b
Transactional replication: http://msdn.microsoft.com/en-us/library/ms151832(v=sql.105).aspx
17 b
18 c?
19 a
The database will store large amount of data.
Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data.
20. b
Not sure about d.
21 d
22 d
23 b
24 b
25 b,c
26 d
27 c
28 c
Table type parameter will use less code than xml parameter.
Inline table-valued function can achieve the results.
29 a
30 a
31 n/a
32 n/a
33 a
34 a
35 a
36 d
37 a
38 a
39 d
40 b?
41 b
42 b
43 b
44 b
45 a?
46 c
47 d
48 a
49 c
50 a

  • Jan 30, 2013
  • islam
  • Saudi Arabia
  • Jan 30, 2013

dear MAXBOX the upload new question without answer can you upload new
question with answer please because i will enter to exam at 2-2-2013

  • Jan 30, 2013
  • Damir, Croatia
  • Croatia
  • Jan 30, 2013

thanx MAXBOX !!!
all questions was from this dump

i pass today with score 723, for 10 questions i did not find a answer.

  • Jan 30, 2013
  • MAXBOX
  • Iran
  • Jan 29, 2013

Upload new question
http://www.4shared.com/folder/lIdLmsgz/070-451.html

  • Jan 29, 2013
  • Omar
  • Mexico
  • Jan 28, 2013

Only seven question from the dump and 7 from MAXBOXX all others new questions

  • Jan 28, 2013
  • Hieron
  • Poland
  • Jan 28, 2013

Dennis's answers are 80 - 90% correct, if you also read his later comments where he changes his view on few of them. No idea which answer is correct for partitions question though, depending who you ask there are different 2 that fit :)

  • Jan 28, 2013
  • res74
  • Canada
  • Jan 26, 2013

Can someone verify one of these peoples answers for the MaxDrop File? I want to convert it to a VCE but I want to make sure which person had the right answers. If someone would do that, I'll convert the MaxDrop to a VCE for everyone else to use. Thanks!

  • Jan 26, 2013
  • Brian
  • United States
  • Jan 24, 2013

Use Maxxbox's questions, those will give you the base you need to pass this exam successfully. You need to study the questions and the answers and why each answer is what it is.

  • Jan 24, 2013
  • Unknown
  • United States
  • Jan 23, 2013

Are the 152 questions posted here valid or is it just Maxboxx's questions

  • Jan 23, 2013
  • Ishana
  • India
  • Jan 23, 2013

ya goutham I too have the same doubt. Plz clarify?

  • Jan 23, 2013
  • Brian
  • United States
  • Jan 22, 2013

Just took the exam and passed with a 800, thanks Maxboxx, Dennis and everyone else!!

  • Jan 22, 2013
  • Unknown
  • United States
  • Jan 21, 2013

As far as I know, the Maxboxx dump is still valid. I am taking my exam tomorrow, and I hope to pass it based on my studies an the maxboxx dump

  • Jan 21, 2013
  • res74
  • Canada
  • Jan 20, 2013

Is the MaxDrop still valid? Taking Exam in a few days and want to make sure.

Thanks!

  • Jan 20, 2013
  • bigdoo
  • Taiwan
  • Jan 19, 2013

Passed today.
Thanks to Maxboxx and Dennis

  • Jan 19, 2013
  • Dennis - From the Philipppines
  • Philippines
  • Jan 12, 2013

For All, I have re-uploaded the file in this site <http://www.filedropper.com/70-451>.

  • Jan 12, 2013
  • Alex
  • Russian Federation
  • Jan 11, 2013

Hi, guys!
As the link by maxbox does not work people here are unable to find it anywhere else. I'm sure somebody has definitely downloaded that file before the link is not actual, please share it again. It will be great appreciated!

  • Jan 11, 2013
  • GuFra
  • Belgium
  • Jan 07, 2013

Hi all,

Does anyone have a new link for maxbox ?
The link provided (http://maxbox.maxlc.com/Exams/70-451) doesn't work anymore.

Thanks,

  • Jan 07, 2013
  • Rukthaz
  • United States
  • Jan 04, 2013

I can't seem to get the Maxboxx link to work. Does anyone have a mirror?

  • Jan 04, 2013
  • Stan
  • Canada
  • Dec 31, 2012

Just took the exam today and passed as well. The questions haven't changed!! Thanks to Maxboxx for the pdf and Dennis for the answers.

  • Dec 31, 2012
  • cym
  • Malaysia
  • Dec 28, 2012

Take the exam today and passed :-)
Thanks for the Maxbox pdf.. almost all questions from there

  • Dec 28, 2012
  • mcitp
  • Ukraine
  • Dec 24, 2012

To Angel.
I agree that there is no right answers at all. Moreover question contains mistake ;)
Because phrase "before 1 Jun 2005" means that there should be boundary 20050101 For right or 20041231 for Left partition function ;)

About http://msdn.microsoft.com/en-us/library/ms187802.aspx Be attentive

because explicit NULL boundary with RIGHT leads to
1. one partitions will be always empty
2. just second partition will hold NULL values.

The most likely answer is D.

  • Dec 24, 2012
  • Angel
  • United States
  • Dec 19, 2012

For the question 2, I think 20051231 in the answers should be changed to 20041231. Otherwise, none of them is correct. If it's 20041231, I think answer D is correct which seperate rows in 4 partitions.
1. Close_date is null
2. Close_date<=20041231
3. 20041231<Close_date<=20071231
4. 20071231<Close_date

Any comments?

  • Dec 19, 2012
  • Prosp
  • Sweden
  • Dec 18, 2012

Took the test today, failed, after studying this vce and passing it with +95% a couple of times (didn't recognize any of the questions on the real test). Then I found the "maxbox PDF" below.. I got about 90% of these questions..should have found this link earlier.. :) Are there any more questions/pdf with this quality for my next try?

  • Dec 18, 2012
  • codebaron
  • United States
  • Dec 15, 2012

Hi Shachar,

thanks for posting, please upload your pdf to fileswap.com or filedropper.com and then post the link they give you for your file here. It is free without registration to post to those sites. You won't even have to join. Congrats on passing the test.

  • Dec 15, 2012
  • Hawkeye
  • Philippines
  • Dec 14, 2012

@shachar: You can upload the pdf files in a site and then you can just paste here the url to be able to download.

  • Dec 14, 2012
  • Damir
  • Croatia
  • Dec 12, 2012

not valid dump in Croatia !! i fell ..

  • Dec 12, 2012
  • I am jack
  • United Kingdom
  • Dec 11, 2012

I passed last week, all the questions where in the PDFs posted

  • Dec 11, 2012
  • shachar
  • United States
  • Dec 11, 2012

I learned from maxbox question and passed!!!!
about 50%-60% of them were in my test thanks maxbox!!!!!!!!
I have another dumb of question and answers that because of them and maxbox question I passed it is in a pdf file. I'm sure that if you study from those to dumbs you will pass the test.

can anybody tell me how can i load them over here?

  • Dec 11, 2012
  • codebaron
  • United States
  • Dec 10, 2012

Hi IH,
Since two others thought it might be c I didn't work this question all the way through. I looked at it again and I think I have worked out the logic. Using the page referenced by Dennis I have changed my answer to A (this was my original answer when I first read the question, always go with that first answer!) What do you think?

  • Dec 10, 2012
  • IH
  • Canada
  • Dec 04, 2012

Hi codebaron,

I dopn't think answer C would be correct. If we choose answer C:

RANGE LEFT FOR VALUES ('20051231', '20071231', '2008101');

We will have the following partitions:
1. Any claims closed before and up to December 31, 2005
2. Claims closed from January 1, 2006 up to December 31, 2007
3. Claims closed on January 1, 2008
4. Claims closed on and after January 2, 2008

This is not what the question requirements are.

  • Dec 04, 2012
  • Examer
  • Russian Federation
  • Dec 04, 2012

Codebaron,
OK, now it's clear. Thank you.

  • Dec 04, 2012
  • codebaron
  • United States
  • Dec 04, 2012

Hi Examer,

This question tries to confuse you by using the date of 1-1-2005 in the question and then using 20051231 in the answers. If you look on msdn.microsoft.com/en-us/library/ms187802.aspx, it explains LEFT partition with this code:

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);

If you use the date of 20051231 as the first partition you CANNOT use range right because range RIGHT is everything less than BUT NOT EQUAL TO that date. We need every date before 20051231 and equal to that date. In other words: col1 <= 1

If partition 1 cannot use range right then the rest of it cannot use range right.

So in the test question the answer must be C. (the question tells you there are no nulls)

  • Dec 04, 2012
  • Examer
  • Russian Federation
  • Dec 02, 2012

Someone please explain the answer on question #2.

Question states: "Claims closed before January 1, 2005"
Answers shows: ".. by the values 20051231" (which is NOT January 1 2005, but December 31 2005). WTF?

I would created RANGE RIGHT partition funciton by using the values 20050101, 20071231, 20080101.

comments are most welcome.

  • Dec 02, 2012
  • Peter - Philippines
  • Philippines
  • Dec 02, 2012

@Dennis: Thank you very much for the info! Soon I will take this exam too. :)

  • Dec 02, 2012
  • Dennis - From the Philipppines
  • New Zealand
  • Dec 02, 2012

@Peter: I used Maxxboxx's pdf from this link:
http://maxbox.maxlc.com/Exams/70-451/
This is one of the 5 actual exams batches that you could get, If you look at the possible answers for each question and research why it is correct or incorrect then I believe you will be ready.

Study the Table Hints ( http://msdn.microsoft.com/en-us/library/ms187373%28v=sql.105%29.aspx) this is 30-40% of the exam.

Also study the Date, Time, DateTime2 and DateTimeofset data types.
I had 4 questions around these data types.

Also a few questions on Service Broker and replication

Hope this helps.

  • Dec 02, 2012
  • nz
  • United Kingdom
  • Nov 28, 2012

Congratulations Dennis. Thank all for your inputs, I changed several of my answers now. Haven't took the exam, maybe in next one or two month.

  • Nov 28, 2012
  • Peter - Philippines
  • United States
  • Nov 28, 2012

@Dennis: Congrats! Could you provide some questions/answers that was shown in the exam or the dump you have used for this exam.

  • Nov 28, 2012
  • Dennis - From the Philipppines
  • New Zealand
  • Nov 26, 2012

Hi All,

First of all I would like to thank everyone that posted their answers and to Maxboxx for posting the questions.
I passed the exam today :). There were only 10 from MaxBoxx's that appeared in the exam but it is a good document to learn what the questions will be like..

Cheers!

  • Nov 26, 2012
  • Dennis - From the Philipppines
  • New Zealand
  • Nov 26, 2012

Hi I think Ne Lo is correct,

I don't believe that Truncate table suites the scenario because Truncate table deletes all the rows in the table. The scenario is to delete 10% of the table.

using the While Loop and Delete Top N

DECLARE @hasItemsToDelete bit
SET @hasItemsToDelete = 1
WHILE (@hasItemsToDelete ) = 1
BEGIN

IF (
(SELECT COUNT(*) FROM TABLE1 WHERE [CONDITION]) > 0
OR
(SELECT COUNT(*) FROM TABLE2 WHERE [CONDITION]) > 0
OR
(SELECT COUNT(*) FROM TABLE3 WHERE [CONDITION]) > 0
OR
(SELECT COUNT(*) FROM TABLE4 WHERE [CONDITION]) > 0
OR
(SELECT COUNT(*) FROM TABLE5 WHERE [CONDITION]) > 0
OR
..
)
BEGIN
SET @hasItemsToDelete = 1
END
ELSE
BEGIN
SET @hasItemsToDelete = 0
END

IF (@hasItemsToDelete = 1)
BEGIN
BEGIN TRY
BEGIN TRAN
DELETE TOP 1000 from table1 where [condition]
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH

BEGIN TRY
BEGIN TRAN
DELETE TOP 1000 from table2 where [condition]
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
... (so on so forth)
END
END

  • Nov 26, 2012
  • I am jack
  • United Kingdom
  • Nov 25, 2012

28 is B table variable - http://blog.sqlauthority.com/2009/12/28/sql-server-difference-temp-table-and-table-variable-effect-of-transaction/
29 cannot be B - multiple truncate statements as truncate empties the table completely

  • Nov 25, 2012
  • Ne Lo
  • United Kingdom
  • Nov 25, 2012

29 . .is A.

  • Nov 25, 2012
  • Dennis - From the Philipppines
  • New Zealand
  • Nov 25, 2012

Hi Guys,

For question 48, I've changed my answer to D

http://technet.microsoft.com/en-us/library/ms178106%28v=sql.105%29.aspx

EXECUTE AS CALLER will fail - because in the question they stated that the users will only have execute permissions to the stored procedure, which means they do not have permission to the sales table inside the stored proc.

EXECUTE AS OWNER -- Will work if the person creating the procedure has select permission on the sales table but it is not stated that the person creating the procedure has access

SETUSER - according to this link http://msdn.microsoft.com/en-us/library/ms188315%28v=sql.105%29.aspx SETUSER is restricted to members of the sysadmin fixed server role or db_owner fixed database role. Which means this will not work.

Based on this the most likely answer is EXECUTE AS OWNER with the assumption that the person creating the sp has select pemission on the sales table

Cheers!

  • Nov 25, 2012
  • Dennis - From the Philipppines
  • New Zealand
  • Nov 25, 2012

Hi Ramona,

can you please tell us what your answers were for numbers 24, 25, 28, 29,44 and 46..

Thanks in advance..

Cheers

  • Nov 25, 2012
  • Dennis - From the Philipppines
  • New Zealand
  • Nov 25, 2012

Hi Guy,

I've written a simple script to test question 46.

declare @table table (s varchar(30), val int )
insert into @table(s,val) values ('d1',1)
insert into @table(s,val) values ('d2',3)
insert into @table(s,val) values ('d3',4)
insert into @table(s,val) values ('d4',1)
insert into @table(s,val) values ('d5',5)
insert into @table(s,val) values ('d6',4)
insert into @table(s,val) values ('d7',4)
insert into @table(s,val) values ('d8',1)

select top 3 s,val from @table order by val desc
results are
s val
d5 5
d3 4
d6 4

select top 3 with ties s,val from @table order by val desc
s val
d5 5
d6 4
d7 4
d3 4

for question 28
try the following script

create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select 'old value #'
insert into @T select 'old value @'
begin transaction
update #T set s='new value #'
update @T set s='new value @'
rollback transaction
select * from #T
select * from @T

results are #t was not returned because temporary tables are part or included in a transaction while table variable are not

I'm taking the exam 2 days from now.. please wish me luck.. I'm gonna need it :)

to: codebaron you are very welcome..

  • Nov 25, 2012
  • codebaron
  • United States
  • Nov 24, 2012

Here is my result for question 46:

For question 46 I created a database and a table to test the answers. Here is my code-

create database Mycompany

create schema Sales

create table Sales.SalesTotal
(TotalSales money not null,
SalesPersonID int null);

insert Sales.SalesTotal (Totalsales, SalesPersonID)
Values (1000,452),(2000,325),(3000,675),(4000,890),(5000,null),(6352.00,65)

select * from sales.Salestotal

insert Sales.SalesTotal (Totalsales, SalesPersonID)
Values (16336,452),(4350,967)

insert Sales.SalesTotal (Totalsales, SalesPersonID)
Values (16336,478)

select top 3 with ties
SalesPersonID, totalsales
from sales.Salestotal
order by totalsales desc

select top(3)
totalsales as "Top 3 Sales", Salespersonid
from sales.Salestotal
order by totalsales desc

select * from sales.Salestotal

(end code)

As you can see I added two salespersonID's with the same amount of sales. Here is the problem, when I run it both A and C select style statements give the same exact results. Maybe someone could run this and figure out what is wrong with my coding.
Thanks

  • Nov 24, 2012
  • codebaron
  • United States
  • Nov 24, 2012

Hi Everyone,

First off thank you Dennis for the hard work. Of the answers I have verified we differ on 6. I changed my answer for 3 to yours "log shipping". from your link explaining it. Your link for 28 says "There is no universal rule for when and where to use temporary tables or table variables. Try them both and experiment." Not exactly an answer. I think the answer is "Temporary Tables" not "Table variables" as you chose. I still believe my answer for 29 is correct "multiple truncate table statements" even though you have a different answer. I don't have the link where I found the answer though it was probably on MSDN. 44 I believe is "scalar function" from another link I don't have.
Of those of you reading crack the books and web sites and help us verify these answers (especially the ones Dennis has no links for), I don't think we have all the right answers yet.

  • Nov 24, 2012
  • Unknown
  • United States
  • Nov 23, 2012

@Ramona - How many questions were there in the exam? Also,have you referred the answers provided by Dennis?

  • Nov 23, 2012
  • Ramona
  • Kenya
  • Nov 22, 2012

I did the exam again and passed. all the questions were from maxbox screenshots.

  • Nov 22, 2012
  • Dennis - From the Philipppines
  • New Zealand
  • Nov 22, 2012

Questions 26 - 50
I have tried to answer the questions and here are my answers. I verified my answers by looking at websites and blogs
26. C Keep the CompanyName Index, since the CompanyName is the second parameter in the first query and the it is the first and only parameter in the second query
27. C
28. B stackoverflow.com/questions/3878764/table-variable-vs-temp-table-in-sql-server-2008
29. D
30. B blogs.msdn.com/b/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
31. C
32. D
33. C SQL Server Enterprise allows for indexed views
34. C The NewID function creates a unique Identifier that universally unique
35. B msdn.microsoft.com/en-us/library/cc280462%28v=sql.105%29.aspx
msdn.microsoft.com/en-us/library/bb933875%28v=sql.105%29.aspx
msdn.microsoft.com/en-us/library/cc280519%28v=sql.105%29.aspx
36. D msdn.microsoft.com/en-us/library/ms184286%28v=sql.105%29.aspx
37. C msdn.microsoft.com/en-us/library/bb895334.aspx (why not filestream)
38. D msdn.microsoft.com/en-us/library/bb630289%28v=sql.105%29.aspx
39. A msdn.microsoft.com/en-us/library/ms187752%28v=sql.105%29.aspx (time uses 5 bytes)
40. C msdn.microsoft.com/en-us/library/ms187746%28v=sql.105%29.aspx
41. B
42. B www.sqlmag.com/article/sql-server/-using-the-recompile-query-hint-to-solve-parameter-sniffing-problems-94369
43. D
44. C
45. B msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx
46. C
47. A because C req to create 3 index vw, while A req us to create 1 vw
48. C msdn.microsoft.com/en-us/library/ms188354.aspx
Because the person that created the procedure may not have permission to access the objects
49. B -- msdn.microsoft.com/en-us/library/ms188354.aspx
The owner of the sp is the MarketingUser, but the user that created the object has access to the table in the other schema
50. D msdn.microsoft.com/en-us/library/ms178091%28v=sql.105%29.aspx

  • Nov 22, 2012
  • Dennis - From the Philipppines
  • New Zealand
  • Nov 22, 2012

Questions 1 - 25
I have tried to answer the questions and here are my answers. I verified my answers by looking at websites and blogs
1. B msdn.microsoft.com/en-us/library/ms188783%28v=sql.105%29.aspx
2. C msdn.microsoft.com/en-us/library/ms187802.aspx
3. C msdn.microsoft.com/en-us/library/ms187016%28v=sql.105%29.aspx
4. C msdn.microsoft.com/en-us/library/ms188783%28v=sql.105%29.aspx
5. A Row compression is applied to Spatial Indexes , C does not apply to Spatial Data Types
6. A technet.microsoft.com/en-us/library/ms190417%28v=sql.105%29.aspx
7. A msdn.microsoft.com/en-us/library/ms175472%28v=sql.105%29.aspx
8. B
9. A msdn.microsoft.com/en-us/library/ms177480%28v=sql.105%29.aspx
10. create a inner transaction
11. D msdn.microsoft.com/en-us/library/ms173763%28v=sql.105%29.aspx
12. D msdn.microsoft.com/en-us/library/ms173763%28v=sql.105%29.aspx
13. D msdn.microsoft.com/en-us/library/ms173763%28v=sql.105%29.aspx
14. D msdn.microsoft.com/en-us/library/ms187373%28v=sql.105%29.aspx
15. B
16. D msdn.microsoft.com/en-us/library/ms186973%28v=sql.100%29.aspx
17. C
18. C msdn.microsoft.com/en-us/library/bb522893%28v=sql.105%29.aspx
19. C msdn.microsoft.com/en-us/library/ms166066%28v=sql.100%29.aspx
20. A www.sql-server-performance.com/2012/sql-server-2008-r2-fixed-server-roles-pt1/
21. B msdn.microsoft.com/en-us/library/ms189869%28v=sql.105%29.aspx
22. B msdn.microsoft.com/en-us/library/ms190025%28v=sql.105%29.aspx
23. B stackoverflow.com/questions/1890923/xpath-to-fetch-sql-xml-value
24. D programming4.us/database/2576.aspx
25. A sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/
Or C Table Scans, both seems to be the right answer.

  • Nov 22, 2012
  • codebaron
  • United States
  • Nov 17, 2012

I have verified these answers through my schools practice tests, labs and textbook. If anyone else can provide the rest please upload them. Verified answers only please. I have possibles for the rest but haven't verified them.
1. -
2. -
3. B
4. C
5. -
6. -
7. A
8. B
9. A
10. -
11. -
12. D
13. -
14. D
15. D
16. D
17. -
18. C
19. C
20. A
21. -
22. -
23. -
24. -
25. A
26. -
27. C
28. -
29. B
30. B
31. C
32. D
33. -
34. C
35. B
36. D
37. C
38. -
39. A
40. C
41. D
42. -
43. D
44. -
45. B
46. -
47. -
48. D
49. B
50. -

  • Nov 17, 2012
  • Aly
  • Israel
  • Nov 16, 2012

many question from my test i saw in dumb "by Devon" it also 336q

  • Nov 16, 2012
  • shawn
  • United States
  • Nov 16, 2012

I forgot to mantain that it also include the answers to the questions

  • Nov 16, 2012
  • shawn
  • United States
  • Nov 16, 2012

yes on my test there were only 21 question from those 50 but after i failed the test i found a friend who can give me the 70451 dumb which contains 336 question that from them you will be asked in your test i saw there the all 50 question that i had in my test (i wish i had it sooner..)
the only problem is that it costs money 140$ if someone is willing to spilt that amount with me i can mail you the question let me know - idodi999@gmail.com thanks

  • Nov 16, 2012
  • aaa
  • Ukraine
  • Nov 14, 2012

shawn, do you mean that only 21 of these 50 discussed questions were asked during test you haven't passed?

  • Nov 14, 2012
  • shawn
  • United States
  • Nov 14, 2012

hi did the test today only 21 questions were on the test did not pass.. can some one tell me if my answers are correct or nz answers are correct
my email is idodi999@gmail.com i have a way to get the other question but i need someone to tell me what are the right answers contact me through the mail and i will give you the rest of the question

  • Nov 14, 2012
  • shawn
  • United States
  • Nov 12, 2012

Hi about question 10 search for option which is similar to that answer

In order to achieve expected results, Stored procedure needs to have two separate transaction. INSERT and UPDATE statement has to be separate transaction to get the successful COMMIT for INSERT statement, this also ensures consistant data and 0 transaction count.

  • Nov 12, 2012
  • shawn
  • United States
  • Nov 12, 2012

Hi this are my answers tell me what you think
1 C
2 C
3 B
4 D
5 A
6 D
7 A
8 B
9 A
10 Incomplete Options
11 B
12 C
13 B
14 B
15 A
16 D
17 C
18 A
19 C
20 B
21 A
22 B
23 D
24 B
25 C
26 D
27 C
28 C
29 B
30 B
31 D
32 B
33 C
34 B
35 D
36 D
37 C
38 D
39 D
40 A
41 B
42 A
43 D
44 A
45 C
46 A
47 C
48 C
49 A
50 A

  • Nov 12, 2012
  • Ramona
  • Kenya
  • Nov 11, 2012

The questions from Maxxbox are from the exam although the exam I did had a question on heap tables

  • Nov 11, 2012
  • Aly
  • Israel
  • Nov 10, 2012

For nz. In quetions 4 is C, in 37 must be C. Check on http://ru.scribd.com/doc/77476708/70-451-passed

  • Nov 10, 2012
  • qyz
  • Germany
  • Nov 09, 2012

for me the questions seem familiar from my failed trial

  • Nov 09, 2012
  • nz
  • United Kingdom
  • Nov 07, 2012

Hi MAXBOXX, are these real questions? Here is my answers to your test quetions, not sure though. Let's discuss them.
1. B
2.
3.
4.
5. c
6. a
7.a
8.b
9. a
10. ?
11.d
12.d
13.d
14.d
15. b
16. d
17. c
18.b
19.c
20. b
21.a
22. c?
23. b?
24. c?
25. a
26. c
27.c
28.a
29.c
30. b
31. c
32. d
33. c
34. c
35. a
36. d
37. b
38. d
39.a
41. c
41. b
42. b
43. D
44. C
45. B?
46. c
47. a?
48, d?
49. b
50. a

  • Nov 07, 2012
  • MAXBOXX
  • Iran
  • Nov 05, 2012

http://maxbox.maxlc.com/Exams/70-451/
New Question

  • Nov 05, 2012
  • Warning
  • Hungary
  • Sep 17, 2012

Hi! My firend failed on this exam last Friday. Prometric updated thier exam enviroment (new gui) and changed few rules. For example if you leave/finish your exam faster than exam time/2, they will revoke your successfully passed exam. So be aware: you never finish the exam before the half of the time passed.

This exam got totaly renewed/revised questions, only 20-25% was familiar. :(
50 Q, 95 mins, 70% pass limit. No drag and drop, just basic choose the best, but many questions are too similar and hard. Good luck.

  • Sep 17, 2012
  • Umar Memon
  • Australia
  • Aug 31, 2012

coming from exam center. gave exam today on 31st august 2012 in australia , it was totally new exam few vew questions from this exam and all other exams which are available on this website. it was really tough exam i have ever seen. I failed second time after a comprehensive study of cbts and practice tests of approximately 300 questions.

  • Aug 31, 2012

Add Comments

Only Registered Members Can Download VCE Files or View Training Courses

Please fill out your email address below in order to Download VCE files or view Training Courses. Registration is Free and Easy - you simply need to provide an email address.

  • Trusted By 1.2M IT Certification Candidates Every Month
  • VCE Files Simulate Real Exam Environment
  • Instant Download After Registration.
Please provide a correct e-mail address
A confirmation link will be sent to this email address to verify your login.
Already Member? Click Here to Login

Log into your ExamCollection Account

Please Log In to download VCE file or view Training Course

Please provide a correct E-mail address

Please provide your Password (min. 6 characters)

Only registered Examcollection.com members can download vce files or view training courses.

Registration is free and easy - just provide your E-mail address. Click Here to Register

Autumn Sale: 20% OFF!

ExamCollection Premium

ExamCollection Premium Files

Get Unlimited Access to all ExamCollection's PREMIUM files!

  • ExamCollection Certified Safe Files
  • Guaranteed to have ACTUAL Exam Questions
  • Up-to-Date Exam Study Material - Verified by Experts
  • Instant Downloads
Enter Your Email Address to Receive Your 20% OFF Discount Code
A Confirmation Link will be sent to this email address to verify your login
We value your privacy. We will not rent or sell your email address

Autumn Sale: 20% OFF!

Use Discount Code:

EXAM2020

A confirmation link was sent to your e-mail.
Please check your mailbox for a message from support@examcollection.com and follow the directions.

Next

Download Free Demo of VCE Exam Simulator

Experience Avanset VCE Exam Simulator for yourself.

Simply submit your e-mail address below to get started with our interactive software demo of your free trial.

Free Demo Limits: In the demo version you will be able to access only first 5 questions from exam.