Mcsa 70-641 Questions

Front 1

What are the mathematical branches that the relational model is based on?

Back 1

Set theory and predicate logic.

Front 2

What is the difference between T-SQL and SQL?

Back 2

SQL is standard; T-SQL is the dialect of and extension to SQL that Microsoft implements in its RDBMS—SQL Server.

Front 3

1. Name two aspects in which T-SQL deviates from the relational model.
2. Explain how you can address the two items in question 1 and use T-SQL in a relational way.

Back 3

1. A relation has a body with a distinct set of tuples. A table doesn’t have to have a key. T-SQL allows referring to ordinal positions of columns in the ORDER BY clause.
2. Define a key in every table. Refer to attribute names—not their ordinal positions—in the ORDER BY clause.

Front 4

Why are the terms “field” and “record” incorrect when referring to column and row?

Why is the term “NULL value” incorrect?

Back 4

Because “field” and “record” describe physical things, whereas columns and rows are logical elements of a table.

Because NULL isn’t a value; rather, it’s a mark for a missing value.

Front 5

Why is it important to use standard SQL code when possible and know what is standard and what isn’t? (Choose all that apply.)
A. It is not important to code using standard SQL.
B. Standard SQL code is more portable between platforms.
C. Standard SQL code is more efficient.
D. Knowing what standard SQL code is makes your knowledge more portable.

Back 5

B, D

Front 6

Why is it important to use standard SQL code when possible and know what is standard
and what isn’t? (Choose all that apply.)
A. It is not important to code using standard SQL.
B. Standard SQL code is more portable between platforms.
C. Standard SQL code is more efficient.
D. Knowing what standard SQL code is makes your knowledge more portable.

Back 6

D - Because attributes are supposed to be identified by name, ensuring that all attributes have names is relational, and hence not a violation of the relational model.

Front 7

What is the relationship between SQL and T-SQL?
A. T-SQL is the standard language and SQL is the dialect in Microsoft SQL Server.
B. SQL is the standard language and T-SQL is the dialect in Microsoft SQL Server.
C. Both SQL and T-SQL are standard languages.
D. Both SQL and T-SQL are dialects in Microsoft SQL Server.

Back 7

B

Front 8

What is the difference between the WHERE and HAVING clauses?

Back 8

The WHERE clause is evaluated before rows are grouped, and therefore is evaluated per row.

The HAVING clause is evaluated after rows are grouped, and therefore is evaluated per group.

Front 9

Why are you not allowed to refer to a column alias defined by the SELECT clause in the WHERE clause?

Back 9

Because the WHERE clause is logically evaluated in a phase earlier to the one that evaluates the SELECT clause.

Front 10

Why are you not allowed to refer to a column alias defined by the SELECT clause in the same SELECT clause?

Back 10

Because all expressions that appear in the same logical query processing phase are evaluated conceptually at the same point in time.

Front 11

Which of the following correctly represents the logical query processing order of the various query clauses?
A. SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
B. FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
C. FROM > WHERE > GROUP BY > HAVING > ORDER BY > SELECT
D. SELECT > ORDER BY > FROM > WHERE > GROUP BY > HAVING

Back 11

B

Front 12

Which of the following is invalid? (Choose all that apply.)
A. Referring to an attribute that you group by in the WHERE clause
B. Referring to an expression in the GROUP BY clause; for example, GROUP BY YEAR(orderdate)
C. In a grouped query, referring in the SELECT list to an attribute that is not part of the GROUP BY list and not within an aggregate function
D. Referring to an alias defined in the SELECT clause in the HAVING clause

Back 12

CD

Front 13

What is true about the result of a query without an ORDER BY clause?
A. It is relational as long as other relational requirements are met.
B. It cannot have duplicates.
C. The order of the rows in the output is guaranteed to be the same as the insertion order.
D. The order of the rows in the output is guaranteed to be the same as that of the clustered index.

Back 13

A

Front 14

What are the forms of aliasing an attribute in T-SQL?

Back 14

The forms are <expression> AS <alias>, <expression> <alias>, and <alias> = <expression>.

Front 15

What is an irregular identifier?

Back 15

An identifier that does not follow the rules for formatting identifiers; for example, it starts with a digit, has an embedded space, or is a reserved T-SQL keyword.

Front 16

What is the importance of the ability to assign attribute aliases in T-SQL? (Choose all that apply.)
A. The ability to assign attribute aliases is just an aesthetic feature.
B. An expression that is based on a computation results in no attribute name unless you assign one with an alias, and this is not relational.
C. T-SQL requires all result attributes of a query to have names.
D. Using attribute aliases, you can assign your own name to a result attribute if you need it to be different than the source attribute name.

Back 16

BD

Front 17

What are the mandatory clauses in a SELECT query, according to T-SQL?
A. The FROM and SELECT clauses
B. The SELECT and WHERE clauses
C. The SELECT clause
D. The FROM and WHERE clauses

Back 17

C

Front 18

Which of the following practices are considered bad practices? (Choose all that apply.)
A. Aliasing columns by using the AS clause
B. Aliasing tables by using the AS clause
C. Not assigning column aliases when the column is a result of a computation
D. Using * in the SELECT list

Back 18

cd

Front 19

Would you use the type FLOAT to represent a product unit price?

Back 19

No, because FLOAT is an approximate data type and cannot represent all values precisely.

Front 20

What is the difference between NEWID and NEWSEQUENTIALID?

Back 20

The NEWID function generates GUID values in random order, whereas the NEWSEQUENTIAL ID function generates GUIDs that increase in a sequential order.

Front 21

Which function returns the current date and time value as a DATETIME2 type?

Back 21

The SYSDATETIME function.

Front 22

When concatenating character strings, what is the difference between the plus
(+) operator and the CONCAT function?

Back 22

The + operator by default yields a NULL result on NULL input, whereas the CONCAT function treats NULLs as empty strings.

Front 23

Why is it important to use the appropriate type for attributes?
A. Because the type of your attribute enables you to control the formatting of the values
B. Because the type constrains the values to a certain domain of supported values
C. Because the type prevents duplicates
D. Because the type prevents NULLs

Back 23

b

Front 24

Which of the following functions would you consider using to generate surrogate keys?
(Choose all that apply.)
A. NEWID
B. NEWSEQUENTIALID
C. GETDATE
D. CURRENT_TIMESTAMP

Back 24

ab

Front 25

What is the difference between the simple CASE expression and the searched CASE expression?
A. The simple CASE expression is used when the database recovery model is simple, and the searched CASE expression is used when it’s full or bulk logged.
B. The simple CASE expression compares an input expression to multiple possible expressions in the WHEN clauses, and the searched CASE expression uses independent predicates in the WHEN clauses.
C. The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in the WHERE clause.
D. The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in query filters (ON, WHERE, HAVING).

Back 25

b

The difference between the two is that the simple form compares expressions and the searched form uses predicates.

Front 26

What are the performance benefits in using the WHERE filter?

Back 26

You reduce network traffic by filtering in the database server instead of in the client, and you can potentially use indexes to avoid full scans of the tables involved.

Front 27

What is the form of a filter predicate that can rely on index ordering called?

Back 27

A search argument, or SARG, for short.

Front 28

What does the term three-valued logic refer to in T-SQL?
A. The three possible logical result values of a predicate : true, false, and NULL
B. The three possible logical result values of a predicate : true, false, and unknown
C. The three possible logical result values of a predicate : 1, 0, and NULL
D. The three possible logical result values of a predicate : -1, 0, and 1

Back 28

Correct: Three-valued logic refers to true, false, and unknown.

Front 29

Which of the following literals are language-dependent for the DATETIME data type?
(Choose all that apply.)
A. '2012-02-12'
B. '02/12/2012'
C. '12/02/2012'
D. '20120212'

Back 29

abc

Front 30

Which of the following predicates are search arguments? (Choose all that apply.)
A. DAY(orderdate) = 1
B. companyname LIKE 'A%'
C. companyname LIKE '%A%'
D. companyname LIKE '%A'
E. orderdate > = '20120212' AND orderdate < '20120213'

Back 30

be

Front 31

How do you guarantee the order of the rows in the result of a query?

Back 31

the only way to do so is by adding an ORDER BY clause.

Front 32

What is the difference between the result of a query with and one without an ORDER BY clause?

Back 32

Without an ORDER BY clause, the result is relational (from an ordering perspective); with an ORDER BY clause, the result is conceptually what the standard calls a cursor.

Front 33

When a query doesn’t have an ORDER BY clause, what is the order in which the rows are returned?
A. Arbitrary order
B. Primary key order
C. Clustered index order
D. Insertion order

Back 33

A

Front 34

You want result rows to be sorted by orderdate descending, and then by orderid, descending. Which of the following clauses gives you what you want?
A. ORDER BY orderdate, orderid DESC
B. ORDER BY DESC orderdate, DESC orderid
C. ORDER BY orderdate DESC, orderid DESC
D. DESC ORDER BY orderdate, orderid

Back 34

c

Front 35

