- Which server receives the changes to replicated data?

1. Distributor

2. Article

3. Subscriber

4. Publisher

(I marked 4., but now I would tend to mark 1. I'm still not sure 'bout

it...)

- You want your application to wait for 10 minutes before continuing:

1. WAITFOR TIME "0:10:0"

2. WAITFOR DELAY "0:10:0" <=

3. BREAK AFTER "0:10:0"

4. CONTINUE AFTER "0:10:0"

(I saw this question on at least four braindumps and it was the second

question to answer in my exam :)

- Where to specify to index in the optimizer:

1. SELECT ... FROM table (INDEX = number)

2. SELECT ... FROM table (INDEX = name)

3. SELECT ... FROM table (number)

4. SELECT ... FROM table (name)

- A Publisher writes 0 to many books. A book has exactly one publisher.

>From book to publisher is this:

1. one-to-many

2. many-to-one

3. one-to-one <= ( not very logical at the first look, but for ONE book

there is ONE [exactly one] publisher! From publisher to book, there is a

one-to-many relationship but this was not asked.)

4. many-to-many

- A "SELECT DISTINCT"-statement uses which database:

1. master

2. tempdb <=

3. model

4. current users database

- You have a 'authors' table (author_id PK), which contains all authors

(one row per author) and a 'books' table (one row per book, book_id

PK). You also have a 'bookauthors' table to put books and authors

together. This table also has an 'author_id' and 'book_id' column and

the primary key consists of these two columns. The columns itself are

ordinary ones. The question now is, how to maintain integrity between

authors and books.

1. Make a REFERENCE from bookauthors(author_id) to authors(author_id)

and a

REFERENCE from bookauthors(book_id) to books(book_id). <=

2. Make a REFERENCE from authors(author_id) to bookauthors(author_id)

and a

REFERENCE from books(book_id) to bookauthors(book_id).

3. (can't remember)

4. (can't remember)

I choose the first solution because the 'bookauthors'-table is dependant

of the other two. Also, the primary key of 'bookauthors' consists of

(author_id, book_id) thus preventing duplicate entries. A simple drawing

on a sheet of paper helps a lot on this type of question.

- To compare modified values during trigger-execution, what can you use?

1. INSERTED and DELETED tables, that are only available during execution

of a

trigger. <=

2. Use TEMPDB database.

(I don't remember the other choices)

- How to validate data through a view:

1. Use the WITH CHECK Option when creating the view. <=

2. Use a Trigger bound to the view.

3. (?)

4. Data cannot be validated through a view. (This is completely wrong!

See also

the next question)

- Where is the data stored that is displayed in a view.

1. master db

2. model db

3. users current db

4. Data is not stored in a view. <= (Data is stored in a table, not in a

view; but

you can validate data through a view!)

There was one question with three right answers, some with two right

answers but most questions where only 'choose-one'. There were three or

so question where you

are presented a scenario and a solution and you have to choose how good

the solution is. For example:

Problem: You want to list all book titles that begin with 'computer'.

Solution: SELECT * FROM books WHERE title like '%computer%'

-> 'This is not a solution, although it appears to work.' is the right

answer:

Of course the statement works but you will get all titles with

'computer' in it and not only these that begin with 'computer' (notice

the first percent-sign right before the word - if it is not there, the

solution would be outstanding!). Read those questions very carefully!

There was another one, where the statement was:

SELECT * FROM sales WHERE amount = (SELECT * FROM sales_items)

-> 'This is not a solution and does not work.' Because the nested SELECT

statement may return more than one result and that is not allowed in the

WHERE clause.

some general hints:

=============

Know what happens with the associated objects (indizes, rules, defaults,

stored procedures, triggers) when a table is dropped. There were two

questions on this.

Know about DMO, DAO, DB-Library: what they are good for, how to use

them. That was my weak point in the exam, I only got 33% on this

section.

Know about replication: Where the data starts, how it is distributed and

received, what happens when the replicated data is updated on the

subscriber etc.