Querying Microsoft Sql Server 2012 70-461

Front 1

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 1

Correct Answers: A, B, and C
A. Correct: The form '2012-02-12' is language-neutral for the data types DATE, DATETIME2, and DATETIMEOFFSET, but language-dependent for DATETIME and SMALLDATETIME.
B. Correct: The form '02/12/2012' is language-dependent.
C. Correct: The form '12/02/2012' is language-dependent.
D. Incorrect: The form '20120212' is language-neutral.

Front 2

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 2

Correct Answer: C
A. Incorrect: The FROM and SELECT clauses are mandatory in a SELECT query according to standard SQL but not T-SQL.
B. Incorrect: The WHERE clause is optional in T-SQL.
C. Correct: According to T-SQL, the only mandatory clause is the SELECT clause.
D. Incorrect: The FROM and WHERE clauses are both optional in T-SQL.

Front 3

What is the difference between the following functions when the TRY is used?
CAST, CONVERT, PARSE

TRY_CAST, TRY_CONVERT, TRY_PARSE

Back 3

The difference between the functions without the TRY and their counterparts with the TRY is that those without the TRY fail if the value isn’t convertible, whereas those with the TRY return a NULL in such a case.
The following code fails.
SELECT CAST('abc' AS INT);
Conversely, the following code returns a NULL.
SELECT TRY_CAST('abc' AS INT);

Front 4

What is the difference between the Simple CASE form and the Searched CASE form?

Back 4

The simple form compares an input expression to multiple possible scalar WHEN expressions and returns the result expression.
SELECT productid, productname, unitprice, discontinued,
CASE discontinued
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END AS discontinued_desc
FROM Production.Products

The searched form uses predicates in the WHEN clause and the first value that evaluates to TRUE determones which WHEN expression is returned.
SELECT productid, productname, unitprice,
CASE
WHEN unitprice < 20.00 THEN 'Low'
WHEN unitprice < 40.00 THEN 'Medium'
WHEN unitprice >= 40.00 THEN 'High'
ELSE 'Unknown'
END AS pricerange
FROM Production.Products;

Front 5

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 5

Correct Answer: B
A. Incorrect: Formatting isn’t a responsibility of the type or the data layer in general; rather, it is the responsibility of the presentation layer.
B. Correct: The type should be considered a constraint because it limits the values allowed.
C. Incorrect: The type itself doesn’t prevent duplicates. If you need to prevent duplicates, you use a primary key or unique constraint.
D. Incorrect: A type doesn’t prevent NULLs. For this, you use a NOT NULL constraint.

Front 6

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 6

Correct Answers: A and B
A. Correct: The NEWID function creates GUIDs in random order. You would consider it when the size overhead is not a major issue and the ability to generate a unique
value across time and space, from anywhere, in random order is a higher priority.
B. Correct: The NEWSEQUENTIALID function generates GUIDs in increasing order within the machine. It helps reduce fragmentation and works well when a single
session loads the data, and the number of drives is small. However, you should carefully consider an alternative using another key generator, like a sequence object,
with a smaller type when possible.
C. Incorrect: There’s no assurance that GETDATE will generate unique values; therefore, it’s not a good choice to generate keys.
D. Incorrect: The CURRENT_TIMESTAMP function is simply the standard version of GETDATE, so it also doesn’t guarantee uniqueness.

Front 7

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

Back 7

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

SELECT empid, country, region, city,
country + N',' + region + N',' + city AS location
FROM HR.Employees;
empid country region city location
------ -------- ------- -------- ----------
1 USA WA Redmond USA,WA,Redmond
2 UK NULL London NULL

SELECT empid, country, region, city,
CONCAT(country, N',' + region, N',' + city) AS location
FROM HR.Employees;
empid country region city location
------ -------- ------- ------ ------------
1 USA WA Redmond USA,WA,Redmond
2 UK NULL London UK,London

Front 8

Describe what the following functions are used for and list the ones that are standard.