You want result rows to be sorted by orderdate ascending, and then by orderid, ascending. Which of the following clauses gives you what you want? (Choose all
that apply.)
A. ORDER BY ASC(orderdate, orderid)
B. ORDER BY orderdate, orderid ASC
C. ORDER BY orderdate ASC, orderid ASC
D. ORDER BY orderdate, orderid

Back 35

b,c and d

Front 36

What are the performance benefits in using the WHERE filter?

Back 36

You reduce network traffic by filtering in the database server instead of in the client, and you can potentially use indexes to avoid full scans of the tables involved.

Front 37

What is the form of a filter predicate that can rely on index ordering called?

Back 37

A search argument, or SARG, for short.

Front 38

What does the term three-valued logic refer to in T-SQL?
A. The three possible logical result values of a predicate : true, false, and NULL
B. The three possible logical result values of a predicate : true, false, and unknown
C. The three possible logical result values of a predicate : 1, 0, and NULL
D. The three possible logical result values of a predicate : -1, 0, and 1

Back 38

b

Front 39

Which of the following literals are language-dependent for the DATETIME data type?
(Choose all that apply.)
A. '2012-02-12'
B. '02/12/2012'
C. '12/02/2012'
D. '20120212'

Back 39

abc

Front 40

Which of the following predicates are search arguments? (Choose all that apply.)
A. DAY(orderdate) = 1
B. companyname LIKE 'A%'
C. companyname LIKE '%A%'
D. companyname LIKE '%A'
E. orderdate > = '20120212' AND orderdate < '20120213'

Back 40

be

Front 41

How do you guarantee the order of the rows in the result of a query?

What is the difference between the result of a query with and one without an ORDER BY clause?

Back 41

The only way to do so is by adding an ORDER BY clause.

Without an ORDER BY clause, the result is relational (from an ordering perspective); with an ORDER BY clause, the result is conceptually what the standard calls a cursor.

Front 42

When a query doesn’t have an ORDER BY clause, what is the order in which the rows are returned?
A. Arbitrary order
B. Primary key order
C. Clustered index order
D. Insertion order

Back 42

a

Front 43

You want result rows to be sorted by orderdate descending, and then by orderid, descending. Which of the following clauses gives you what you want?
A. ORDER BY orderdate, orderid DESC
B. ORDER BY DESC orderdate, DESC orderid
C. ORDER BY orderdate DESC, orderid DESC
D. DESC ORDER BY orderdate, orderid

Back 43

c

Front 44

You want result rows to be sorted by orderdate ascending, and then by orderid, ascending. Which of the following clauses gives you what you want? (Choose all
that apply.)
A. ORDER BY ASC(orderdate, orderid)
B. ORDER BY orderdate, orderid ASC
C. ORDER BY orderdate ASC, orderid ASC
D. ORDER BY orderdate, orderid

Back 44

bcd

Front 45

How do you guarantee deterministic results with TOP?

Back 45

By either returning all ties by using the WITH TIES option or by defining unique ordering to break ties.

Front 46

What are the benefits of using OFFSET-FETCH over TOP?

Back 46

OFFSET-FETCH is standard and TOP isn’t; also, OFFSET-FETCH supports a skipping capability that TOP doesn’t.

Front 47

You execute a query with a TOP (3) option. Which of the following options most accurately describes how many rows will be returned?
A. Fewer than three rows
B. Three rows or fewer
C. Three rows
D. Three rows or more
E. More than three rows
F. Fewer than three, three, or more than three rows

Back 47

b

there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are three rows or more without TOP, the query will return three rows.

Front 48

You execute a query with TOP (3) WITH TIES and nonunique ordering. Which of the following options most accurately describes how many rows will be returned?
A. Fewer than three rows
B. Three rows or fewer
C. Three rows
D. Three rows or more
E. More than three rows
F. Fewer than three, three, or more than three rows

Back 48

F. Correct: If there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are at least three rows in the result and no ties with the third, the query will return three rows. If there are more than three rows in the result, as well as ties with the third row, the query will return more than
three rows.

Front 49

Which of the following OFFSET-FETCH options are valid in T-SQL? (Choose all that apply.)
A. SELECT … ORDER BY orderid OFFSET 25 ROWS
B. SELECT … ORDER BY orderid FETCH NEXT 25 ROWS ONLY
C. SELECT … ORDER BY orderid OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY
D. SELECT … <no ORDER BY> OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY

Back 49

ac

Front 50

What is the difference between the old and new syntax for cross joins?

What are the different types of outer joins?

Back 50

The new syntax has the CROSS JOIN keywords between the table names and the old syntax has a comma.

Left, right, and full.

Front 51

What is the difference between the ON clause and the WHERE clause?
A. The ON clause uses two-valued logic and the WHERE clause uses three-valued logic.
B. The ON clause uses three-valued logic and the WHERE clause uses two-valued logic.
C. In outer joins, the ON clause determines filtering and the WHERE clause determines matching.
D. In outer joins, the ON clause determines matching and the WHERE clause determines filtering.

Back 51

d

Front 52

Which keywords can be omitted in the new standard join syntax without changing the meaning of the join? (Choose all that apply.)
A. JOIN
B. CROSS
C. INNER
D. OUTER

Back 52

CD

Front 53

Which syntax is recommended to use for cross joins and inner joins, and why?
A. The syntax with the JOIN keyword because it’s consistent with outer join syntax and is less prone to errors.
B. The syntax with the comma between the table names because it’s consistent with outer join syntax and is less prone to errors.
C. It is recommended to avoid using cross and inner joins.
D. It is recommended to use only lowercase characters and omit default keywords, as in join instead of INNER JOIN because it increases energy consumption

Back 53

a

Front 54

What happens when a scalar subquery returns more than one value?
A. The query fails at run time.
B. The first value is returned.
C. The last value is returned.
D. The result is converted to a NULL.

Back 54

a

Front 55

What are the benefits of using a CTE over derived tables? (Choose all that apply.)
A. CTEs are better performing than derived tables.
B. CTEs don’t nest; the code is more modular, making it easier to follow the logic.
C. Unlike with derived tables, you can refer to multiple instances of the same CTE name, avoiding repetition of code.
D. Unlike derived tables, CTEs can be used by all statements in the session, and not just the statement defining them.

Back 55

b Correct: If you want to refer to one derived table from another, you need to nest them. With CTEs, you separate those by commas, so the code is more modular and
easier to follow.
C. Correct: Because the CTE name is defined before the outer query that uses it, the outer query is allowed to refer to multiple instances of the same CTE name.

d Incorrect: CTEs are visible only in the scope of the statement that defined them.
a Incorrect: CTEs are visible only in the scope of the statement that defined them.

Front 56

What is the difference between the result of T1 CROSS APPLY T2 and T1 CROSS JOIN T2 (the right table expression isn’t correlated to the left)?
A. CROSS APPLY filters only rows where the values of columns with the same name are equal; CROSS JOIN just returns all combinations.
B. If T1 has rows and T2 doesn’t, CROSS APPLY returns an empty set and CROSS JOIN still returns the rows from T1.
C. If T1 has rows and T2 doesn’t, CROSS APPLY still returns the rows from T1 and CROSS join returns an empty set.
D. There is no difference.

Back 56

D

Front 57

Which of the following operators removes duplicates from the result? (Choose all that apply.)
A. UNION
B. UNION ALL
C. INTERSECT
D. EXCEPT

Back 57

abc

Front 58

In which operator does the order of the input queries matter?
A. UNION
B. UNION ALL
C. INTERSECT
D. EXCEPT

Back 58

d

Front 59

Which of the following is the equivalent of <query 1> UNION <query 2> INTERSECT <query 3> EXCEPT <query 4>?
A. (<query 1> UNION <query 2>) INTERSECT (<query 3> EXCEPT <query 4>)
B. <query 1> UNION (<query 2> INTERSECT <query 3>) EXCEPT <query 4>
C. <query 1> UNION <query 2> INTERSECT (<query 3> EXCEPT <query 4>)
D. <query 1> UNION (<query 2> INTERSECT <query 3> EXCEPT <query 4>)

Back 59

B

Front 60

What makes a query a grouped query?

What are the clauses that you can use to define multiple grouping sets in the same query?

Back 60

When you use an aggregate function, a GROUP BY clause, or both.

GROUPING SETS, CUBE, and ROLLUP.

Front 61

What is the restriction that grouped queries impose on your expressions?
A. If the query is a grouped query, you must invoke an aggregate function.
B. If the query has an aggregate function, it must have a GROUP BY clause.
C. The elements in the GROUP BY clause must also be specified in the SELECT clause.
D. If you refer to an element from the queried tables in the HAVING, SELECT, or ORDER BY clauses, it must either appear in the GROUP BY list or be contained by an aggregate function.

Back 61

d

Front 62

