Monday, November 10, 2014

Microsoft.Press.Training.Kit.Exam.70-461.Nov.2012

Microsoft.Press.Training.Kit.Exam.70-461.Nov.2012

1. What are the mathematical branches that the relational model is based on?
2. What is the difference between T-SQL and SQL?
 Answer
1. Set theory and predicate logic.
2. SQL is standard; T-SQL is the dialect of and extension to SQL that Microsoft implements in its RDBMS—SQL Server.

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.
 Answer
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.

1. Why are the terms “field” and “record” incorrect when referring to column and row?
2. Why is the term “NULL value” incorrect?
 Answer
1. Because “field” and “record” describe physical things, whereas columns and rows are logical elements of a table.
2. Because NULL isn’t a value; rather, it’s a mark for a missing value.

■■ What is the difference between the WHERE and HAVING clauses?
 Answer
■■ 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.

1. Why are you not allowed to refer to a column alias defined by the SELECT clause in the WHERE clause?
2. Why are you not allowed to refer to a column alias defined by the SELECT clause in the same SELECT clause?
 Answer
1. Because the WHERE clause is logically evaluated in a phase earlier to the one that evaluates the SELECT clause.
2. Because all expressions that appear in the same logical query processing phase are evaluated conceptually at the same point in time.

1. What are the forms of aliasing an attribute in T-SQL?
2. What is an irregular identifier?
 Answer
1. The forms are <expression> AS <alias>, <expression> <alias>, and
<alias> = <expression>.
2. 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.

1. Would you use the type FLOAT to represent a product unit price?
2. What is the difference between NEWID and NEWSEQUENTIALID?
3. Which function returns the current date and time value as a DATETIME2 type?
4. When concatenating character strings, what is the difference between the plus
(+) operator and the CONCAT function?
 Answer
1. No, because FLOAT is an approximate data type and cannot represent all values precisely.
2. The NEWID function generates GUID values in random order, whereas the NEWSEQUENTIAL ID function generates GUIDs that increase in a sequential order.
3. The SYSDATETIME function.
4. The + operator by default yields a NULL result on NULL input, whereas the
CONCAT function treats NULLs as empty strings.

1. What are the performance benefits in using the WHERE filter?
2. What is the form of a filter predicate that can rely on index ordering called?
 Answer
1. 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.
2. A search argument, or SARG, for short.


1. How do you guarantee the order of the rows in the result of a query?
2. What is the difference between the result of a query with and one without an
ORDER BY clause?
 Answer
1. The only way to do so is by adding an ORDER BY clause.
2. 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.

1. How do you guarantee deterministic results with TOP?
2. What are the benefits of using OFFSET-FETCH over TOP?
 Answer
1. By either returning all ties by using the WITH TIES option or by defining unique
ordering to break ties.
2. OFFSET-FETCH is standard and TOP isn’t; also, OFFSET-FETCH supports a skipping
capability that TOP doesn’t.

1. What is the difference between the old and new syntax for cross joins?
2. What are the different types of outer joins?
 Answer
1. The new syntax has the CROSS JOIN keywords between the table names and
the old syntax has a comma.
2. Left, right, and full.

1. What is the difference between self-contained and correlated subqueries?
2. What is the difference between the APPLY and JOIN operators?
 Answer
1. Self-contained subqueries are independent of the outer query, whereas correlated
subqueries have a reference to an element from the table in the outer
query.
2. With a JOIN operator, both inputs represent static relations. With APPLY, the
left side is a static relation, but the right side can be a table expression with
correlations to elements from the left table.

1. Which set operators does T-SQL support?
2. Name two requirements for the queries involved in a set operator.
 Answer
1. The UNION, INTERSECT, and EXCEPT set operators, as well as the UNION ALL
multiset operator.
2. The number of columns in the two queries needs to be the same, and corresponding
columns need to have compatible types.


1. What makes a query a grouped query?
2. What are the clauses that you can use to define multiple grouping sets in the
same query?
 Answer
1. When you use an aggregate function, a GROUP BY clause, or both.
2. GROUPING SETS, CUBE, and ROLLUP.

1. What is the difference between PIVOT and UNPIVOT?
2. What type of language constructs are PIVOT and UNPIVOT implemented as?
 Answer
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.

1. What are the clauses that the different types of window functions support?
2. What do the delimiters UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
represent?
 Answer
1. Partitioning, ordering, and framing clauses.
2. The beginning and end of the partition, respectively.

■■ Can you store indexes from the same full-text catalog to different filegroups?
 Answer
■■ 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.