COALESCE() , NULLIF() , ISNULL(), IIF(), CHOOSE()

Back 8

All of these functions can be considered abbreviates of the CASE expression. They are used to handle NULL

CASE
WHEN <exp1> IS NOT NULL THEN <exp1>
WHEN <expn> IS NOT NULL THEN <expn>
ELSE NULL
END
COALESCE() and NULLIF() are standard. IIF and CHOOSE were added to T-SQL to simplify migrations from ACCESS platforms.

Front 9

What are the three T-SQL query clauses that enable you to filter data based on predicates?

Back 9

The ON, WHERE, and HAVING clauses.

ON: Inner Joins - filters only rows for which the predicate evaluates to true.
ON: Outer Joins - ON predicate determines which rows from the nonpreserved side get matched to rows from the preserved side—not whether to return the rows from the preserved side.

WHERE: returns rows for which the predicate evaluates to TRUE.

HAVING: uses a predicate but evaluates the predicate per group as opposed to per row.

Front 10

Which of the following predicates are search arguments (SARG)? (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'
F. YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2

Back 10

Correct Answers: B and E
A. Incorrect: This predicate applies manipulation to the filtered column, and hence isn’t a search argument.
B. Correct: The LIKE predicate is a search argument when the pattern starts with a known prefix.
C. Incorrect: The LIKE predicate isn’t a search argument when the pattern starts with a wild card.
D. Incorrect: The LIKE predicate isn’t a search argument when the pattern starts with a wild card.
E. Correct: Because no manipulation is applied to the filtered column, the predicate is a search argument.
F. Incorrect: This predicate applies manipulation to the filtered column, and hence isn’t a search argument.

Front 11

How can query filters be important to performance and in what ways?

Back 11

1.) By filtering rows in the query (as opposed to in the client), you reduce network traffic.
2.) When query filters have the predicate set in the form of a search argument, it allows SQL Server to evaluate the option using indexes as opposed to a full table scan.

Front 12

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 12

Correct Answers: A, B, and C
A. Correct: The form '2012-02-12' is language-neutral for the data types DATE,DATETIME2, and DATETIMEOFFSET, but language-dependent for DATETIME and SMALLDATETIME.
B. Correct: The form '02/12/2012' is language-dependent.
C. Correct: The form '12/02/2012' is language-dependent.
D. Incorrect: The form '20120212' is language-neutral.

Front 13

What is language-neutral and language-dependant mean in reference to Date and Time data?

Back 13

language-dependant means that based on the language and/or location a date can be interpreted differently.
WHERE orderdate = '02/12/07';
American, this form probably means February 12, 2007
British, this form probably means December 2, 2007
Japanese, this form probably means December 7, 2002
Language-neutral means that the date can only be interpreted one way, for example the form '20070212' is always interpreted as ymd, regardless of your language.

Front 14

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 14

Correct Answer: A
A. Correct: Without an ORDER BY clause, ordering isn’t guaranteed and is said to be arbitrary—it’s optimization-dependent.
B. Incorrect: Without an ORDER BY clause, there’s no guarantee for ordering.
C. Incorrect: Without an ORDER BY clause, there’s no guarantee for ordering.
D. Incorrect: Without an ORDER BY clause, there’s no guarantee for ordering.

Front 15

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 15

Correct Answer: F
A. Incorrect: If there are at least three rows in the query result without TOP, the query will return at least three rows.
B. Incorrect: 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.
C. Incorrect: If there are fewer rows than three in the query result without TOP, the query will return only those 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.
D. Incorrect: If there are fewer rows than three in the query result without TOP, the query will return only those rows.
E. Incorrect: If there are three rows or less in the query result without TOP, the query won’t return more than three rows.
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 16

Describe the characteristics of the following Joins in terms of matching?

Cross Join
Inner Join
Outer Join
Multi-Joins

Back 16

Cross joins return a Cartesian product of the rows from both sides.

Inner joins match rows based on a predicate and return only matches.