What is the purpose of the GROUPING and GROUPING_ID functions? (Choose all that apply.)
A. You can use these functions in the GROUP BY clause to group data.
B. You can use these functions to tell whether a NULL in the result represents a placeholder for an element that is not part of the grouping set or an original NULL from
the table.
C. You can use these functions to uniquely identify the grouping set that the result row is associated with.
D. These functions can be used to sort data based on grouping set association—that is, first detail, and then aggregates.

Back 62

b
c
d

Front 63

What is the difference between the COUNT(*) aggregate function and the COUNT(<expression>) general set function?
A. COUNT(*) counts rows; COUNT(<expression>) counts rows where <expression> is not NULL.
B. COUNT(*) counts columns; COUNT(<expression>) counts rows.
C. COUNT(*) returns a BIGINT; COUNT(<expression>) returns an INT.
D. There’s no difference between the functions.

Back 63

A

Front 64

1. What is the difference between PIVOT and UNPIVOT?
2. What type of language constructs are PIVOT and UNPIVOT implemented as?

Back 64

1. PIVOT rotates data from a state of rows to a state of columns; UNPIVOT rotates the data from columns to rows.
2. PIVOT and UNPIVOT are implemented as table operators.

Front 65

How does the PIVOT operator determine what the grouping element is?
A. It’s the element specified as input to the GROUPING function.
B. It’s determined by elimination—the element(s) from the queried table that were not specified as the spreading or aggregation elements.
C. It’s the element specified in the GROUP BY clause.
D. It’s the primary key.

Back 65

b

Front 66

Which of the following are not allowed in the PIVOT operator’s specification? (Choose all that apply.)
A. Specifying a computation as input to the aggregate function
B. Specifying a computation as the spreading element
C. Specifying a subquery in the IN clause
D. Specifying multiple aggregate functions

Back 66

A. Correct: You cannot specify a computation as input to the aggregate function, rather just a name of a column from the input table.
B. Correct: You cannot specify a computation as the spreading element, rather just a name of a column from the input table.
C. Correct: You cannot specify a subquery in the IN clause, rather just a static list.
D. Correct: You cannot specify multiple aggregate functions, rather just one.

Front 67

What is the data type of the target values column in the result of an UNPIVOT operator?
A. INT
B. NVARCHAR(128)
C. SQL_VARIANT
D. The data type of the source columns that you unpivot

Back 67

d

Front 68

What are the clauses that the different types of window functions support?
What do the delimiters UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING represent?

Back 68

Partitioning, ordering, and framing clauses.
The beginning and end of the partition, respectively

Front 69

What is the default frame window functions use when a window order clause is specified but an explicit window frame clause isn’t? (Choose all that apply.)
A. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
B. ROWS UNBOUNDED PRECEDING
C. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
D. RANGE UNBOUNDED PRECEDING

Back 69

C. Correct: This is the default frame.
D. Correct: This is an abbreviated form of the default frame, having the same meaning.

Front 70

What do the RANK and DENSE_RANK functions compute?
A. The RANK function returns the number of rows that have a lower ordering value (assuming ascending ordering) than the current; the DENSE_RANK function returns the number of distinct ordering values that are lower than the current.
B. The RANK function returns one more than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one more than the number of distinct ordering values that are lower than the current.
C. The RANK function returns one less than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one less than the number of distinct ordering values that are lower than the current.
D. The two functions return the same result unless the ordering is unique.

Back 70

cb

Front 71

Why are window functions allowed only in the SELECT and ORDER BY clauses of a query?
A. Because they are supposed to operate on the underlying query’s result, which is achieved when logical query processing gets to the SELECT phase.
B. Because Microsoft didn’t have time to implement them in other clauses.
C. Because you never need to filter or group data based on the result of window functions.
D. Because in the other clauses, the functions are considered door functions (also known as backdoor functions).

Back 71

a

Front 72

The difference between ROWS and RANGE is actually similar to the difference between ROW_NUMBER and RANK, respectively

Back 72

When the window ordering isn’t unique, ROWS doesn’t include peers, and therefore it isn’t deterministic, whereas RANGE includes peers, and therefore it is deterministic. Also, the ROWS option can be optimized with an efficient in-memory spool; RANGE is optimized with an on-disk spool and therefore is usually slower.

Front 73

Can you store indexes from the same full-text catalog to different filegroups?

Back 73

Yes. A full-text catalog is a virtual object only; full-text indexes are physical objects. You can store each full-text index from the same catalog to a different file group.

Front 74

Which full-text search elements can you use to prevent indexing noisy words? (Choose all that apply.)
A. Stopwords
B. Thesaurus
C. Stemmer
D. Stoplists

Back 74

a d

Front 75

Which database do you have to install in order to enable the Semantic Search feature?
A. msdb
B. distribution
C. semanticsdb
D. tempdb

Back 75

c

Front 76

How can you create synonyms for the words searched?
A. You can edit the thesaurus file.
B. You can create a thesaurus table.
C. You can use the stopwords for synonyms as well.
D. Full-text search does not support synonyms.

Back 76

a

Front 77

1. How do you search for synonyms of a word with the CONTAINS predicate?
2. Which is a more specific predicate, CONTAINS or FREETEXT?

Back 77

1. You have to use the CONTAINS(FTcolumn, ‘FORMSOF(THESAURUS, SearchWord1)’) syntax.
2. You use the CONTAINS predicate for more specific searches.

Front 78

Which of the following is not a part of the CONTAINS predicate?
A. FORMSOF
B. THESAURUS
C. NEAR
D. PROPERTY
E. TEMPORARY

Back 78

e

Front 79

Which form of the proximity term defines the distance and the order?
A. NEAR((SearchWord1, SearchWord2), 5, TRUE)
B. NEAR((SearchWord1, SearchWord2), CLOSE, ORDER)
C. NEAR((SearchWord1, SearchWord2), 5)
D. NEAR(SearchWord1, SearchWord2)

Back 79

A Correct: This proximity term defines both distance and order of searched terms.

Front 80

What can you search for with the CONTAINS predicate? (Choose all that apply.)
A. Inflectional forms of a word
B. Synonyms of a searched word
C. Translations of a word
D. Text in which a search word is close to another search word
E. A prefix of a word or a phrase only

Back 80

Correct Answers: A, B, D, and E
A. Correct: You can search for inflectional forms of a word.
B. Correct: You can search for synonyms of a searched word.
C. Incorrect: Full-text search does not support translations.
D. Correct: You can search for text in which a search word is close to another search word.
E. Correct: You can search for a prefix of a word or a phrase only.

Front 81

How many full-text search and how many semantic search functions are supported by SQL Server?

Back 81

SQL Server supports two full-text search and three semantic search functions

Front 82

Which function can be used to rank documents based on proximity of words?
A. CONTAINSTABLE()
B. FREETEXTTABLE()
C. SEMANTICKEYPHRASETABLE()
D. SEMANTICSIMILARITYTABLE()
E. SEMANTICSIMILARITYDETAILSTABLE()

Back 82

a
You use the CONTAINSTABLE function to rank documents based on proximity of words.

Front 83

Which function can be used to find the document that is most semantically similar to a specified document?
A. CONTAINSTABLE()
B. FREETEXTTABLE()
C. SEMANTICKEYPHRASETABLE()
D. SEMANTICSIMILARITYTABLE()
E. SEMANTICSIMILARITYDETAILSTABLE()

Back 83

d
Correct: You use the SEMANTICSIMILARITYTABLE function to retrieve documents scored by similarity to a specified document.

Front 84

Which function returns a table with key phrases associated with the full-text indexed column?
A. CONTAINSTABLE()
B. FREETEXTTABLE()
C. SEMANTICKEYPHRASETABLE()
D. SEMANTICSIMILARITYTABLE()
E. SEMANTICSIMILARITYDETAILSTABLE()

Back 84

c
Correct: You use the SEMANTICKEYPHRASETABLE function to return key phrases associated with the full-text indexed column.

Front 85

How can you get an XSD schema together with an XML document from your SELECT statement?

Back 85

You should use the XMLSCHEMA directive in the FOR XML clause.

Front 86

OPENXML function

Back 86

provides a rowset over in-memory XML documents by using DOM presentation. Before parsing the DOM, you need to prepare it.
To prepare the DOM presentation of XML, you need to call the system stored procedure sys.sp_xml_preparedocument.
After you shred the document, you must remove the DOM presentation by using the system procedure sys.sp_xml_removedocument.

Front 87

Which FOR XML options are valid? (Choose all that apply.)
A. FOR XML AUTO
B. FOR XML MANUAL
C. FOR XML DOCUMENT
D. FOR XML PATH

Back 87

a
d

Front 88

Which directive of the FOR XML clause should you use to produce element-centric XML?
A. ATTRIBUTES
B. ROOT
C. ELEMENTS
D. XMLSCHEMA

Back 88

c

Front 89

Which FOR XML options can you use to manually format the XML returned? (Choose
all that apply.)
A. FOR XML AUTO
B. FOR XML EXPLICIT
C. FOR XML RAW
D. FOR XML PATH

Back 89

b
d

Front 90

What do you do in the return clause of the FLWOR expressions?
What would be the result of the expression (12, 4, 7) != 7?