1. How do you search for synonyms of a word with the CONTAINS predicate?
2. Which is a more specific predicate, CONTAINS or FREETEXT?
 Answers
1. You have to use the CONTAINS(FTcolumn, ‘FORMSOF(THESAURUS,
SearchWord1)’) syntax.
2. You use the CONTAINS predicate for more specific searches.

■■ How many full-text search and how many semantic search functions are supported
by SQL Server?
 Answer
■■ SQL Server supports two full-text search and three semantic search functions.

■■ How can you get an XSD schema together with an XML document from your
SELECT statement?
 Answer
■■ You should use the XMLSCHEMA directive in the FOR XML clause.

1. What do you do in the return clause of the FLWOR expressions?
2. What would be the result of the expression (12, 4, 7) != 7?
 Answers
1. In the return clause, you format the resulting XML of a query.
2. The result would be true.

■■ Which XML data type method would you use to retrieve scalar values from an XML
instance?
 Answer
■■ The value() XML data type method retrieves scalar values from an XML instance.

1. Can a table or column name contain spaces, apostrophes, and other nonstandard
characters?
2. What types of table compression are available?
 Answer
1. Yes, table and column names can be delimited identifiers that contain nonstandard
characters.
2. You can use either page or row compression on a table. Page compression includes row compression.

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?
 Answer
1. SQL Server uses unique indexes to enforce uniqueness for both primary key
and unique constraints.
2. No, all table constraints must have unique names in a database.

1. Must a view consist of only one SELECT statement?
2. What types of views are available in T-SQL?
 Answer
1. 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.
2. You can create regular views, which are just stored SELECT statements, or
indexed views, which actually materialize the data, in addition to partitioned
views.

1. What type of data does an inline function return?
2. What type of view can an inline function simulate?
 Answer
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.

1. Does a synonym store T-SQL or any data?
2. Can synonyms be altered?
 Answer
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.

1. Why is it recommended to specify the target column names in INSERT
statements?
2. What is the difference between SELECT INTO and INSERT SELECT?
 Answer
1. Because then you don’t care about the order in which the columns are defined
in the table. Also, you won’t be affected if the column order is rearranged due
to future definition changes, in addition to when columns that get their values
automatically are added.
2. SELECT INTO creates the target table and inserts into it the result of the query.
INSERT SELECT inserts the result of the query into an already existing table.

1. Which table rows are updated in an UPDATE statement without a WHERE
clause?
2. Can you update rows in more than one table in one UPDATE statement?
 Answer
1. All table rows.
2. No, you can use columns from multiple tables as the source, but update only
one table at a time.


1. Which rows from the target table get deleted by a DELETE statement without a
WHERE clause?
2. What is the alternative to a DELETE statement without a WHERE clause?
 Answer
1. All target table rows.
2. The TRUNCATE statement. But there are a few differences between the two
that need to be considered.

1. How many columns with an IDENTITY property are supported in one table?
2. How do you obtain a new value from a sequence?
 Answer
1. One.
2. With the NEXT VALUE FOR function.

1. What is the purpose of the ON clause in the MERGE statement?
2. What are the possible actions in the WHEN MATCHED clause?
3. How many WHEN MATCHED clauses can a single MERGE statement have?
 Answer
1. 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.
2. UPDATE and DELETE.
3. Two—one with an UPDATE action and one with a DELETE action.

1. How many OUTPUT clauses can a single statement have?
2. How do you determine which action affected the OUTPUT row in a MERGE
statement?
 Answer
1. Two—one with INTO and one without INTO.
2. Use the $action function.

1. Why is it important for SQL Server to maintain the ACID quality of
transactions?
2. How does SQL Server implement transaction durability?
 Answer
1. To ensure that the integrity of database data will not be compromised.
2. By first writing all changes to the database transaction log before making
changes to the database data.

1. How many ROLLBACKs must be executed in a nested transaction to roll it back?
2. How many COMMITs must be executed in a nested transaction to ensure that
the entire transaction is committed?
 Answer
1. Only one ROLLBACK. A ROLLBACK always rolls back the entire transaction, no
matter how many levels the transaction has.
2. One COMMIT for each level of the nested transaction. Only the last COMMIT
actually commits the entire transaction.

1. Can readers block readers?
2. Can readers block writers?
 Answer
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.

1. If two transactions never block each other, can a deadlock between them
result?
2. Can a SELECT statement be involved in a deadlock?
 Answer
1. No. In order to deadlock, each transaction must already have locked a resource
the other transaction wants, resulting in mutual blocking.
2. 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.