Outer joins match rows based on a predicate and return both matches and nonmatches from the tables marked as preserved.

Multi-join queries involve multiple joins. They can have a mix of different join types. You can control the logical join ordering by using parentheses or by repositioning the
ON clauses.

Front 17

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 17

Correct Answer: D
A. Incorrect: Both clauses use three-valued logic.
B. Incorrect: Both clauses use three-valued logic.
C. Incorrect: ON determines matching and WHERE determines filtering.
D. Correct: ON determines matching and WHERE determines filtering.

Front 18

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 18

Correct Answers: C and D
A. Incorrect: The JOIN keyword cannot be omitted in the new syntax for joins.
B. Incorrect: If the CROSS keyword is omitted from CROSS JOIN, the keyword JOIN alone means inner join and not cross join anymore.
C. Correct: If the INNER keyword is omitted from INNER JOIN, the meaning is retained.
D. Correct: If the OUTER keyword is omitted from LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, the meaning is retained.

Front 19

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 19

Correct Answer: A
A. Correct: The syntax with the JOIN keyword is consistent with the only standard syntax available for outer joins and is less prone to errors.
B. Incorrect: Outer joins don’t have a standard syntax based on commas.
C. Incorrect: There’s no such recommendation. Cross and inner joins have a reason to exist.
D. Incorrect: There’s no such evidence.

Front 20

What is the difference between Self-Contained subqueries and Correlated subqueries.

Back 20

Self-contained subqueries have no dependency on the outer query. Whereas Correlated subqueries are subqueries where the inner query has a reference to a column from the table in the outer query.

Self-Contained
SELECT productid, productname, unitprice
FROM Production.Products
WHERE supplierid IN
(SELECT supplierid
FROM Production.Suppliers
WHERE country = N'Japan');

Correlated Subquery
SELECT categoryid, productid, productname, unitprice
FROM Production.Products AS P1
WHERE unitprice =
(SELECT MIN(unitprice)
FROM Production.Products AS P2
WHERE P2.categoryid = P1.categoryid);

Front 21

What are table expression and what are the four forms supported by T-SQL.

Back 21

Table expressions are named queries.
T-SQL supports four forms of table expressions:
■ Derived tables
■ Common table expressions (CTEs)
■ Views
■ Inline table-valued functions

Front 22

Which of the table expression are visible only in the scope of the statement that defines them? (Choose all that apply)
A. Derived tables
B. Common table expressions (CTEs)
C. Views
D. Inline table-valued functions

Back 22

Correct Answer: A and B

Derived tables and CTEs are table expressions that are
visible only in the scope of the statement that defines them. After that statement terminates, the table expression is gone. Hence, derived tables and CTEs are not reusable.

For reusability,you need to store the definition of the table expression as an object in the database, and for this you can use either views or inline table-valued functions.

Front 23

Which two table expressions are stored as an object in the database and what is the main difference between them?
A. Derived tables
B. Common table expressions (CTEs)
C. Views
D. Inline table-valued functions

Back 23

Correct Answer: C and D

The main difference between views and inline table-valued functions is that a view doesn’t accept input parameters and an inline table-valued functions does.

Front 24

What is the difference between the APPLY and JOIN operators?

Back 24

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.

Front 25

What is the OFFSET-FETCH option used for and how does it work?

Back 25

The OFFSET-FETCH option is a filtering option that is used to filter data based on a specified number of rows and ordering. Unlike TOP, it is standard, and also has a
skipping capability, making it useful for ad-hoc paging purposes.

SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY;

The above query defines ordering based on order date descending, followed by order ID descending; it then skips 50 rows and fetches the next 25 rows.

<offset_fetch> ::= {OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS } [FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY]}

Front 26

Is the ORDER BY clause required when using OFFSET-FETCH and if so, what role does it play?

Back 26

In T-SQL, the OFFSET-FETCH option requires an ORDER BY clause to be present. Also, in T-SQL, contrary to standard SQL—a FETCH clause requires an OFFSET clause to be present. So if you do want to filter some rows but skip none, you still need to specify
the OFFSET clause with 0 ROWS.