Back 90

In the return clause, you format the resulting XML of a query.
The result would be true.

Front 91

Which of the following is not a FLWOR clause?
A. for
B. let
C. where
D. over
E. return

Back 91

d

Front 92

Which node type test can be used to retrieve all nodes of an XML instance?
A. Asterisk (*)
B. comment()
C. node()
D. text()

Back 92

c

Front 93

Which conditional expression is supported in XQuery?
A. IIF
B. if..then..else
C. CASE
D. switch

Back 93

b

Front 94

Which of the following is not an XML data type method?
A. merge()
B. nodes()
C. exist()
D. value()

Back 94

a

Front 95

What kind of XML indexes can you create? (Choose all that apply.)
A. PRIMARY
B. PATH
C. ATTRIBUTE
D. PRINCIPALNODES

Back 95

a
b

Front 96

Which XML data type method do you use to shred XML data to tabular format?
A. modify()
B. nodes()
C. exist()
D. value()

Back 96

b
You use the nodes() method to shred XML data.

Front 97

What types of table compression are available

Back 97

You can use either page or row compression on a table. Page compression includes row compression.

Front 98

1. How does SQL Server enforce uniqueness in both primary key and unique constraints?
2. Can a primary key on one table have the same name as the primary key in another table in the same database?

Back 98

SQL Server uses unique indexes to enforce uniqueness for both primary key and unique constraints.
No, all table constraints must have unique names in a database.

Front 99

Which of the following columns would be appropriate as a surrogate key? (Choose all that apply.)
A. The time (in hundredths of a second) that the row was inserted
B. An automatically increasing integer number
C. The last four digits of a social security number concatenated with the first eight digits of a user's last name
D. A uniqueidentifier (GUID) newly selected from SQL Server at the time the row is inserted

Back 99

BD

Front 100

You want to enforce that a valid supplierid be entered for each productid in the Production.Products table. What is the appropriate constraint to use?
A. A unique constraint
B. A default constraint
C. A foreign key constraint
D. A primary key constrain

Back 100

C. A foreign key constraint

Front 101

What metadata tables give you a list of constraints in a database? (Choose all that apply.)
A. sys.key_constraints
B. sys.indexes
C. sys.default_constraints
D. sys.foreign_keys

Back 101

A. Correct: sys.key_constraints lists all primary key and unique constraints in a
database.
B. Incorrect: sys.indexes does not list constraints.
C. Correct: sys.default_constraints lists the default constraints in a database.
D. Correct: sys.foreign_keys lists all the foreign keys in a database.

Front 102

You are configuring an appropriate transaction isolation level for a session. You want to ensure the following:
*Data read by any statement in the transaction must be transactionally consistent with how the data existed at the start of the transaction
*Other transactions should be able to write data to rows selected by the transaction even when the transaction has yet to commit.
Which of the following T-SQL statements can you use to accomplish this task
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEl READ COMMITED
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET TRANSCATION ISOLATION LEVEL READ UNCOMMITTED

Back 102

You use the SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement when you want to ensure that data read by any statement in a transaction will be transactionally consistent with how that data existed at the start of the transaction. SNAPSHOT transactions do not block other transactions from writing data.

Front 103

You want to rename the Jobseekers view associated with the HumanResources schema so that it is named Candidates.

Which of the following Transact-SQL statements can you use to accomplish this goal?
a.ALTER VIEW [HumanResources].[Jobseekers] Candidates
b.EXEC sp_rename 'HumanResources.Jobseekers', 'Candidates'
c.ALTER VIEW [HumanResources].[Candidates] Jobseekers
d. EXEC sp_rename 'HumanResources.Candidates', 'Jobseekers'

Back 103

b. You should use the EXEC sp_rename 'HumanResources.Jobseekers', 'Candidates' statement to rename the Jobseekers view associated with the HumanResources schema to Candidates.

You should not use the ALTER VIEW [HumanResources].[Jobseekers] Candidates statement. You cannot use the ALTER VIEW statement to change the name of a view.

Front 104

You are creating a number of tables for your company's Hovercraft Rental database. You want to create a table based on the properties of the columns and the data contained in rows output by a particular query. This new table should be automatically populated with the data that is generated by the query.

Which of the following T-SQL statements would you use to accomplish this goal?
a.CREATE TABLE
b.DROP TABLE
c.ALTER TABLE
d.SELECT . . . INTO

Back 104

d

You can use the SELECT . . . INTO statement to use the output from a select query to form the basis of creating a new table. When you do this, the new table's properties inherit the properties of the columns selected using the query.

Front 105

Which of the following statements about designing views is true?
a.You can associate AFTER triggers with views.
b.You can use the COMPUTE clause in a view.
c.You can associate INSTEAD OF triggers with views.
d.You can create a view on a temporary table.

Back 105

b

You can associate INSTEAD OF triggers with views.

You cannot associate AFTER triggers with views.

You cannot create a view on a temporary table.

You cannot use the COMPUTE clause in a view.

Front 106

You want to view the definition of a DML trigger named Hovercraft.RentalTrigger.

Which of the following Transact-SQL statements can you use to accomplish this goal? (Each correct answer presents a complete solution. Choose two.)

a.EXEC sp_rename 'Hovercraft.RentalTrigger'
b.EXEC sp_helptext 'Hovercraft.RentalTrigger'
c.SELECT definition from sys.sql_modules where object_id = OBJECT_ID(N'Hovercraft.RentalTrigger')
d.LECT object_id from sys.sql_modules where object_id=OBJECT_ID(N'Hovercraft.RentalTrigger')

Back 106

bc

Executing the SELECT definition from sys.sql_modules where object_id = OBJECT_ID(N'Hovercraft.RentalTrigger') statement will provide the definition of the Hovercraft.RentalTrigger trigger.

Executing the EXEC sp_helptext 'Hovercraft.RentalTrigger' statement will provide the definition of the Hovercraft.RentalTrigger trigger.

You should not execute the SELECT object_id from sys.sql_modules where object_id=OBJECT_ID(N'Hovercraft.RentalTrigger') statement. This will return the objectID of the trigger but not the trigger definition.

Front 107

Which of the following statements will list the number of BEGIN TRANSACTION statements that have occurred through the current connection without a COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK statement being issued?
a.PRINT @@TRANCOUNT
b.PRINT @@IDENTITY
c.PRINT @@ERROR
d.PRINT @@ROWCOUNT

Back 107

a

You use the @@TRANCOUNT system function to learn the number of BEGIN TRANSACTION statements that have occurred through the current connection without a ROLLBACK or COMMIT statement being issued. Each time you issue a BEGIN TRANSACTION statement, @@TRANCOUNT is incremented by one. Every time you issue a ROLLBACK or COMMIT statement, @@TRANCOUNT decrements by one unless you are rolling back to a savepoint.

You use the @@IDENTITY system function to learn the last-inserted identity value.

You use the @@ROWCOUNT system function to learn the number of rows affected by the most recent statement.

You use the @@ERROR system function to learn the error number for the last Transact-SQL statement executed.

Front 108

You are reviewing the design of a database hosted on SQL Server 2012. You want to move the Exemplar view from the HumanResources schema to the Person schema.

Which of the following Transact-SQL statements can you use to accomplish this goal?
a.ALTER SCHEMA [HumanResources] TRANSFER [Person].[Exemplar]
b.ALTER VIEW [HumanResources] TRANSFER [Person].[Exemplar]
c.ALTER SCHEMA [Person] TRANSFER [HumanResources].[Exemplar]
d.ALTER VIEW [Person] TRANSFER [HumanResources].[Exemplar]

Back 108

c

You should use the ALTER SCHEMA [Person] TRANSFER [HumanResources].[Exemplar] statement. This statement transfers the Exemplar view from the HumanResources schema to the Person schema.

You cannot use the ALTER VIEW statement to move a view to a different schema.

Front 109

You are reviewing the design of a database hosted on SQL Server 2012. You want to move the Exemplar view from the HumanResources schema to the Person schema.

Which of the following Transact-SQL statements can you use to accomplish this goal?
a.ALTER SCHEMA [HumanResources] TRANSFER [Person].[Exemplar]
b.ALTER VIEW [HumanResources] TRANSFER [Person].[Exemplar]
c.ALTER SCHEMA [Person] TRANSFER [HumanResources].[Exemplar]
d.ALTER VIEW [Person] TRANSFER [HumanResources].[Exemplar]

Back 109

You should use the ALTER SCHEMA [Person] TRANSFER [HumanResources].[Exemplar] statement. This statement transfers the Exemplar view from the HumanResources schema to the Person schema.

Front 110

You want to generate a list of integers and their natural logarithms between 1 and 20.

