Pass Your Microsoft 70-451 Exam Easy!

I passed with almost top score, dump verified.

I did the exam on 20.07.2011 and got 935 point mark. There were 4 question that wasn't in this dump.

Tks very much for those who had prepared this dump.

My contribution to this 100% valid dump:
1) Multiple servers, one of them a production server. Need a solution for moving data on the production server, including:
- Insert rows;
- Update rows;
- Determine duplicate rows;
- Generates an audit file of whole process.
Answers:
- MERGE with OUTPUT clause
- MERGE with ? clause
- SERIALIZABLE isolation level
- READUNCOMMITTED isolation level

2) Third party application with no rights on the database. You need a solution to mitigate performance issues.
Answers:
- Clustered index
- ?
- ?
- Plan guides

3) XML data in a table field, containing: Author name, Book titile, Book description,.. (XMLData1).
You need a solution to extract Author name and Book description.
Answers:
- XMLData1.Query();
- XMLData1.Exist();
- ?
- ?

Note: Sorry for not remembering all options. Good luck to all !

@mm: feel free to post any new question that you remember !

I passed with 800+, 5 new questions, still valid

Hi,
I passed it yesterday (07/05/2011) in France with 850/1000.
There was about 10-15 new questions on the 50 questions, but not really hard!

@f00ker

When you upload your new Dump? I'm writting on Friday.
Can you please do it before Friday.

Thanks

@eak @someone, glad u pass, can you share new questions?

pass 914/1000

7 new questions

I pass this exam today with score 860 (1 round for selecting and 1 round for reviewing). I saw about 10-20% of question is not exist in this exam answer.

PS. I think this exam answer does not acculate. I should pass with 900+ if this answer is 100% corrent.

any new questions to add before I upload the new dump?

@Alejandro Thanks! I was able to use several of those images and I am uploading a new dump to include yours and the other questions commented below.

@Blade972
@wissam
I had also your questions 1 and 2. Same answers for me and I confirm that option was only FILESTREAM and not Varbinary with filestream in question 2.

I remember another question not exact sentence:
You have 2 office one in NY and one in Tokyo. Main db is in NY. U must permit Tokyo sales manager to produce local reports. Reduce WAN traffic.
- implement Failover Clustering
- implement replication with snapshot
- .. something with ..cluster index (i don't remember)
- I don't remember


I had this questions too. Nice questions, I wasn't ready ;(( I remember my answer - "use distribuded view" but a'm not shure.
Also I think replication with snapshot use a lot of traffic.

If some one recognize the answer or the question and can complete it thnkz this help others http://www.megaupload.com/?d=04VHGTQ0

I passed this exam yesterday 8xx.. i took some pictures of some questions that I saw different from the dump .. i will post it soon

The answer to number 4 from SomeBody is with(nowait)
NOWAIT
Instructs the Database Engine to return a message as soon as a lock is encountered on the table. NOWAIT is equivalent to specifying SET LOCK_TIMEOUT 0 for a specific table.

@wissam
I had also your questions 1 and 2. Same answers for me and I confirm that option was only FILESTREAM and not Varbinary with filestream in question 2.

I remember another question not exact sentence:
You have 2 office one in NY and one in Tokyo. Main db is in NY. U must permit Tokyo sales manager to produce local reports. Reduce WAN traffic.
- implement Failover Clustering
- implement replication with snapshot
- .. something with ..cluster index (i don't remember)
- I don't remember

I answer replication with snapshot.

@squid
you are right but there is no option for varbinary(max) filestream since i know that you should select this option so i think may there is an error in the question!!!!!

Wissam - my congratulations!

>2-you have to store windows media file (WMV) that has size greater that 2GB.
-image
-filestream.
-varbinary(max)
-? ( i do not remember it)

varbinary (max) filestream - correct answer. Only data stored as filestream can be over 2GB.

For first qestions - exactly - NEW() ;)

thank you all,
i have passed today with 8xx and 6-7 new questions
The questions that i have remember:
1-you have to design a new database, the tables in the database should be replicated to three offices and the database tables should have the following requirements : - a unique row should be identified among all the rows
- the row should be unique among the entire organization.
Options:
-add a new column that have the identity property.
-add a new column that have the hierarchyid datatype.
-add a new column with a uniqueidentifier datatype and put the default to the getdate().
-add a new column with a uniqueidentifier datatype and put the default to the NEWID().

2-you have to store windows media file (WMV) that has size greater that 2GB.
-image
-filestream.
-varbinary(max)
-? ( i do not remember it)

When i remember the other new question i will post them.
good luck

@wissam
Sorry I confused book names, here is what I meant:

Pro SQL Server 2008 Relational Database Design and Implementation

written by: Louis Davidson

It is quite easy to find and download it.

Passed today with 9xx. About 10-15 new questions but enaugh simple even for me. Many of that are questions similar to that in dump an I had all 6 or 7 new question posted in this forum. I think I made about 3-4 stupid mistakes.
Today I try to writedown new question I remember. Stay tuned
Cheers

@mahmoud,
good luck brother. R u going on this saturday that is 2 July or next saturday? If you are appearing on this SAT then do post you new question here in this thread. Or let us know how many questions were out of this VCE and thread.

Just in case you dont know .the easy way to count new questions is simply mark them all during exam and at the end count that :). Then let us know later.

I doubt paper may change from next month, but i'm not sure. As many exams have changed within last 2 weeks.
My best wishes are with you man.

@Blade972,