The ORDER BY clause plays two roles: One role is telling the OFFSET-FETCH option which rows it needs to filter. Another role is it's usual role determining presentation ordering in the query.

Front 27

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 27

Correct Answer: A and C
A. Correct: T-SQL supports indicating an OFFSET clause without a FETCH clause.
B. Incorrect: Contrary to standard SQL, T-SQL does not support a FETCH clause without an OFFSET clause.
C. Correct: T-SQL supports indicating both OFFSET and FETCH clauses.
D. Incorrect: T-SQL does not support OFFSET-FETCH without an ORDER BY clause.

Front 28

Name the Set Operators which are supported by T-SQL.

Back 28

■ UNION
■ INTERSECT
■ EXCEPT
■ UNION ALL (a multi-set operator)

Front 29

What is the difference between the set operator UNION and the multi-set operator UNION ALL?

Back 29

UNION has an implied DISTINCT property, meaning that it does not return duplicate rows.

UNION ALL operator unifies the results of the two input queries, but doesn’t try to eliminate duplicates.

Front 30

Describe the Row return actions of eack of the SET Operators:

■ UNION
■ INTERSECT
■ EXCEPT
■ UNION ALL (a multi-set operator)

Back 30

UNION: unifies the results of the two input queries with an implied DISTINCT, so that duplicate rows are not returned.

INTERSECT: returns only distinct rows that are common to both sets.

EXCEPT: returns distinct rows that appear in the first
query but not the second.

UNION ALL: unifies the results of the two input queries, but doesn’t try to eliminate duplicates.

Front 31

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

Back 31

Correct Answer: D
A. Incorrect: With UNION, the order of the inputs doesn’t matter.
B. Incorrect: With UNION ALL, the order of the inputs doesn’t matter.
C. Incorrect: With INTERSECT, the order of the inputs doesn’t matter.
D. Correct: With EXCEPT, the order of the inputs matters.

Front 32

What makes a query a grouped query?

Back 32

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

Front 33

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

Back 33

GROUPING SETS - list all grouping sets that you want to define in the query.

CUBE - accepts a list of expressions as inputs and defines all possible grouping sets that can be generated from the inputs—including the empty grouping set.

ROLLUP - when the elements form a hierarchy, you use the ROLLUP

Front 34

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 34

Correct Answer: A
A. Correct: The COUNT(*) function doesn’t operate on an input expression; instead, it counts the number of rows in the group. The COUNT(<expression>) function operates on an expression and ignores NULLs. Interestingly, COUNT(<expression>) returns 0 when all inputs are NULLs, whereas other general set functions like MIN,
MAX, SUM, and AVG return a NULL in such a case.

B. Incorrect: COUNT(*) counts rows.

C. Incorrect: COUNT(*) returns an INT.

D. Incorrect: Clearly, there is a difference between the functions in the treatment of NULLs.

Front 35

What types of table compression are available?

Back 35

Page or Row compression can be used on a table. Page compression also incorporates row compression.

Front 36

What is the difference between the results of COUNT(shippeddate) and COUNT(*) in

SELECT shipperid,
COUNT(*) AS numorders,
COUNT(shippeddate) AS shippedorders,
FROM Sales.OrderValues
GROUP BY shipperid;
.

Back 36

The COUNT(shippeddate) ignores NULLs in the shippeddate column, and therefore the counts are less than or equal to those produced by COUNT(*) .

Front 37

What is the syntax of a Pivot query?

Back 37

WITH PivotData AS
(
SELECT
[1st pivoted column] , -- grouping column
[2nd pivoted column], -- spreading column
[last pivoted column] -- aggregation column
FROM [database_table])
SELECT [1st pivoted column], [1], [2], [3]
FROM PivotData
PIVOT(SUM(]last pivoted column]) FOR shipperid IN ([1],[2],[3]) ) AS P;