Which of the following Transact-SQL statements can you use to accomplish this task?
a.DECLARE @testvalue float;
SET @testvalue = 1.00;
WHILE @testvalue < 21.00
BEGIN
SELECT @testvalue as 'Value', SQRT(@testvalue) as 'Result';
SET @testvalue = @testvalue +1;
END;
b.DECLARE @testvalue float;
SET @testvalue = 1.00;
WHILE @testvalue < 21.00
BEGIN
SELECT @testvalue as 'Value', LOG(@testvalue) as 'Result';
SET @testvalue = @testvalue +1;
END;
c.DECLARE @testvalue float;
SET @testvalue = 1.00;
WHILE @testvalue < 21.00
BEGIN
SELECT @testvalue as 'Value', EXP(@testvalue) as 'Result';
SET @testvalue = @testvalue +1;
END;
d.DECLARE @testvalue float;
SET @testvalue = 1.00;
WHILE @testvalue < 21.00
BEGIN
SELECT @testvalue as 'Value', SQUARE(@testvalue) as 'Result';
SET @testvalue = @testvalue +1;
END;

Back 110

b

DECLARE @testvalue float;
SET @testvalue = 1.00;
WHILE @testvalue < 21.00
BEGIN
SELECT @testvalue as 'Value', LOG(@testvalue) as 'Result';
SET @testvalue = @testvalue +1;
END;

Front 111

You want to create a stored procedure that returns an employee's email address when partial values for first name and last name are provided as input.

Which of the following Transact-SQL statements can you use to accomplish this task?
a.CREATE PROCEDURE HumanResources.uspEmployeeEmail
@LastName nvarchar(50) = N'%',
@FirstName nvarchar(50) = N'%'
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, EmailAddress
FROM HumanResources.vEmployee
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;
b.CREATE PROCEDURE HumanResources.uspEmployeeEmail
@LastName nvarchar(50) = N'%',
@FirstName nvarchar(50) = N'%'
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, EmailAddress
FROM HumanResources.vEmployee
WHERE FirstName = @FirstName AND LastName = @LastName;
c.CREATE PROCEDURE HumanResources.uspEmployeeEmail
@LastName nvarchar(50) = N'%',
@FirstName nvarchar(50) = N'%'
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, PhoneNumber
FROM HumanResources.vEmployee
WHERE FirstName = @FirstName AND LastName = @LastName;
d.CREATE PROCEDURE HumanResources.uspEmployeeEmail
@LastName nvarchar(50) = N'%',
@FirstName nvarchar(50) = N'%'
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, PhoneNumber
FROM HumanResources.vEmployee
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

Back 111

a. CREATE PROCEDURE HumanResources.uspEmployeeEmail
@LastName nvarchar(50) = N'%',
@FirstName nvarchar(50) = N'%'
AS
SET NOCOUNT ON;
SELECT FirstName, LastName, EmailAddress
FROM HumanResources.vEmployee
WHERE FirstName LIKE @FirstName AND LastName LIKE @LastName;

Front 112

Your database has two tables related to Hovercraft models, named Hovercraft.OldDesign and Hovercraft.NewDesign. You want to produce a list of Hovercraft models from the OldDesign table as long as those Hovercraft models do not have duplicate entries in the NewDesign table.

Which of the following Transact-SQL statements can you use to accomplish this goal?
a.SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign UNION SELECT HovercraftModelID, Name FROM Hovercraft.NewDesign ORDER BY Name
b.SELECT HovercraftModelID, Name FROM Hovercraft.NewDesign EXCEPT SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign ORDER BY Name
c.SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign INTERSECT SELECT HovercraftModelID, Name FROM Hovercraft.NewDesign ORDER BY Name
d.SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign EXCEPT SELECT HovercraftModelID, Name FROM Hovercraft.NewDesign ORDER BY Name
e.SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign UNION ALL SELECT HovercraftModelID, Name FROM Hovercraft.NewDesign ORDER BY Name

Back 112

d

You should use the SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign EXCEPT SELECT HovercraftModelID, Name FROM Hovercraft.NewDesign ORDER BY Name statement. This will produce a list of Hovercraft models from the OldDesign table as long as those Hovercraft models do not have duplicate entries in the NewDesign table.

Front 113

You are optimizing several complex queries. You have determined that you can improve performance by always merging UNION sets.

Which of the following query hints should you use to implement this optimization?
a.CONCAT UNION
b.HASH UNION
c.HASH JOIN
d.MERGE UNION

Back 113

a.The MERGE UNION query hint option specifies that all UNION operations will be performed by merging UNION sets.

The CONCAT UNION query hint option specifies that all UNION operations will be performed by concatenating UNION sets.

The HASH UNION query hint option specifies that all UNION operations will be performed by hashing UNION sets.

The HASH JOIN query hint option specifies that all JOIN operations in the query use HASH JOINs.

Front 114

You create the table Hovercraft.Chassis by executing the following statement:

CREATE TABLE Hovercraft.Chassis
(
HovercraftID uniqueidentifier DEFAULT NEWID() PRIMARY KEY,
hullid INT NOT NULL,
registration nvarchar(8) NOT NULL,
maintenancedate DATE NOT NULL
CONSTRAINT DFT_MyOrders_orderdate DEFAULT (CAST(SYSDATETIME() AS DATE)), homebase NVARCHAR(15) NOT NULL,
fuelcapacity decimal(8,4) NOT NULL
);

Which of the following statements can you use to insert data into this table? (Each correct answer presents a complete solution. Choose two.)
a.INSERT INTO Hovercraft.Chassis (maintenancedate, hullid, registration, fuelcapacity, homebase) VALUES (20120719, 22, N'AAA119', 800.75, N'Hobart')
b.INSERT INTO Hovercraft.Chassis (hullid, registration, maintenancedate, homebase, fuelcapacity) VALUES (25, N'AAA124', 20120728, N'Sydney', 950.50)
c.INSERT INTO Hovercraft.Chassis (hullid, registration, maintenancedate, homebase, fuelcapacity) VALUES (24, N'AAA123', '20120727', N'Melbourne', 900.50)
d.INSERT INTO Hovercraft.Chassis (maintenancedate, hullid, registration, fuelcapacity, homebase) VALUES ('20120727', 26, N'AAA128', 800.75, N'Brisbane')

Back 114

cd
You can use the INSERT INTO Hovercraft.Chassis (hullid, registration, maintenancedate, homebase, fuelcapacity) VALUES (24, N'AAA123', '20120727', N'Melbourne', 900.50) statement. This statement correctly pairs column data types with column data as defined in the table creation statement.

You can use the INSERT INTO Hovercraft.Chassis (maintenancedate, hullid, registration, fuelcapacity, homebase) VALUES ('20120727', 26, N'AAA128', 800.75, N'Brisbane') statement. This statement correctly pairs column data types with column data as defined in the table creation statement.

Front 115

You are optimizing several Transact-SQL statements. You have determined that a particular statement will perform more efficiently if a lock is applied at the page level rather than at the table or row level.

Which of the following table hints will you use in your Transact-SQL statements when performing optimization?
a.TABLOCK
b.READPAST
c.PAGLOCK
d.ROWLOCK

Back 115

c. When you use the PAGLOCK table query hint, a lock is placed at the page level rather than at the row or table level.

Front 116

You want to create several markers within a transaction that a user can return to without necessarily rolling back the entire transaction.

Which of the following Transact-SQL statements can you use to accomplish this task?

a.ROLLBACK TRANSACTION
b.SAVE TRANSACTION
c.BEGIN TRANSACTION
d.COMMIT TRANSACTION

Back 116

You use the SAVE TRANSACTION statement to create a savepoint within a transaction. You can use the ROLLBACK TRANSACTION statement to roll back to a savepoint.

You use the BEGIN TRANSACTION statement to mark the start point of an explicit local transaction.

You use the COMMIT TRANSACTION statement to mark the end of a successful implicit or explicit transaction.

You use the ROLLBACK TRANSACTION statement to roll back an implicit or explicit transaction. You can use this statement to roll back to a savepoint or to the beginning of the transaction

Front 117

The Hovercraft database has three tables related to Hovercraft design, OldDesign, CurrentDesign, and FutureDesign. Some designs are present in more than one table. You want to generate a list of all items in the OldDesign table as well as OldDesign items that are present in both the CurrentDesign and FutureDesign tables.

Which of the following Transact-SQL statements can you use to accomplish this goal?
a.SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign INTERSECT SELECT HovercraftModelID, Name FROM Hovercraft.CurrentDesign EXCEPT SELECT HovercraftModelID, Name FROM Hovercraft.FutureDesign
b.SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign INTERSECT SELECT HovercraftModelID, Name FROM Hovercraft.CurrentDesign INTERSECT SELECT HovercraftModelID, Name FROM Hovercraft.FutureDesign
c.SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign UNION SELECT HovercraftModelID, Name FROM Hovercraft.CurrentDesign INTERSECT SELECT HovercraftModelID, Name FROM Hovercraft.FutureDesign
d.SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign UNION SELECT HovercraftModelID, Name FROM Hovercraft.CurrentDesign INTERSECT SELECT HovercraftModelID, Name FROM Hovercraft.FutureDesign

Back 117

c