Thanks brother. Interestingly, it happned to almost all the threads. Even on other threads new files have been removed which were uploaded yesterday morning. Either there is an issue with site or whatever.

@Lara, I'm glad my answers are right :). Thats why i got good marks.
I guess people should not start new thread rather everyone should add new questions to this thread. It would be very helpfull for others.

Amazingly, I voted this thread as well and it was ranked 8. Now its again back to 7. beats me :(.

i will take the exam 70-451 next suterday
plz inform me if new question appear

Hi Rado, this morning I opened this page and it still open in my browser so now I can report here you r2 new question (I saved all html.. he he:) so I have also all vanished conversations if u want ask to me)
This are the 2 question you report
1. Three columns in a table Sales Duty, Freight, Customs. You need to add new column to table which would show Sales+Freight+Customs.
The solution must not impact the storage space. What type of column should be added:
a. Persisted Computed column
b. Computed Column
c. Numeric data type
d. Money data type

2. In a procedure, a SELECT statement is used which retrieves a sample data set from a table. The query takes some time to show data.
What type of isolation should be used. Query should return results fast. Some inconsistent data may appear.
a. Repeatable Read
b. Read Uncommited
c. ReadCommitted
d. ReadCommitted Snapshot

Inconsistent - leads Read Uncommitted(includes dirty read)
but ReadCommitted Snapshot last committed consistent data.
My Ans : Read Uncommitted

i'm looking at this way;
In the question query need to be run fast and can contain inconsistent data.
that means when we are running query does not wait for any lock (any tran might put lock before we run)

if we run our query under Repeatable isolation still
before run our query any other tran might put lock.we might wait.
Only other tran can't update after we run the query.

In this scenario we need to run our query fast i think we do not need to wait any lock to be released.
My ans is Read Uncommitted(includes dirty read) or ReadCommitted Snapshot (last committed data).

Hey wissam,

I posted my comments yesterday..now i cant find it here.What happened to that? any idea?

Wissam:
Don't spend your time - training kit (book) for this exam does not exist !

I'm sorry i will be at work 27.06 ;)

Hi SomeBody
i want to ask you a question about what you say that:
-you should study cbtnuggets videos so i study them.
but what about Microsoft 70-451 PRO Designing Database Solutions and Data Access Using Microsoft SQL Server 2008 book ,i have made research about this book but i did not find it so can you help me ?

your help is highly appreciated.

thank you all
Lockystar , what are the new questions?

squid , what do you mean by 27.08?
your help is highly appreciated.

This dump is valid, got 9xx/1000 today, less than 10 new questions which are not hard to be answered if you have experience about SQL programming.
Good luck to you all guys!

I have remember many questions. 27.08 I will be at work and post some new questions.

PS: sorry for my english. I studying it.

Greetimgs,

I just want to ask squid if there are new questions so what are them if you remember them and what are the answers.

your help is highly appreciated.

I have passed this exam today with 957 score in Ukraine !. There are many questions from this valid dump.

lara - yes you are right, correct answer for 4th question is with(nowait)

with(holdlock) was a correct answer for another question that I had on my exam, but I could not remember it.

Is it 100% valid ? I will go tomorrow..

thank you somebody for your support.

Update statement should be terminated when encounters a shared lock, how to achieve that - Ans with(nowait)

Yes, answers for questions 1-3 are correct.

thank you somebody for your support.
are you sure for your answers for the quesions 1,2 and 3??
And if you remember the other new questions so please post them.
Very thanks

I marked - with(holdlock), but I'm not sure for 100%

Thank you SomeBody but what is the answer of the question number 4?

your help is highly appreciated.

Hi, there was a question not included in this dump. You have a table like this:

SalesmanID | ProductsSold
1 | 2000
2 | 4000
3 | 5000
4 | 5000
5 | 6000
6 | 6000
7 | 9000
8 | 9000

You have to chose a query that will select all SalesmanIDs that have sold an amount of products within the 3 top ProductsSold values. So on my table it should return 3,4,5,6,7,8. To achieve that chose the answer with the TOP WITH TIES clause and ORDER BY. (TOP WITH TIES explained http://www.youdidwhatwithtsql.com/top-with-ties/54)

Hello,

As an uploader I need to leave a comment here. I created this dump during my studies to 70-451. I had my exam last week in Poland and passed it with score over 900 points. 16 out of 50 questions were totally new, but most of them were quite easy. There rest was from this dump.

How to prepare to exam:
- get yourself a CBT Nuggets Exam Pack 70-451 Microsoft SQL Server 2008 Designing Database Solutions And Data Access. These training videos are really informative.
- read Microsoft 70-451 PRO Designing Database Solutions and Data Access Using Microsoft SQL Server 2008. Really great book to start getting better understanding of SQL Server.
- read this dump few times and make sure that you understand each answer
- DON'T buy uCertify preparation kits for 70-451, they are worthless.

Some new questions that I remembered after my exam:
1. You have to protect yourself against SQL injections what should you do:
- parse input for: -- /* */ etc

2. You have two different application sessions, how to share transactions and lock between them to avoid any conflicts
- bound sessions

3. You have to validate an email address using regular expressions and some sophisticated logic, what to do
- use user defined function in CLR assembly

4. Update statement should be terminated when encounters a shared lock, how to achieve that
- with(holdlock)
- sp_who
- sp_lock
- with(nowait)

Good luck on your exams!

Hi SomeBody, how much did you score using this VCE q's since i want to know if it is still or not?

your help is highly appreciated.