SUM = any group aggregate function.

Front 38

What are Window Functions and how are they used?

Back 38

A window function is one that can be applied to a partitioned set of rows (known as a window) in order to rank or aggregate values in that partition.

Window functions have nothing to do with Microsoft Windows, but they do provide a way to work with data grouped into logical windows.

Front 39

What are the four Ranking Window Functions?

Back 39

•ROW_NUMBER: Assigns a sequential number to each row in the result set.

•RANK: Ranks each row in the result set. If values in the ranking column are the same, they receive the same rank. However, the next number in the ranking sequence is skipped.

•DENSE_RANK: Ranks each row in the result set. If values in the ranking column are the same, they receive the same rank. The next number in the ranking sequence is then used to rank the row or rows that follow.

•NTILE: Divides the result set into the number of groups specified as an argument to the function. A group number is then assigned to each row identifying which group the row belongs to

Front 40

What are the Window Aggregate Functions?

Back 40

Window aggregate functions are the same as the group aggregate functions (for example,
SUM, COUNT, AVG, MIN, and MAX), except window aggregate functions are applied to a
window of rows defined by the OVER clause.

Front 41

What is the name of the key clause in Window Functions?

Back 41

The OVER clause

The key to understanding SQL Server window functions is in the OVER clause, which can be defined on any window function used in a query’s select list. The clause determines how to partition and sort a result set in order to apply the window function. In fact, you can think of a window function as one that supports the OVER clause.

<window function> OVER (<supported subclause>) AS <name>

Front 42

What are the three subclauses supported by the OVER clause? What is their purpose?

Back 42

PARTITION BY : Partitions the result set based on one or more columns or expressions.

ORDER BY : Sorts one or more columns or expressions within the partition. The ORDER BY clause applies strictly within the context of the partition.

ROWS|RANGE: Further defines how the data is displayed within the partition.

Front 43

What do Window Offset Functions do and what are their names?

Back 43

Window offset functions let you return a value from a row that's in a certain offset from the current row .

LAG function returns a value from a row in the window partition that, based on the window ordering, is the specified number of rows before the current row.

LEAD function returns a value from a row in the window partition that, based on the window ordering, is the specified number of rows after the current row.

By default, the LAG and LEAD functions assume an offset of one row if an explicit offset wasn't specified.

FIRST_VALUE and LAST_VALUE return the requested value from the first and last rows, respectively, from the applicable window frame.

Front 44

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 44

Correct Answer: B
A. Incorrect: These definitions are one less than the correct ones.
B. Correct: These are the correct definitions.
C. Incorrect: These definitions are two less than the correct ones.
D. Incorrect: The opposite is true—the two functions return the same result when the ordering is unique.

Front 45

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 45

Correct Answer: A
A. Correct: Window functions are supposed to operate on the underlying query’s result
set. In terms of logical query processing, this result set is reached in the SELECT phase.
B. Incorrect: Standard SQL defines this restriction, so it has nothing to do with Microsoft’s time constraints.
C. Incorrect: There are practical reasons to want to filter or group data based on the results of window functions.
D. Incorrect: There are neither door functions nor backdoor functions in SQL.

Front 46

What is Full-Text search?

Back 46

Full-Text Search in SQL Server lets users and applications run full-text queries against character-based data in SQL Server tables.

Full-text queries perform linguistic searches against text data in full-text indexes by operating on words and phrases based on rules of a particular language such as English or Japanese. Full-text queries can include simple words and phrases or multiple forms of a word or phrase.

Front 47

What are the two kinds of DML triggers supported by SQL Server?

Back 47

AFTER: fires after the event it is associated with finishes. Can only be defined on permanent tables.

INSTEAD OF: fires instead of the event it is associated with. Can be defined on permanent tables and views.

Front 48

Syntax of a DML Trigger

Back 48

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

FOR is for Server Scope

Front 49

Syntax of a DDL Trigger

Back 49

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier > [ ; ] }

<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

FOR is for Server Scope