You should use the SELECT HovercraftModelID, Name FROM Hovercraft.OldDesign UNION SELECT HovercraftModelID, Name FROM Hovercraft.CurrentDesign INTERSECT SELECT HovercraftModelID, Name FROM Hovercraft.FutureDesign statement. This statement will list all items in the OldDesign table as well as OldDesign items that are present in both the CurrentDesign and FutureDesign tables.

Not Correct
a.This statement will find items that are present in the OldDesign and CurrentDesign tables but not present in the FutureDesign table
b.This statement will find items that are present in all three tables.
c.This statement will list all items in the FutureDesign table as well as FutureDesign items that are present in both the OldDesign and CurrentDesign tables.

Front 118

You are optimizing several complex queries. You have determined that you can improve performance by concatenating UNION sets.

Which of the following query hints should you use to implement this optimization?
a.MERGE UNION
b.CONCAT UNION
c.HASH UNION
d.HASH JOIN

Back 118

b

The CONCAT UNION query hint option specifies that all UNION operations will be performed by concatenating UNION sets.

The MERGE UNION query hint option specifies that all UNION operations will be performed by merging UNION sets.

The HASH UNION query hint option specifies that all UNION operations will be performed by hashing UNION sets.

The HASH JOIN query hint option specifies that all JOIN operations in the query use HASH JOINs.

Front 119

You are working on some errors raised by a complex set of transactions. You want to ensure that transactions are not rolled back when low-severity errors are raised by the database engine.

Which of the following Transact-SQL statements can you use to accomplish this task?
a.SET XACT_ABORT OFF
b.SET TRANSACTION ISOLATION LEVEL READ COMMITTED
c.SET XACT_ABORT ON
d.SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Back 119

c. You use the SET XACT_ABORT OFF statement to allow a Transact-SQL statement to cause low-severity errors without rolling back the entire transaction.

You use the SET XACT_ABORT ON statement to ensure that when a Transact-SQL statement run-time error occurs, the entire transaction is terminated and rolled back.

Front 120

Which of the following Transact-SQL statements do you use if you want to release the current result set from a cursor?
a.OPEN
b.CLOSE
c.DEALLOCATE
d.FETCH

Back 120

b
You use the CLOSE statement to release the current result set. This releases cursor locks on rows on which the cursor is positioned.

You use the DEALLOCATE statement to remove a cursor reference.

You use the OPEN statement to open a Transact-SQL cursor. The cursor is populated by the results of the Transact-SQL statement used when the cursor is created.

You use the FETCH statement to retrieve a specific row from a CURSOR.

Front 121

Which of the following Transact-SQL statements do you use if you want to remove a cursor reference?
a.DEALLOCATE
b.OPEN
c.CLOSE
d.FETCH

Back 121

a
You use the DEALLOCATE statement to remove a cursor reference.

You use the OPEN statement to open a Transact-SQL cursor. The cursor is populated by the results of the Transact-SQL statement used when the cursor is created.

You use the FETCH statement to retrieve a specific row from a CURSOR.

You use the CLOSE statement to release the current result set. This releases cursor locks on rows on which the cursor is positioned.

Front 122

You are creating a view that will list all employees at your organization who make less than $50,000 per year. Users in Payroll will use this view to update salary information for these employees. All modifications to salary occur through the view. Users in Payroll should not be able to increase any employee's salary so that it exceeds $50,000.

Which clause would you use when creating the view to ensure that this restriction is enforced?
a.VIEW_METADATA
b.SCHEMABINDING
c.CHECK OPTION
d.ENCRYPTION

Back 122

c
Using the CHECK OPTION clause will mean that the view cannot be used to make a modification to data that would cause that data to disappear from the scope of the view.

The ENCRYPTION option means that the view definition will not be visible to unauthorized users.

The SCHEMABINDING option means that changes cannot be made to the underlying table structure that would affect the view.

The VIEW_METADATA option means that the SQL Server instance will return metadata information about the view instead of about base table or tables.

Front 123

One of the developers in your organization created a number of views without using the SCHEMABINDING clause. Several modifications to the objects underlying the view have meant that queries run against these views are generating unexpected results.

Which stored procedure should you run to resolve this issue?
a.sp_refreshview
b.sp_rename
c.sp_tableoption
d.sp_recompile

Back 123

d
The sp_refreshview system stored procedure updates metadata for a non-schema-bound view. You should run this system stored procedure when there are changes to the underlying objects on which the view depends.

Front 124

You are creating several views for a Hovercraft rental database. You need to ensure that data within the Hovercraft rental database is modified in authorized ways only. When creating the view, you specify the limits to the ways that data can be modified. You want to ensure that all statements that modify data executed against the view follow these criteria.

Which of the following options would you use when creating this view?
a.SCHEMABINDING
b.ENCRYPTION
c.CHECK OPTION
d.VIEW_METADATA

Back 124

c

You would use CHECK OPTION. This option forces all statements that modify data executed against the view to follow criteria set within the statement that defines the view.

You would not use ENCRYPTION. This option encrypts the text of the CREATE VIEW statement. A side effect of using the ENCRYPTION option is that the view cannot be published as part of SQL Server replication.

You would not use SCHEMABINDING. This option ensures that the base table or tables cannot be modified in any way that would affect the view definition.

You would not use VIEW_METADATA. When you use this option, browse mode metadata will return the view name and not the underlying table names when it lists columns from the view in the result set.

Front 125

You want to modify the properties of a view and move it to another schema.

Which of the following Transact-SQL statements would you use to accomplish this goal? (Each correct answer presents part of a complete solution. Choose two.)
a.CREATE VIEW
b.ALTER SCHEMA
c.DROP VIEW
d.ALTER VIEW

Back 125

bd
You use the ALTER VIEW statement to modify the properties of an existing view.

You use the ALTER SCHEMA statement to move a view from one schema to another.

You should not use the CREATE VIEW statement. This statement enables you to create a view. In this question, you are asked to move a view to a different schema and alter its properties.

You should not use the DROP VIEW statement. In this question, you are asked to move a view to a different schema and alter its properties.

Front 126

Must a view consist of only one SELECT statement?

Back 126

Technically, yes, but a workaround to this is that you can unite (using the UNION statement) multiple SELECT statements that together produce one result set.

Front 127

What types of views are available in T-SQL?

Back 127

You can create regular views, which are just stored SELECT statements, or indexed views, which actually materialize the data, in addition to partitioned views.

Front 128

inline functions (def)

Back 128

can use an inline table-valued function to simulate passing a parameter to a view, or in other words, simulate a parameterized view.

Front 129

1. What type of data does an inline function return?
2. What type of view can an inline function simulate?

Back 129

1. Inline functions return tables, and accordingly, are often referred to as inline table-valued functions.
2. An inline table-valued function can simulate a parameterized view—that is, a view that takes parameters

Front 130

Which of the following operators work in T-SQL views? (Choose all that apply.)
A. The WHERE clause
B. The ORDER BY clause
C. The UNION or UNION ALL operators
D. The GROUP BY clause

Back 130

acd
A. Correct: A view can contain a WHERE clause.
C. Correct: You can combine SELECT statements in a view with UNION and UNION ALL.
D. Correct: A view can contain a GROUP BY clause.
B. Incorrect: A view can contain an ORDER BY if the SELECT TOP clause is used, but no actual sorting of the results is guaranteed.

Front 131

What is the result of WITH SCHEMABINDING in a view?
A. The view cannot be altered without altering the table.
B. The tables referred to in the view cannot be altered unless the view is first altered.
C. The tables referred to in the view cannot be altered unless the view is first dropped.
D. The view cannot be altered unless the tables it refers to are first dropped.

Back 131

b

Front 132

What is the result of the WITH CHECK OPTION in a view that has a WHERE clause in its SELECT statement?
A. Data can no longer be updated through the view.
B. Data can be updated through the view, but primary key values cannot be changed.
C. Data can be updated through the view, but values cannot be changed that would cause rows to fall outside the filter of the WHERE clause.
D. Data can be updated through the view, but only columns with check constraints can be changed.

Back 132

C

Front 133

1. Does a synonym store T-SQL or any data?
2. Can synonyms be altered?

Back 133

1. No, a synonym is just a name. All that is stored with a synonym is the object it refers to.
2. No, to change a synonym, you must drop and recreate it.

Front 134

What types of database objects can have synonyms? (Choose all that apply.)
A. Stored procedures
B. Indexes
C. Temporary tables
D. Database users

Back 134

ac

A. Correct: Synonyms can refer to stored procedures.
C. Correct: Synonyms can refer to temporary tables.

B. Incorrect: Synonyms cannot refer to indexes; indexes are not database objects that are scoped by schema names.
D. Incorrect: Database users are not database objects that are scoped by schema names.

Front 135

Which of the following are true about synonyms? (Choose all that apply.)
A. Synonyms do not store T-SQL code or data.
B. Synonyms do not require schema names.
C. Synonym names can match those of the objects they refer to.
D. Synonyms can reference objects in other databases or through linked servers.

Back 135

ad