1. If your session is in the READ COMMITTED isolation level, is it possible for one
of your queries to read uncommitted data?
2. Is there a way to prevent readers from blocking writers and still ensure that
readers only see committed data?
 Answer
1. 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.
2. 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.

1. How can you add custom error messages?
2. What is severity level 0 used for?
 Answer
1. You can use the system stored procedure sp_addmessage to add your own
custom error messages.
2. When you issue a RAISERROR with severity level 0, only an informational message
is sent. If you add WITH NOWAIT, the message will be sent without waiting
in the output buffer.

1. What are the main advantages of using a TRY/CATCH block over the traditional
trapping for @@ERROR?
2. Can a TRY/CATCH block span batches?
 Answer
1. The main advantage is that you have one place in your code that errors will be
trapped, so you only need to put error handling in one place.
2. No, you must have one set of TRY/CATCH blocks for each batch of code.

1. Can you generate and execute dynamic SQL in a different database than the
one your code is in?
2. What are some objects that cannot be referenced in T-SQL by using variables?
 Answer
1. Yes, because the USE <database> command can be inserted into a dynamic
SQL batch.
2. Objects that you cannot use variables for in T-SQL commands include the
database name in a USE statement, the table name in a FROM clause, column
names in the SELECT and WHERE clauses, and lists of literal values in the IN()
and PIVOT() functions.

1. How can a hacker detect that SQL injection may be possible?
2. Where is the injected code inserted?
 Answer
1. By inserting a single quotation mark and observing an error message.
2. 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.

1. How can you pass information from sp_executesql to the caller?
2. How does sp_executesql help stop SQL injection?
 Answer
1. 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.
2. You can use sp_executesql to parameterize user input, which can prevent any
injected code from being executed.

■■ What is the result of the parsing phase of query execution?
 Answer
■■ The result of this phase, if the query passed the syntax check, is a tree of logical
operators known as a parse tree.

1. How would you quickly measure the amount of disk IO a query is performing?
2. How can you get an estimated execution plan in XML format for further analysis?
 Answers
1. You should use the SET STATISTICS IO command.
2. You can use the SET SHOWPLAN_XML command.

■■ Which DMO gives you detailed text of queries executed?
 Answer
■■ You can retrieve the text of batches and queries executed from the
sys.dm_exec_sql_text DMO.

1. What are the two types of parameters for a T-SQL stored procedure?
2. Can a stored procedure span multiple batches of T-SQL code?
 Answer
1. A T-SQL stored procedure can have input and output parameters.
2. No, a stored procedure can only contain one batch of T-SQL code.

1. What are the two types of DML triggers that can be created?
2. If an AFTER trigger discovers an error, how does it prevent the DML command
from completing?
 Answer
1. You can create AFTER and INSTEAD OF DML-type triggers.
2. An AFTER trigger issues a THROW or RAISERROR command to cause the transaction
of the DML command to roll back.

1. What are the two types of table-valued UDFS?
2. What type of UDF returns only a single value?
 Answer
1. You can create inline or multistatement table-valued UDFs.
2. A scalar UDF returns only a single value.

■■ What kind of clustering key would you select for an OLTP environment?
 Answer
■■ For an OLTP environment, a short, unique, and sequential clustering key might be
the best choice.

■■ Which clauses of a query should you consider supporting with an index?
 Answer
■■ The list of the clauses you should consider supporting with an index includes, but
is not limited to, the WHERE, JOIN, GROUP BY, and ORDER BY clauses.

■■ How would you quickly update statistics for the whole database after an upgrade?
 Answer
■■ You should use the sys.sp_updatestats system procedure.

1. What are the commands that are required to work with a cursor?
2. When using the FAST_FORWARD option in the cursor declaration command,
what does it mean regarding the cursor properties?
 Answer
1. DECLARE, OPEN, FETCH in a loop, CLOSE, and DEALLOCATE.
2. That the cursor is read-only, forward-only.

1. How do you create a local temporary table, and how do you create a global
one?
2. Can you name constraints in local temporary tables and in table variables?
 Answer
1. You name a local temporary table by using a single number sign as a prefix and
a global one with two number signs as a prefix.
2. You can name constraints in local temporary tables, although it’s not recommended
because it can generate name conflicts. You cannot name constraints
in table variables.

■■ How would you determine whether SQL Server used the batch processing mode
for a specific iterator?
 Answer
■■ You can check the iterator’s Actual Execution Mode property.

■■ Why might you prefer using plan guides instead of optimizer hints?
 Answer
■■ With plan guides, you do not need to change the query text.



No comments:

Post a Comment