Front 50

What are the main query clauses specified in the "keyed-in" order - the order in which they are typed?

Back 50

1) SELECT
2) FROM
3) WHERE
4) GROUP BY
5) HAVING
6) ORDER BY

Front 51

What are the main query clauses ordered in the logical processing order - the conceptual interpretation order?

Back 51

1) FROM
2) WHERE
3) GROUP BY
4) HAVING
5) SELECT
6) ORDER BY

Front 52

How does the HAVING clause filter rows? How is this different from the WHERE clause?

Back 52

The HAVING clause filters data based on a predicate but is evaluated per group and so filters the group as a whole. The GROUP BY clause must be used in order to use the HAVING clause. The difference between HAVING and WHERE is that WHERE filters rows and HAVING filters groups. In the conceptual query interpretaion order HAVING is interpreted after GROUP BY, whereas WHERE is interpreted before.

Front 53

Is the following query valid? Explain.

SELECT empid, country, YEAR(hiredate) AS yearhired, yearhired - 1 AS prevyear
FROM HR.Employees;

Back 53

Invalid. An Alias created by the SELECT phase isn't even visible to other expressions that appear in the same SELECT list.

The reason that this isn’t allowed is that, conceptually, T-SQL evaluates all expressions that appear in the same logical query processing phase in an all-at-once manner. Note the use of the word conceptually. SQL Server won’t necessarily physically process all expressions at the
same point in time, but it has to produce a result as if it did. This behavior is different than many other programming languages where expressions usually get evaluated in a left-toright order, making a result produced in one expression visible to the one that appears to its
right. But T-SQL is different.

Front 54

What are the relational model's core mathematical foundations?

Back 54

Set Theory and predicate logic.

By a "set", we mean any collection into a whole of unordered collection of unique, non-duplicated items.

A Predicate is an expression that when attributed to some object, makes a proposition either True or False.

Front 55

How does T-SQL deviate from the relational model concerning predicate logic?

Back 55

When a predicate compares two values, the result evaluates to either true or false. However, SQL implements a general purpose mark called NULL for any kind of missing value. Thus a result can evaluate to a third logical value - unknown.
Because of this, every piece of code written with T-SQL where NULLs are possible in the data - one needs to understand the treatment of those NULLs in the query.

Front 56

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

Back 56

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

Front 57

What two methods can be used in T-SQL to delimit an irregular identifier?

Back 57

T-SQL supports both a proprietary way to delimit identifiers by using square brackets [phone number], and the standard form using double quotation marks, as in "phone number".

Front 58

When should you use unicode datatypes over regular character datatypes?

Back 58

If data is in multiple languages and you need to represent only one language besides English in your data, you can benefit from using regular character types, with lower storage requirements.

When data is international, or your application natively works with Unicode data, you should use Unicode data types so you don’t lose information.

Front 59

Describe the identity column property.

Back 59

A property that automatically generates keys in
an attribute of a numeric type with a scale of 0; namely, any integer type (TINYINT,
SMALLINT, INT, BIGINT) or NUMERIC/DECIMAL with a scale of 0.

Front 60

Describe the Sequence object.

Back 60

An independent object in the database from which you can
obtain new sequence values. Like identity, it supports any numeric type with a scale of 0. Unlike identity, it’s not tied to a particular column; instead, as mentioned, it is an
independent object in the database.

Front 61

Describe how to generate Nonsequential GUIDS.

Back 61

You can generate nonsequential global unique identifiers to be stored in an attribute of a UNIQUEIDENTIFIER type. You can use the T-SQL function NEWID to generate a new GUID.

Front 62

Describe how to generate Sequential GUIDS.

Back 62

You can generate sequential GUIDs within the machine by using the T-SQL function NEWSEQUENTIALID.

Front 63

Describe the syntax and what the COALESCE expression does.

Back 63

COALESCE ( expression [ ,...n ] )