A. Correct: Synonyms are just names, and do not store T-SQL code or any data.
D. Correct: Synonyms can reference other database objects using three-part names, and objects through linked servers using four-part names.
B. Incorrect: Synonyms are database objects that are scoped to database schemas, just like tables, views, functions, and stored procedures, so they require schema names.
C. Incorrect: A synonym name (schema name plus object name) cannot be the same as any other schema-scoped database object, including other synonyms.

Front 136

What kind of dependencies do synonyms have on the objects they refer to?
A. Synonyms can be created WITH SCHEMABINDING to prevent the underlying objects from being altered.
B. Synonyms can refer to other synonyms.
C. Synonyms can be created to refer to database objects that do not yet exist.
D. Synonyms can be created without an initial schema name, which can be added later.

Back 136

Correct Answer: C
A. Incorrect: Only views can be created WITH SCHEMABINDING, not synonyms.
B. Incorrect: Synonyms cannot refer to other synonyms; synonym chaining is not allowed.
C. Correct: You can create a synonym that refers to a nonexistent object. In order to use the synonym, however, you must ensure that the object exists.
D. Incorrect: Synonyms always require a schema name.

Front 137

1. In which case out of the following are you normally not allowed to specify the target column in an INSERT statement?
A. If the column has a default constraint associated with it
B. If the column allows NULLs
C. If the column does not allow NULLs
D. If the column has an IDENTITY property

Back 137

D

Front 138

What are the things that the SELECT INTO statement doesn’t copy from the source?
(Choose all that apply.)
A. Indexes
B. Constraints
C. The IDENTITY property
D. Triggers

Back 138

A
B
D

Front 139

3. What are the benefits of using the combination of statements CREATE TABLE and INSERT SELECT over SELECT INTO? (Choose all that apply.)
A. Using the CREATE TABLE statement, you can control all aspects of the target table.
Using SELECT INTO, you can’t control some of the aspects, like the destination file group.
B. The INSERT SELECT statement is faster than SELECT INTO.
C. The SELECT INTO statement locks both data and metadata for the duration of the transaction. This means that until the transaction finishes, you can run into
blocking related to both data and metadata. If you run the CREATE TABLE and INSERT SELECT statements in separate transactions, locks against metadata will be
released quickly, reducing the probability for and duration of blocking related to metadata.
D. Using the CREATE TABLE plus INSERT SELECT statements involves less coding than using SELECT INTO.

Back 139

A
C

Front 140

Can you update rows in more than one table in one UPDATE statement?

Back 140

No, you can use columns from multiple tables as the source, but update only one table at a time.

Front 141

How do you modify a column value in a target row and collect the result of the modification in one visit to the row?
A. By using an UPDATE based on a join
B. By using an UPDATE based on a table expression
C. By using an UPDATE with a variable
D. The task cannot be achieved with only one visit to the row.

Back 141

C

Front 142

What are the benefits of using an UPDATE statement based on joins? (Choose all that apply.)
A. You can filter the rows to update based on information in related rows in other tables.
B. You can update multiple tables in one statement.
C. You can collect information from related rows in other tables to be used in the source expressions in the SET clause.
D. You can use data from multiple source rows that match one target row to update the data in the target row.

Back 142

A
C

B. Incorrect: You cannot update multiple tables in one UPDATE statement.
D. Incorrect: When multiple source rows match one target row, you get a nondeterministic
update in which only one source row is used. Also, the fact that such an update doesn’t fail should be considered a disadvantage—not a benefit.

Front 143

How can you update a table, setting a column to the result of a window function?
A. By using an UPDATE based on a join
B. By using an UPDATE based on a table expression
C. By using an UPDATE with a variable
D. The task cannot be achieved.

Back 143

B

Front 144

How do you delete rows from a table for which a ROW_NUMBER computation is equal to 1?
A. You refer to the ROW_NUMBER function in the DELETE statement’s WHERE clause.
B. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered DELETE statement against the table expression.
C. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered TRUNCATE statement
against the table expression.
D. The task cannot be achieved.

Back 144

B

Front 145

Which of the following is applicable to a DELETE statement?
A. The statement writes more to the transaction log than TRUNCATE.
B. The statement resets an IDENTITY property.
C. The statement is disallowed when a foreign key points to the target table.
D. The statement is disallowed when an indexed view based on the target table exists.

Back 145

A
B. Incorrect: The DELETE statement does not rest an IDENTITY property.
C. Incorrect: A DELETE statement is allowed even when there’s a foreign key pointing to the table, as long as there are no rows related to the deleted ones.
D. Incorrect: A DELETE statement is allowed when an indexed view based on the target table exists.

Front 146

Which of the following is applicable to a TRUNCATE statement? (Choose all that apply.)
A. The statement writes more to the transaction log than DELETE.
B. The statement resets an IDENTITY property.
C. The statement is disallowed when a foreign key points to the target table.
D. The statement is disallowed when an indexed view based on the target table exists.

Back 146

B
C
D

Front 147

Which function do you use to return the last identity value generated in a specific table?
A. MAX
B. SCOPE_IDENTITY
C. @@IDENTITY
D. IDENT_CURRENT

Back 147

D
The IDENT_CURRENT function accepts a table name as input and returns the last identity value generated in that table

Front 148

What are the advantages of using a sequence object instead of IDENTITY? (Choose all that apply.)
A. The IDENTITY property doesn’t guarantee that there won’t be gaps and the sequence object does.
B. The IDENTITY property cannot be added to or removed from an existing column; a DEFAULT constraint with a NEXT VALUE FOR function can be added to or removed
from an existing column.
C. A new identity value cannot be generated before issuing an INSERT statement, whereas a sequence value can.
D. You cannot provide your own value when inserting a row into a table with an IDENTITY column without special permissions. You can specify your own value for a column that normally gets its values from a sequence object.

Back 148

B
C
D

Front 149

In an INSERT SELECT statement, how do you generate sequence values in specific order?
A. Use the OVER clause in the NEXT VALUE FOR function.
B. Specify an ORDER BY clause at the end of the query.
C. Use TOP (100) PERCENT and ORDER BY in the query.
D. Use TOP (9223372036854775807) and ORDER BY in the query.

Back 149

а

Front 150

What is the purpose of the ON clause in the MERGE statement?

Back 150

The ON clause determines whether a source row is matched by a target row, and whether a target row is matched by a source row. Based on the result of the predicate, the MERGE statement knows which WHEN clause to activate and as a result, which action to take against the target.

Front 151

What are the possible actions in the WHEN MATCHED clause?

Back 151

UPDATE and DELETE.

Front 152

How many WHEN MATCHED clauses can a single MERGE statement have?

Back 152

Two—one with an UPDATE action and one with a DELETE action.

Front 153

Which WHEN clauses are required in a MERGE statement at minimum?
A. At minimum, the WHEN MATCHED and WHEN NOT MATCHED clauses are required.
B. At minimum, only one clause is required, and it can be any of the WHEN clauses.
C. At minimum, the WHEN MATCHED clause is required.
D. At minimum, the WHEN NOT MATCHED clause is required

Back 153

b

Front 154

What can you specify as the source data in the USING clause? (Choose all that apply.)
A. A regular table, table variable, or temporary table
B. A table expression like a derived table or a CTE
C. A stored procedure
D. A table function like OPENROWSET or OPENXML

Back 154

abd

C Incorrect: Stored procedures aren’t allowed as the source in a MERGE statement

Front 155

Which clause of the MERGE statement isn’t standard?
A. The WHEN MATCHED clause
B. The WHEN NOT MATCHED clause
C. The WHEN NOT MATCHED BY SOURCE clause
D. All MERGE clauses are standard.

Back 155

c Correct: The WHEN NOT MATCHED BY SOURCE clause isn’t standard.

Front 156

How many OUTPUT clauses can a single statement have?

Back 156

Two—one with INTO and one without INTO.

Front 157

How do you determine which action affected the OUTPUT row in a MERGE
statement?

Back 157

Use the $action function.

Front 158

When referring in the OUTPUT clause to columns from the inserted rows, when should you prefix the columns with the keyword inserted?
a. Always
B. Never
c. Only when the statement is UPDATE
D. Only when the statement is MERGE

Back 158

a. correct: When referring to elements from inserted rows, you must always prefix the column with the keyword inserted.

Front 159

What is the restriction in regard to the table specified as the target of an OUTPUT
INTO clause? (Choose all that apply.)
a. The table can only be a table variable.
B. The table can only be a temporary table.
c. The table cannot participate in either side of a foreign key relationship.
D. The table cannot have triggers defined on it.

Back 159

c. correct: The target table cannot take part in a foreign key relationship.
D. correct: The target table cannot have triggers defined on it.

Front 160

Which of the following is only possible when using the MERGE statement in regard to the OUTPUT clause?
a. Referring to columns from the source table
B. Referring to both the keywords deleted and inserted
c. Assigning aliases to output columns
D. Using composable DML

Back 160

a. correct: Only in a MERGE statement’s OUTPUT clause can you refer to elements from the source table.

Front 161

Why is it important for SQL Server to maintain the ACID quality of transactions?

Back 161

To ensure that the integrity of database data will not be compromised

Front 162

How does SQL Server implement transaction durability?

Back 162

By first writing all changes to the database transaction log before making changes to the database data.

Front 163

How many ROLLBACKs must be executed in a nested transaction to roll it back?

Back 163

Only one ROLLBACK. A ROLLBACK always rolls back the entire transaction, no matter how many levels the transaction has.

Front 164

How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed?

Back 164

One COMMIT for each level of the nested transaction. Only the last COMMIT actually commits the entire transaction.

Front 165

1. Can readers block readers?
2. Can readers block writers?

Back 165

1. No, because shared locks are compatible with other shared locks.
2. Yes, even if only momentarily, because any exclusive lock request has to wait until the shared lock is released

Front 166

Can a SELECT statement be involved in a deadlock?

Back 166

Yes. If the SELECT statement locks some resource that keeps a second transaction from finishing, and the SELECT cannot finish because it is blocked by the same transaction, the deadlock cycle results.

Front 167

If your session is in the READ COMMITTED isolation level, is it possible for one of your queries to read uncommitted data?

Back 167

Yes, if the query uses the WITH (NOLOCK) or WITH (READUNCOMMITTED) table hint. The session value for the isolation level does not change, just the characteristics for reading that table.

Front 168

Is there a way to prevent readers from blocking writers and still ensure that readers only see committed data?

Back 168

Yes, that is the purpose of the READ COMMITTED SNAPSHOT option within the READ COMMITTED isolation level. Readers see earlier versions of data changes for current transactions, not the currently uncommitted data.

Front 169

Which of the following T-SQL statements automatically occur in the context of a transaction?
(Choose all that apply.)
A. An ALTER TABLE command
B. A PRINT command
C. An UPDATE command
D. A SET command

Back 169

ac
A Correct: An ALTER TABLE command is a DDL command that changes metadata and always executes as a transaction.
C. Correct: An UPDATE statement changes data and executes as a transaction.
D. Incorrect: A SET statement only affects session settings and does not change data, and therefore does not execute as a transaction.

Front 170

How do the COMMIT and ROLLBACK commands work with nested transactions in T-SQL? (Choose all that apply.)
A. A single COMMIT commits the entire nested transaction.
B. A single ROLLBACK rolls back the entire nested transaction.
C. A single COMMIT commits only one level of the nested transaction.
D. A single ROLLBACK rolls back only one level of the nested transaction.

Back 170

B
C

Front 171

Which of the following strategies can help reduce blocking and deadlocking by reducing shared locks? (Choose all that apply.)
A. Add the READUNCOMMITTED table hint to queries.
B. Use the READ COMMTTED SNAPSHOT option.
C. Use the REPEATABLE READ isolation level.
D. Use the SNAPSHOT isolation level.

Back 171

A. Correct: Adding a READUNCOMMITTED table hint causes no shared locks to be used by the statement.
B. Correct: The READ COMMITTED SNAPSHOT option reads committed data from versions, not by acquiring shared locks.
C. Incorrect: The REPEATABLE READ isolation level actually holds shared locks until the end of a transaction, and therefore can actually increase blocking and deadlocking.
D. Correct: The SNAPSHOT isolation level also reduces shared locks by reading committed data from committed versions and not by using shared locks, so it also can
reduce blocking and deadlocking.

Front 172

Which of the following are T-SQL regular identifiers? (Choose all that apply.)
A. categoryname
B. category name
C. category$name
D. category_name

Back 172

ad
B. Incorrect: A regular identifier cannot contain a space.
C. Incorrect: A regular identifier cannot contain a dollar sign ($).

Front 173

Which data type should be used in place of TIMESTAMP?
A. VARBINARY
B. ROWVERSION
C. DATETIME2
D. TIME

Back 173

B. Correct: ROWVERSION is the replacement for the deprecated TIMESTAMP

Front 174

How can you express that the column categoryname allow NULLs?
A. categoryname PERMIT NULL NVARCHAR(15)
B. categoryname NVARCHAR(15) ALLOW NULL
C. categoryname NVARCHAR(15) PERMIT NULL
D. categoryname NVARCHAR(15) NULL

Back 174

D. Correct: You specify NULL right after the data type.

Front 175

Which of the following columns would be appropriate as a surrogate key? (Choose all that apply.)
A. The time (in hundredths of a second) that the row was inserted
B. An automatically increasing integer number
C. The last four digits of a social security number concatenated with the first eight digits of a user's last name
D. A uniqueidentifier (GUID) newly selected from SQL Server at the time the row is inserted

Back 175

b
d

Front 176

You want to enforce that a valid supplierid be entered for each productid in the Production.Products table. What is the appropriate constraint to use?
A. A unique constraint
B. A default constraint
C. A foreign key constraint
D. A primary key constraint

Back 176

C. Correct: A foreign key constraint validates that a value exists in another table.

Front 177

What metadata tables give you a list of constraints in a database? (Choose all that apply.)
A. sys.key_constraints
B. sys.indexes
C. sys.default_constraints
D. sys.foreign_keys

Back 177

A
C
D
A. Correct: sys.key_constraints lists all primary key and unique constraints in a
database.
B. Incorrect: sys.indexes does not list constraints.
C. Correct: sys.default_constraints lists the default constraints in a database.
D. Correct: sys.foreign_keys lists all the foreign keys in a database.

Front 178

Can a TRY/CATCH block span batches

Back 178

No, you must have one set of TRY/CATCH blocks for each batch of code.

Front 179

What is the advantage of using THROW in a CATCH block?
A. THROW in a CATCH block does not require parameters and so is easier to write.
B. THROW re-throws the original error so that the original error can be handled.
C. THROW causes an error severity of level 16 automatically.
D. The statement before a THROW requires a semicolon.

Back 179

b

Front 180

Which of the following functions can be used in a CATCH block to return information
about the error? (Choose all that apply.)
A. @@ERROR
B. ERROR_NUMBER()
C. ERROR_MESSAGE()
D. XACT_STATE()

Back 180

ABCD

Front 181

How does SET XACT_ABORT ON affect a transaction?
A. If a T-SQL error with a severity level > 16 occurs, the transaction will be aborted.
B. If a T-SQL error with a severity level > 10 occurs, the transaction will be aborted.
C. If a T-SQL error with a severity level > 16 occurs, some statements of the transaction may still be executed.
D. If a T-SQL error with a severity level > 10 occurs, some statements of the transaction may still be executed.

Back 181

B Correct: A T-SQL error with severity level > 10 causes the transaction to be aborted.

Front 182

How can a hacker detect that SQL injection may be possible?

Back 182

By inserting a single quotation mark and observing an error message.

Front 183

Where is the injected code inserted?

Back 183

Between an initial single quotation mark, which terminates the data input string, and a final comment mark, which disables the internal terminating single quotation mark.

Front 184

How can you pass information from sp_executesql to the caller?

Back 184

Use one or more OUTPUT parameters. You can also persist the data in a permanent or temporary table, but the most direct method is through the OUTPUT parameter.

Front 185

How does sp_executesql help stop SQL injection?

Back 185

You can use sp_executesql to parameterize user input, which can prevent any injected code from being executed.

Front 186

Which of the following techniques can be used to inject unwanted code into dynamic SQL when user input is concatenated with valid SQL commands?
A. Insert a comment string of two dashes, then the malicious code, and then a single quotation mark.
B. Insert a single quotation mark, then the malicious code, and then a comment string of two dashes.
C. Insert the malicious code followed by a single quotation mark and a comment string of two dashes.

Back 186

b

Front 187

What are the advantages of sp_executesql over the EXECUTE() command? (Choose all that apply.)
A. sp_executesql can parameterize search arguments and help prevent SQL injection.
B. sp_executesql uses Unicode strings.
C. sp_executesql can return data through output parameters.

Back 187

a
c

Front 188

Which of the following are true about the SET QUOTED_IDENTIFIER statement?
(Choose all that apply.)
A. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks
to delimit T-SQL identifiers such as table and column names.
B. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks
to delimit T-SQL identifiers such as table and column names.
C. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks
to delimit strings.
D. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks
to delimit strings.

Back 188

A
d

Front 189

What are the actions of the optimization phase of query execution? (Choose all that
apply.)
A. Generation of the algebrized tree
B. Generation of candidate plans
C. Selection of the best candidate plan
D. Caching the plan
E. Query execution

Back 189

B
C

Front 190

In which phase of query execution does SQL Server check whether the objects referred
to by the query exist?
A. In the parsing phase
B. In the binding phase
C. In the optimization phase
D. In the execution phase

Back 190

B. Correct: In the optimization phase, SQL Server generates candidate plans.
C. Correct: During the optimization phase, SQL Server selects an execution plan from the set of candidate plans.

Front 191

Which of the following is not a part of an Extended Events package?
A. Predicates
B. Targets
C. Sources
D. Actions

Back 191

C