The COALESCE expression is a syntactic shortcut for the CASE expression. That is, the code COALESCE(expression1,...n) is rewritten by the query optimizer as the following CASE expression:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
WHEN (expression2 IS NOT NULL) THEN expression2
...
ELSE expressionN
END

Can also be used to substitute a NULL for something else: COALESCE(region, '')

Front 64

What is three-valued logic and how does it effect filtering data in a query?

Back 64

A predicate evaluates to true or false. SELECT * FROM table WHERE x = 3. However, when NULLS are possible then three-value logic comes into ploay. A predicate can then evalute to true,false and unknown. In this case both false and unknown cases are discarded.

Front 65

What makes a predicate a search argument and why is this important?

Back 65

When a predicate is presented in the form of a search argument (WHERE col1=10), SQL Server is able to efficiently use indexes. If NULLS are involved and they need to be taken into account, then the filtered column may need to be manipulated (WHERE col1=10 OR col1 IS NULL), then SQL Server cannot efficiently use indexes.

Front 66

What is a Query Hint and what is the syntax for using them?

Back 66

Query hints specify that the indicated hints should be used throughout a query. A Query Hint overides the default behavior of the query optimizer in selecting an exceution plan.

Query hints are specified as part of the OPTION clause.
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);

Front 67

What does SET STATISTICS IO do and what is the syntax?

Back 67

Causes SQL Server to display information regarding the amount of disk activity generated by Transact-SQL statements.

SET STATISTICS IO { ON | OFF }

SET STATISTICS IO ON;
GO
SELECT * FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;
GO
SET STATISTICS IO OFF;
RESULTS
Table 'ProductCostHistory'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0,lob read-ahead reads 0.

Front 68

What is a TABLE Hint and what is the syntax for using them?

Back 68

Table hints override the default behavior of the query optimizer for the duration of the data manipulation language (DML) statement by specifying a locking method, one or more indexes, a query-processing operation such as a table scan or index seek, or other options.
Table hints are specified in the FROM clause of the DML statement and affect only the table or view referenced in that clause.

FROM t WITH (TABLOCK, INDEX(myindex))

Front 69

What is a Hint and what are their categories?

Back 69

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.

There are three types of Hints:
JOIN Hint - hint forces the type of join algorithm that is used. Can be used in SELECT,UPDATE and DELETE

QUERY Hint - used when certain kind of logic has to be applied to a whole query. Can be used in SELECT,UPDATE,DELETE, ONSERT and MERGE.

TABLE Hint - used when certain kind of locking mechanism of tables has to be controlled. Can be used in SELECT,UPDATE,DELETE, ONSERT and MERGE.

Front 70

What is a JOIN Hint and what is the syntax for using them?

Back 70

The SQL Server query optimizer utilzes Physical Join Operators, which are algorithms that are used in carrying out T-SQL joins. Using a JOIN hint overrides any default the query optimizer might select for a query.

JOIN hints are specified in the FROM clause of the DML statement (DELETE, SELECT or UPDATE).

DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER LOOP JOIN Sales.SalesPerson AS sp
ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;

Front 71

Place the following operators in order of precedence.

OR
AND
NOT

Back 71

The NOT operator precedes AND and OR, and AND precedes OR. NOTE: parentheses have the highest precedence among ALL operators and can be used to fully control the logical evaluation order that you need.

NOT
AND
OR

Front 72

Describe the actions of the TOP option? What is the syntax?

Back 72

With the TOP option, you can filter a requested number or percent of rows from the query
result based on indicated ordering.

SELECT TOP (3) or SELECT TOP (3) PERCENT

Front 73

The ORDER BY clause is mandatory when using OFFSET-FETCH because this clause is standard, and standard SQL decided to make the ORDER BY mandatory. Microsoft simply followed the standard. With this being the case, how do you make a query using OFFSET-FETCH nondeterministic (more than one correct result)?

Back 73

If you want the ordering to be completely nondeterministic, you can specify ORDER BY (SELECT NULL) this makes it equivalent to not specifying an ORDER BY clause at all.