Mcsa 70-641

Front 1

What is the difference between the functions without the TRY and their counterparts with the TRY

SELECT CAST('abc' AS INT);
SELECT TRY_CAST('abc' AS INT);

Back 1

that those without the TRY fail if the value isn’t convertible, whereas those with the TRY return a NULL in such a case.

SELECT CAST('abc' AS INT); << FAILS
SELECT TRY_CAST('abc' AS INT); << RETURNS NULL

Front 2

What is the difference between the functions without the TRY and their counterparts with the TRY

SELECT CAST('abc' AS INT);
SELECT TRY_CAST('abc' AS INT);

Back 2

that those without the TRY fail if the value isn’t convertible, whereas those with the TRY return a NULL in such a case.

SELECT CAST('abc' AS INT); << FAILS
SELECT TRY_CAST('abc' AS INT); << RETURNS NULL

Front 3

What is the difference between the functions without the TRY and their counterparts with the TRY

SELECT CAST('abc' AS INT);
SELECT TRY_CAST('abc' AS INT);

Back 3

that those without the TRY fail if the value isn’t convertible, whereas those with the TRY return a NULL in such a case.

SELECT CAST('abc' AS INT); << FAILS
SELECT TRY_CAST('abc' AS INT); << RETURNS NULL

Front 4

What is the difference between the functions without the TRY and their counterparts with the TRY

SELECT CAST('abc' AS INT);
SELECT TRY_CAST('abc' AS INT);

Back 4

that those without the TRY fail if the value isn’t convertible, whereas those with the TRY return a NULL in such a case.

SELECT CAST('abc' AS INT); << FAILS
SELECT TRY_CAST('abc' AS INT); << RETURNS NULL

Front 5

As for the difference between CAST, CONVERT, and PARSE,

Back 5

with CAST, you indicate the expression and the target type

with CONVERT there’s a third argument representing the style for the conversion, which is supported for some conversions, like between character strings and date and time values. For example, CONVERT(DATE, '1/2/2012', 101) converts the
literal character string to DATE using style 101 representing the United States standard

PARSE, you can indicate the culture by using any culture supported by the Microsoft .NET Framework. For example, PARSE('1/2/2012' AS DATE USING 'en-US') parses the input literal as a DATE by using a United States English culture.

Front 6

As for the difference between CAST, CONVERT, and PARSE,

Back 6

with CAST, you indicate the expression and the target type

with CONVERT there’s a third argument representing the style for the conversion, which is supported for some conversions, like between character strings and date and time values. For example, CONVERT(DATE, '1/2/2012', 101) converts the
literal character string to DATE using style 101 representing the United States standard

PARSE, you can indicate the culture by using any culture supported by the Microsoft .NET Framework. For example, PARSE('1/2/2012' AS DATE USING 'en-US') parses the input literal as a DATE by using a United States English culture.

Front 7

As for the difference between CAST, CONVERT, and PARSE,

Back 7

with CAST, you indicate the expression and the target type

with CONVERT there’s a third argument representing the style for the conversion, which is supported for some conversions, like between character strings and date and time values. For example, CONVERT(DATE, '1/2/2012', 101) converts the
literal character string to DATE using style 101 representing the United States standard

PARSE, you can indicate the culture by using any culture supported by the Microsoft .NET Framework. For example, PARSE('1/2/2012' AS DATE USING 'en-US') parses the input literal as a DATE by using a United States English culture.

Front 8

As for the difference between CAST, CONVERT, and PARSE,

Back 8

with CAST, you indicate the expression and the target type

with CONVERT there’s a third argument representing the style for the conversion, which is supported for some conversions, like between character strings and date and time values. For example, CONVERT(DATE, '1/2/2012', 101) converts the
literal character string to DATE using style 101 representing the United States standard

PARSE, you can indicate the culture by using any culture supported by the Microsoft .NET Framework. For example, PARSE('1/2/2012' AS DATE USING 'en-US') parses the input literal as a DATE by using a United States English culture.

Front 9

Float short comings (code)

Back 9

DECLARE @F AS FLOAT = '29545428.022495';
SELECT CAST (@F AS NUMERIC (28, 14)) AS value;
----------------------------------------------------------------
29545428.02249500200000

Front 10

Float short comings (code)

Back 10

DECLARE @F AS FLOAT = '29545428.022495';
SELECT CAST (@F AS NUMERIC (28, 14)) AS value;
----------------------------------------------------------------
29545428.02249500200000

Front 11

Float short comings (code)

Back 11

DECLARE @F AS FLOAT = '29545428.022495';
SELECT CAST (@F AS NUMERIC (28, 14)) AS value;
----------------------------------------------------------------
29545428.02249500200000

Front 12

Float short comings (code)

Back 12

DECLARE @F AS FLOAT = '29545428.022495';
SELECT CAST (@F AS NUMERIC (28, 14)) AS value;
----------------------------------------------------------------
29545428.02249500200000

Front 13

What is the difference between GETDATE and CURRENT_TIMESTAMP

Back 13

GETDATE is T_SQL specific
CURRENT_TIMESTAMP is standard
they both return the current date and time in SQL Server instance

Front 14

What is the difference between GETDATE and CURRENT_TIMESTAMP

Back 14

GETDATE is T_SQL specific
CURRENT_TIMESTAMP is standard
they both return the current date and time in SQL Server instance

Front 15

What is the difference between GETDATE and CURRENT_TIMESTAMP

Back 15

GETDATE is T_SQL specific
CURRENT_TIMESTAMP is standard
they both return the current date and time in SQL Server instance

Front 16

What is the difference between GETDATE and CURRENT_TIMESTAMP

Back 16

GETDATE is T_SQL specific
CURRENT_TIMESTAMP is standard
they both return the current date and time in SQL Server instance

Front 17

Note that there are no build-in functions to return the current date or the current time, to get such info ....

Back 17

simply cast the SYSDATETIME function to DATE or TIME, respectively

CVAST (SYSDATETIME() AS DATE)

Front 18

Note that there are no build-in functions to return the current date or the current time, to get such info ....

Back 18

simply cast the SYSDATETIME function to DATE or TIME, respectively

CVAST (SYSDATETIME() AS DATE)

Front 19

Note that there are no build-in functions to return the current date or the current time, to get such info ....

Back 19

simply cast the SYSDATETIME function to DATE or TIME, respectively

CVAST (SYSDATETIME() AS DATE)

Front 20

Note that there are no build-in functions to return the current date or the current time, to get such info ....

Back 20

simply cast the SYSDATETIME function to DATE or TIME, respectively

CVAST (SYSDATETIME() AS DATE)

Front 21

DATEPART func.

Back 21

Using the DATEPART func, you can extract from an input date and time value a desired part, such as year, min, etc.

DATEPART(month,'20120212') returns 2

Front 22

DATEPART func.

Back 22

Using the DATEPART func, you can extract from an input date and time value a desired part, such as year, min, etc.

DATEPART(month,'20120212') returns 2

Front 23

DATEPART func.

Back 23

Using the DATEPART func, you can extract from an input date and time value a desired part, such as year, min, etc.

DATEPART(month,'20120212') returns 2

Front 24

DATEPART func.

Back 24

Using the DATEPART func, you can extract from an input date and time value a desired part, such as year, min, etc.

DATEPART(month,'20120212') returns 2

Front 25

DATEPART func and YEAR, MONTH, DATE

Back 25

T-SQL provides the functions YEAR, MONTH, DATE as abbr to DATEPART

SELECT DATEPART(YEAR, '20130317') - return 2013
SELECT DATEPART(MONTH, '20130317') - return 03
SELECT DATEPART(DAY, '20130317') - return 17

Front 26

DATEPART func and YEAR, MONTH, DATE

Back 26

T-SQL provides the functions YEAR, MONTH, DATE as abbr to DATEPART

SELECT DATEPART(YEAR, '20130317') - return 2013
SELECT DATEPART(MONTH, '20130317') - return 03
SELECT DATEPART(DAY, '20130317') - return 17

Front 27

DATEPART func and YEAR, MONTH, DATE

Back 27

T-SQL provides the functions YEAR, MONTH, DATE as abbr to DATEPART

SELECT DATEPART(YEAR, '20130317') - return 2013
SELECT DATEPART(MONTH, '20130317') - return 03
SELECT DATEPART(DAY, '20130317') - return 17

Front 28

DATEPART func and YEAR, MONTH, DATE

Back 28

T-SQL provides the functions YEAR, MONTH, DATE as abbr to DATEPART

SELECT DATEPART(YEAR, '20130317') - return 2013
SELECT DATEPART(MONTH, '20130317') - return 03
SELECT DATEPART(DAY, '20130317') - return 17

Front 29

DATENAM func

Back 29

similar to DATEPART but returns character stings opposed to int value

SELECT DATENAME(MONTH, '20130317') - returns March

Front 30

DATENAM func

Back 30

similar to DATEPART but returns character stings opposed to int value

SELECT DATENAME(MONTH, '20130317') - returns March

Front 31

DATENAM func

Back 31

similar to DATEPART but returns character stings opposed to int value

SELECT DATENAME(MONTH, '20130317') - returns March

Front 32

DATENAME func

Back 32

similar to DATEPART but returns character stings opposed to int value

SELECT DATENAME(MONTH, '20130317') - returns March

Front 33

T-SQL provide a set of functions that construct a desired date and time value from its numeric parts (list)

Back 33

DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS

Front 34

T-SQL provide a set of functions that construct a desired date and time value from its numeric parts (list)

Back 34

DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS

Front 35

T-SQL provide a set of functions that construct a desired date and time value from its numeric parts (list)

Back 35

DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS

Front 36

T-SQL provide a set of functions that construct a desired date and time value from its numeric parts (list)

Back 36

DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS

Front 37

Functions that compute the respective end of moth date for the input date and time

Back 37

EOMONTH
EOMONTH(SYSDATETIME()) - in Feb will return 2012-02-29

Front 38

Functions that compute the respective end of moth date for the input date and time

Back 38

EOMONTH
EOMONTH(SYSDATETIME()) - in Feb will return 2012-02-29

Front 39

Functions that compute the respective end of moth date for the input date and time

Back 39

EOMONTH
EOMONTH(SYSDATETIME()) - in Feb will return 2012-02-29

Front 40

Functions that compute the respective end of month date for the input date and time

Back 40

EOMONTH
EOMONTH(SYSDATETIME()) - in Feb will return 2012-02-29

Front 41

DATEADD def
DATEDIFF def

Back 41

DATEADD - add a requested number of unites of a specified part to a specified date and time value

DATEADD(year, 1, '20120212) adds 1 year to 2012-02-12

DATEDIFF - computes the difference in days between two dates

DATEDIFF(year, '20111131', '20120101') returns 1
NOTE: as we specified year in doesnt take in account days and month

Front 42

DATEADD def
DATEDIFF def

Back 42

DATEADD - add a requested number of unites of a specified part to a specified date and time value

DATEADD(year, 1, '20120212) adds 1 year to 2012-02-12

DATEDIFF - computes the difference in days between two dates

DATEDIFF(year, '20111131', '20120101') returns 1
NOTE: as we specified year in doesnt take in account days and month

Front 43

DATEADD def
DATEDIFF def

Back 43

DATEADD - add a requested number of unites of a specified part to a specified date and time value

DATEADD(year, 1, '20120212) adds 1 year to 2012-02-12

DATEDIFF - computes the difference in days between two dates

DATEDIFF(year, '20111131', '20120101') returns 1
NOTE: as we specified year in doesnt take in account days and month

Front 44

DATEADD def
DATEDIFF def

Back 44

DATEADD - add a requested number of unites of a specified part to a specified date and time value

DATEADD(year, 1, '20120212) adds 1 year to 2012-02-12

DATEDIFF - computes the difference in days between two dates

DATEDIFF(year, '20111131', '20120101') returns 1
NOTE: as we specified year in doesnt take in account days and month

Front 45

NULL + ' word' returns NULL

If you want to substitute NULL with an empty string you can use option 1 (COALESCE)

Back 45

COALESCE(<expression>, '')

SELECT COALESCE( @ST , N'') + 'work' -- returns work
SELECT @ST + 'work' -- returns NULL

Front 46

NULL + ' word' returns NULL

If you want to substitute NULL with an empty string you can use option 1 (COALESCE)

Back 46

COALESCE(<expression>, '')

SELECT COALESCE( @ST , N'') + 'work' -- returns work
SELECT @ST + 'work' -- returns NULL

Front 47

NULL + ' word' returns NULL

If you want to substitute NULL with an empty string you can use option 1 (COALESCE)

Back 47

COALESCE(<expression>, '')

SELECT COALESCE( @ST , N'') + 'work' -- returns work
SELECT @ST + 'work' -- returns NULL

Front 48

COALESCE function accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs. For example, the expression COALESCE(NULL, 'x', 'y') returns 'x'.

Back 48

COALESCE(<exp1>, <exp2>, …, <expn>)
is similar to the following.
CASE
WHEN <exp1> IS NOT NULL THEN <exp1>
WHEN <exp2> IS NOT NULL THEN <exp2>
…
WHEN <expn> IS NOT NULL THEN <expn>
ELSE NULL
END

Front 49

NULL + ' word' returns NULL

If you want to substitute NULL with an empty string you can use option 2

Back 49

CONCAT unlike the + operator substitutes a NULL input with an empty string

DECLARE @ST AS VARCHAR(10) = NULL
SELECT CONCAT( @ST , N'Work') as work -- returns work

Front 50

NULL + ' word' returns NULL

If you want to substitute NULL with an empty string you can use option 2

Back 50

CONCAT unlike the + operator substitutes a NULL input with an empty string

DECLARE @ST AS VARCHAR(10) = NULL
SELECT CONCAT( @ST , N'Work') as work -- returns work

Front 51

NULL + ' word' returns NULL

If you want to substitute NULL with an empty string you can use option 2

Back 51

CONCAT unlike the + operator substitutes a NULL input with an empty string

DECLARE @ST AS VARCHAR(10) = NULL
SELECT CONCAT( @ST , N'Work') as work -- returns work

Front 52

NULL + ' word' returns NULL

If you want to substitute NULL with an empty string you can use option 2

Back 52

CONCAT unlike the + operator substitutes a NULL input with an empty string

DECLARE @ST AS VARCHAR(10) = NULL
SELECT CONCAT( @ST , N'Work') as work -- returns work

Front 53

DATALENGTH function (vs LEN)

Back 53

returns the length of the input in terms of number of bytes
LEN function returns the length of an input string in terms of the number of characters

DATALENGTH(N'xyz') returns 6
LEN(N'xyz') returns 3

Front 54

DATALENGTH function (vs LEN)

Back 54

returns the length of the input in terms of number of bytes
LEN function returns the length of an input string in terms of the number of characters

DATALENGTH(N'xyz') returns 6
LEN(N'xyz') returns 3

Front 55

DATALENGTH function (vs LEN)

Back 55

returns the length of the input in terms of number of bytes
LEN function returns the length of an input string in terms of the number of characters

DATALENGTH(N'xyz') returns 6
LEN(N'xyz') returns 3

Front 56

DATALENGTH function (vs LEN)

Back 56

returns the length of the input in terms of number of bytes
LEN function returns the length of an input string in terms of the number of characters

DATALENGTH(N'xyz') returns 6
LEN(N'xyz') returns 3

Front 57

REPLICATE function

Back 57

allows you to replicate an input string a requested number of
times. For example, the expression REPLICATE('0', 10) replicates the string '0' ten times, returning '0000000000'.

Front 58

REPLICATE function

Back 58

allows you to replicate an input string a requested number of
times. For example, the expression REPLICATE('0', 10) replicates the string '0' ten times, returning '0000000000'.

Front 59

REPLICATE function

Back 59

allows you to replicate an input string a requested number of
times. For example, the expression REPLICATE('0', 10) replicates the string '0' ten times, returning '0000000000'.

Front 60

REPLICATE function

Back 60

allows you to replicate an input string a requested number of
times. For example, the expression REPLICATE('0', 10) replicates the string '0' ten times, returning '0000000000'.

Front 61

STUFF function

Back 61

STUFF function operates on an input string provided as the first argument; then, from the character position indicated as the second argument, deletes the number of characters
indicated by the third argument. Then it inserts in that position the string specified as the fourth argument. For example, the expression STUFF(',x,y,z', 1, 1, '') removes the first character from the input string, returning 'x,y,z'.

Front 62

STUFF function

Back 62

STUFF function operates on an input string provided as the first argument; then, from the character position indicated as the second argument, deletes the number of characters
indicated by the third argument. Then it inserts in that position the string specified as the fourth argument. For example, the expression STUFF(',x,y,z', 1, 1, '') removes the first character from the input string, returning 'x,y,z'.

Front 63

STUFF function

Back 63

STUFF function operates on an input string provided as the first argument; then, from the character position indicated as the second argument, deletes the number of characters
indicated by the third argument. Then it inserts in that position the string specified as the fourth argument. For example, the expression STUFF(',x,y,z', 1, 1, '') removes the first character from the input string, returning 'x,y,z'.

Front 64

STUFF function

Back 64

STUFF function operates on an input string provided as the first argument; then, from the character position indicated as the second argument, deletes the number of characters
indicated by the third argument. Then it inserts in that position the string specified as the fourth argument. For example, the expression STUFF(',x,y,z', 1, 1, '') removes the first character from the input string, returning 'x,y,z'.

Front 65

there’s no TRIM function that removes both leading and trailing spaces; to achieve this, you need to

Back 65

to trim you need to nest one function call within another, as in RTRIM(LTRIM(<input>)).

Front 66

there’s no TRIM function that removes both leading and trailing spaces; to achieve this, you need to

Back 66

to trim you need to nest one function call within another, as in RTRIM(LTRIM(<input>)).

Front 67

there’s no TRIM function that removes both leading and trailing spaces; to achieve this, you need to

Back 67

to trim you need to nest one function call within another, as in RTRIM(LTRIM(<input>)).

Front 68

there’s no TRIM function that removes both leading and trailing spaces; to achieve this, you need to

Back 68

to trim you need to nest one function call within another, as in RTRIM(LTRIM(<input>)).

Front 69

FORMAT function

Back 69

you can format an input value based on a format string, and optionally specify the culture as a third input where relevan

FORMAT(1759, '000000000')
formats the input number as a character string with a fixed
size of 10 characters with leading zeros, returning
'0000001759'.

Front 70

FORMAT function

Back 70

you can format an input value based on a format string, and optionally specify the culture as a third input where relevan

FORMAT(1759, '000000000')
formats the input number as a character string with a fixed
size of 10 characters with leading zeros, returning
'0000001759'.

Front 71

FORMAT function

Back 71

you can format an input value based on a format string, and optionally specify the culture as a third input where relevan

FORMAT(1759, '000000000')
formats the input number as a character string with a fixed
size of 10 characters with leading zeros, returning
'0000001759'.

Front 72

FORMAT function

Back 72

you can format an input value based on a format string, and optionally specify the culture as a third input where relevan

FORMAT(1759, '000000000')
formats the input number as a character string with a fixed
size of 10 characters with leading zeros, returning
'0000001759'.

Front 73

COALESCE function

Back 73

COALESCE function accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs. For example, the expression COALESCE(NULL, 'x', 'y') returns 'x'

ISNULL that is similar to the standard

Front 74

COALESCE function

Back 74

COALESCE function accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs. For example, the expression COALESCE(NULL, 'x', 'y') returns 'x'

ISNULL that is similar to the standard

Front 75

COALESCE function

Back 75

COALESCE function accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs. For example, the expression COALESCE(NULL, 'x', 'y') returns 'x'

ISNULL that is similar to the standard

Front 76

COALESCE function

Back 76

COALESCE function accepts a list of expressions as input and returns the first that is not NULL, or NULL if all are NULLs. For example, the expression COALESCE(NULL, 'x', 'y') returns 'x'

ISNULL that is similar to the standard

Front 77

standard NULLIF function

Back 77

function accepts two input expressions, returns NULL if they are equal, and returns the first input if they are not.
For example, consider the expression NULLIF(col1, col2). If col1 is equal to col2, the function returns a NULL; other wise, it returns the col1 value.

Front 78

standard NULLIF function

Back 78

function accepts two input expressions, returns NULL if they are equal, and returns the first input if they are not.
For example, consider the expression NULLIF(col1, col2). If col1 is equal to col2, the function returns a NULL; other wise, it returns the col1 value.

Front 79

standard NULLIF function

Back 79

function accepts two input expressions, returns NULL if they are equal, and returns the first input if they are not.
For example, consider the expression NULLIF(col1, col2). If col1 is equal to col2, the function returns a NULL; other wise, it returns the col1 value.

Front 80

standard NULLIF function

Back 80

function accepts two input expressions, returns NULL if they are equal, and returns the first input if they are not.
For example, consider the expression NULLIF(col1, col2). If col1 is equal to col2, the function returns a NULL; other wise, it returns the col1 value.

Front 81

What is the difference between NEWID and NEWSEQUENTIALID?

Back 81

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

Front 82

What is the difference between NEWID and NEWSEQUENTIALID?

Back 82

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

Front 83

What is the difference between NEWID and NEWSEQUENTIALID?

Back 83

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

Front 84

What is the difference between NEWID and NEWSEQUENTIALID?

Back 84

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

Front 85

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

Back 85

The SYSDATETIME function.

Front 86

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

Back 86

The SYSDATETIME function.

Front 87

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

Back 87

The SYSDATETIME function.

Front 88

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

Back 88

The SYSDATETIME function.

Front 89

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 quer y filters (ON, WHERE, HAVING).

Back 89

B correct: The difference between the two is that the simple form compares expressions and the searched form uses predicates

Front 90

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 quer y filters (ON, WHERE, HAVING).

Back 90

B correct: The difference between the two is that the simple form compares expressions and the searched form uses predicates

Front 91

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 quer y filters (ON, WHERE, HAVING).

Back 91

B correct: The difference between the two is that the simple form compares expressions and the searched form uses predicates

Front 92

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 quer y filters (ON, WHERE, HAVING).

Back 92

B correct: The difference between the two is that the simple form compares expressions and the searched form uses predicates

Front 93

three-valued logic (def)

Back 93

when NULLs are possible in the data, a predicate can evaluate to true, false, and unknown. This type of logic is known as three-valued logic

Front 94

three-valued logic (def)

Back 94

when NULLs are possible in the data, a predicate can evaluate to true, false, and unknown. This type of logic is known as three-valued logic

Front 95

three-valued logic (def)

Back 95

when NULLs are possible in the data, a predicate can evaluate to true, false, and unknown. This type of logic is known as three-valued logic

Front 96

empid firstname lastname country region city
------ ---------- ------------- -------- ------- ---------
1 Sara Davis USA WA Seattle
2 Don Funk USA WA Tacoma
3 Judy Lew USA WA Kirkland
4 Yael Peled USA WA Redmond
5 Sven Buck UK NULL London
6 Paul Suurs UK NULL London
7 Russell King UK NULL London

Correct query to return all employees not i WA state

Back 96

SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE region <> N'WA' OR region IS NULL;

If you use

SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE region <> N'WA';

you will get an empty set since NULL <> WA returns unknown result

Front 97

empid firstname lastname country region city
------ ---------- ------------- -------- ------- ---------
1 Sara Davis USA WA Seattle
2 Don Funk USA WA Tacoma
3 Judy Lew USA WA Kirkland
4 Yael Peled USA WA Redmond
5 Sven Buck UK NULL London
6 Paul Suurs UK NULL London
7 Russell King UK NULL London

Correct query to return all employees not i WA state

Back 97

SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE region <> N'WA' OR region IS NULL;

If you use

SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE region <> N'WA';

you will get an empty set since NULL <> WA returns unknown result

Front 98

empid firstname lastname country region city
------ ---------- ------------- -------- ------- ---------
1 Sara Davis USA WA Seattle
2 Don Funk USA WA Tacoma
3 Judy Lew USA WA Kirkland
4 Yael Peled USA WA Redmond
5 Sven Buck UK NULL London
6 Paul Suurs UK NULL London
7 Russell King UK NULL London

Correct query to return all employees not i WA state

Back 98

SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE region <> N'WA' OR region IS NULL;

If you use

SELECT empid, firstname, lastname, country, region, city
FROM HR.Employees
WHERE region <> N'WA';

you will get an empty set since NULL <> WA returns unknown result

Front 99

Predicates need to be in a form known as ________

Back 99

search argument (SARG) to allow efficient use of indexes


A predicate in the form column operator value or value operator column can be a search argument. For example, predicates like col1 = 10, and col1 > 10 are search arguments

Applying manipulation to the filtered column in most cases prevents the predicate from being a search argument. An example for manipulation of the filtered column is applying a function to it, as in F(col1) = 10, where F is some function

Front 100

Predicates need to be in a form known as ________

Back 100

search argument (SARG) to allow efficient use of indexes


A predicate in the form column operator value or value operator column can be a search argument. For example, predicates like col1 = 10, and col1 > 10 are search arguments

Applying manipulation to the filtered column in most cases prevents the predicate from being a search argument. An example for manipulation of the filtered column is applying a function to it, as in F(col1) = 10, where F is some function

Front 101

Predicates need to be in a form known as ________

Back 101

search argument (SARG) to allow efficient use of indexes

A predicate in the form column operator value or value operator column can be a search argument. For example, predicates like col1 = 10, and col1 > 10 are search arguments

Applying manipulation to the filtered column in most cases prevents the predicate from being a search argument. An example for manipulation of the filtered column is applying a function to it, as in F(col1) = 10, where F is some function

Front 102

you have a stored procedure that accepts an input parameter @dt representing an input shipped date. The procedure is supposed to return orders that were shipped on the input date. If the shippeddate column did not allow NULLs how will the query look

Back 102

SELECT orderid, orderdate, empid
FROM Sales.Orders
WHERE shippeddate = @dt;

Front 103

you have a stored procedure that accepts an input parameter @dt representing an input shipped date. The procedure is supposed to return orders that were shipped on the input date. If the shippeddate column did not allow NULLs how will the query look

Back 103

SELECT orderid, orderdate, empid
FROM Sales.Orders
WHERE shippeddate = @dt;

Front 104

you have a stored procedure that accepts an input parameter @dt representing an input shipped date. The procedure is supposed to return orders that were shipped on the input date. If the shippeddate column did not allow NULLs how will the query look

Back 104

SELECT orderid, orderdate, empid
FROM Sales.Orders
WHERE shippeddate = @dt;

Front 105

you have a stored procedure that accepts an input parameter @dt representing an input shipped date. The procedure is supposed to return orders that were shipped on the input date. If the shippeddate column did allow NULLs how will the query look

Back 105

SELECT orderid, orderdate, empid
FROM Sales.Orders
WHERE shippeddate = @dt
OR (shippeddate IS NULL AND @dt IS NULL);

Front 106

you have a stored procedure that accepts an input parameter @dt representing an input shipped date. The procedure is supposed to return orders that were shipped on the input date. If the shippeddate column did allow NULLs how will the query look

Back 106

SELECT orderid, orderdate, empid
FROM Sales.Orders
WHERE shippeddate = @dt
OR (shippeddate IS NULL AND @dt IS NULL);

Front 107

you have a stored procedure that accepts an input parameter @dt representing an input shipped date. The procedure is supposed to return orders that were shipped on the input date. If the shippeddate column did allow NULLs how will the query look

Back 107

SELECT orderid, orderdate, empid
FROM Sales.Orders
WHERE shippeddate = @dt
OR (shippeddate IS NULL AND @dt IS NULL);
---- this is a second solution that is not optimal due to manipulation of the shippeddate column causing SQL not to use SARG

Front 108

Some precedence rules determine the logical evaluation order of the different predicates. Which operator precedes which (NOT, AND and OR)

Back 108

The order is
NOT before AND/OR
AND before OR

() have the highest precedence

Front 109

Some precedence rules determine the logical evaluation order of the different predicates. Which operator precedes which (NOT, AND and OR)

Back 109

The order is
NOT before AND/OR
AND before OR

() have the highest precedence

Front 110

Some precedence rules determine the logical evaluation order of the different predicates. Which operator precedes which (NOT, AND and OR)

Back 110

The order is
NOT before AND/OR
AND before OR

() have the highest precedence

Front 111

Suppose that you need to query the Sales.Orders table and return only orders placed on February 12, 2007. You use the following query.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate = '02/12/07';
Using '02/12/2007' will interpret the date differently based on the logon default language (for British this is dec 02 2007)

How to fix this?

Back 111

1) use language-neutral literal like '20070212' which is always ymd, regardless of your languge

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate = '20070212';

2) use CONVERT or PARSE func where you can indicate how you want SQL Server to interpret the literal

CONVERT func support style number
PARSE func support indicating a culture name

Front 112

Suppose that you need to query the Sales.Orders table and return only orders placed on February 12, 2007. You use the following query.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate = '02/12/07';
Using '02/12/2007' will interpret the date differently based on the logon default language (for British this is dec 02 2007)

How to fix this?

Back 112

1) use language-neutral literal like '20070212' which is always ymd, regardless of your languge

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate = '20070212';

2) use CONVERT or PARSE func where you can indicate how you want SQL Server to interpret the literal

CONVERT func support style number
PARSE func support indicating a culture name

Front 113

Suppose that you need to query the Sales.Orders table and return only orders placed on February 12, 2007. You use the following query.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate = '02/12/07';
Using '02/12/2007' will interpret the date differently based on the logon default language (for British this is dec 02 2007)

How to fix this?

Back 113

1) use language-neutral literal like '20070212' which is always ymd, regardless of your languge

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate = '20070212';

2) use CONVERT or PARSE func where you can indicate how you want SQL Server to interpret the literal

CONVERT func support style number
PARSE func support indicating a culture name

Front 114

suppose that you need to filter only orders placed in February 2007. You can use the YEAR and MONTH functions, as in the following.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
because here you apply manipulation to the filtered column, the predicate is not considered a search argument, and therefore, SQL Server won’t be able to rely on index ordering. You could revise your predicate as a range, like the following

Back 114

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate >= '20070201' AND orderdate < '20070301';

Front 115

suppose that you need to filter only orders placed in February 2007. You can use the YEAR and MONTH functions, as in the following.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
because here you apply manipulation to the filtered column, the predicate is not considered a search argument, and therefore, SQL Server won’t be able to rely on index ordering. You could revise your predicate as a range, like the following

Back 115

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate >= '20070201' AND orderdate < '20070301';

Now that you don’t apply manipulation to the filtered column, the predicate is considered a search argument, and there’s the potential for SQL Server to rely on index ordering

Front 116

suppose that you need to filter only orders placed in February 2007. You can use the YEAR and MONTH functions, as in the following.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
because here you apply manipulation to the filtered column, the predicate is not considered a search argument, and therefore, SQL Server won’t be able to rely on index ordering. You could revise your predicate as a range, like the following

Back 116

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate >= '20070201' AND orderdate < '20070301';

Front 117

You can indicate the ordering direction on an expression-by-expression basis like

Back 117

ORDER BY col1 DESC, col2, col3 DESC (col1 descending, then col2 ascending, then col3 descending)

Front 118

You can indicate the ordering direction on an expression-by-expression basis like

Back 118

ORDER BY col1 DESC, col2, col3 DESC (col1 descending, then col2 ascending, then col3 descending)

Front 119

You can specify a percent of rows to filter (instead of number) syntax

Back 119

SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

Front 120

You can specify a percent of rows to filter (instead of number) syntax

Back 120

SELECT TOP (1) PERCENT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

Front 121

backwards compatible syntax for SELECT TOP

Back 121

SELECT TOP (3) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

T-SQL supports specifying the number of rows to filter using the TOP option in SELECT queries without parentheses, but that’s only for backward compatibility reasons. The correct syntax is with parentheses.

Front 122

backwards compatible syntax for SELECT TOP

Back 122

SELECT TOP (3) orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;

T-SQL supports specifying the number of rows to filter using the TOP option in SELECT queries without parentheses, but that’s only for backward compatibility reasons. The correct syntax is with parentheses.

Front 123

You don’t always care about guaranteeing deterministic or repeatable results for SELECT TOP; but if you do, two options are available to you. One option is to ask to include all ties with the last row by adding the WITH TIES option, as follows

Back 123

SELECT TOP (3) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
This can of course, this could result in returning more rows than you asked for

Front 124

You don’t always care about guaranteeing deterministic or repeatable results for SELECT TOP; but if you do, two options are available to you. One option is to ask to include all ties with the last row by adding the WITH TIES option, as follows

Back 124

SELECT TOP (3) WITH TIES orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC;
This can of course, this could result in returning more rows than you asked for

Front 125

OFFSET clause

FETCH clause

Back 125

indicating how many rows you want to skip (0 if you don’t want to skip any);
indicating how many rows you want to filter

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

Front 126

OFFSET clause

FETCH clause

Back 126

indicating how many rows you want to skip (0 if you don’t want to skip any);
indicating how many rows you want to filter

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

Front 127

keywords NEXT or FIRST for

FETCH NEXT and FETCH FIRST

Back 127

When skipping some rows, it might be more intuitive to you to use the keywords FETCH NEXT to indicate how many rows to filter; but when not skipping any rows, it might be more intuitive to you to use the keywords
FETCH FIRST, as follows.
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY;

Front 128

keywords NEXT or FIRST for

FETCH NEXT and FETCH FIRST

Back 128

When skipping some rows, it might be more intuitive to you to use the keywords FETCH NEXT to indicate how many rows to filter; but when not skipping any rows, it might be more intuitive to you to use the keywords
FETCH FIRST, as follows.
SELECT orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC
OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY;

Front 129

OFFSET clause doesnt require FETCH clause

Back 129

by indicating OFFSET clause you are requesting to skip some rows but then by not indicating a FETCH clause your are requesting to return all remaining rows
For example, the following query requests to skip 50 rows

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

Front 130

OFFSET clause doesnt require FETCH clause

Back 130

by indicating OFFSET clause you are requesting to skip some rows but then by not indicating a FETCH clause your are requesting to return all remaining rows
For example, the following query requests to skip 50 rows

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

Front 131

What is SQL standard OFFSET-FETCH or TOP

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

Back 131

OFFSET-FETCH

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

Front 132

What is SQL standard OFFSET-FETCH or TOP

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

Back 132

OFFSET-FETCH

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

Front 133

How do you guarantee deterministic results with TOP?

Back 133

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

Front 134

How do you guarantee deterministic results with TOP?

Back 134

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

Front 135

You are tasked with writing a query against the Production.Products table, returning the five most expensive products from category 1. Write the following query.
SELECT TOP (5) productid, unitprice
FROM Production.Products
WHERE categoryid = 1
ORDER BY unitprice DESC;
productid unitprice
---- ---------
38 263.50
43 46.00
2 19.00
1 18.00
35 18.00

Back 135

This query returns the desired result, except it doesn’t have any handling of ties. In other words, the ordering among products with the same unit price is nondeterministic. Below is the deterministic query

SELECT TOP (5) WITH TIES productid, unitprice
FROM Production.Products
WHERE categoryid = 1
ORDER BY unitprice DESC

Front 136

You are tasked with writing a query against the Production.Products table, returning the five most expensive products from category 1. Write the following query.
SELECT TOP (5) productid, unitprice
FROM Production.Products
WHERE categoryid = 1
ORDER BY unitprice DESC;
productid unitprice
---- ---------
38 263.50
43 46.00
2 19.00
1 18.00
35 18.00

Back 136

This query returns the desired result, except it doesn’t have any handling of ties. In other words, the ordering among products with the same unit price is nondeterministic. Below is the deterministic query

SELECT TOP (5) WITH TIES productid, unitprice
FROM Production.Products
WHERE categoryid = 1
ORDER BY unitprice DESC

Front 137

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 137

B. Correct: If 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 138

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 138

B. Correct: If 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 139

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 139

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 140

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 140

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 141

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 141

A. Correct: T-SQL supports indicating an OFFSET clause without a FETCH clause.
C. Correct: T-SQL supports indicating both OFFSET and FETCH clauses.

Front 142

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 142

A. Correct: T-SQL supports indicating an OFFSET clause without a FETCH clause.
C. Correct: T-SQL supports indicating both OFFSET and FETCH clauses.

Front 143

Cross Joins def

Back 143

Cross Joins aka Cartesian product

Front 144

Cross Joins def

Back 144

Cross Joins aka Cartesian product

Front 145

Inner Joins def

Back 145

inner join, you can match rows from two tables based on a predicate—usually one that compares a primary key value in one side to a foreign key value in another side.

Front 146

Inner Joins def

Back 146

inner join, you can match rows from two tables based on a predicate—usually one that compares a primary key value in one side to a foreign key value in another side.

Front 147

outer joins def

Back 147

you can request to preserve all rows from one or both sides of the join, never mind if there are matching rows in the other side based on the ON predicate.

Front 148

outer joins def

Back 148

you can request to preserve all rows from one or both sides of the join, never mind if there are matching rows in the other side based on the ON predicate.

Front 149

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

Back 149

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

Front 150

Write a query that matches customers with their respective orders, returning only matches. You are not required to return customers with no related orders

C.custid, C.companyname, O.orderid, O.orderdate from
Sales.Customer and Sales.Order

Back 150

SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid;

Front 151

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

Back 151

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

Front 152

Return only customers without orders

C.custid, C.companyname, O.orderid, O.orderdate from
Sales.Customer and Sales.Order

Back 152

SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.orderid IS NULL;

Front 153

Write a query that matches customers with their respective orders, returning only matches. You are not required to return customers with no related orders

C.custid, C.companyname, O.orderid, O.orderdate from
Sales.Customer and Sales.Order

Back 153

SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
INNER JOIN Sales.Orders AS O
ON C.custid = O.custid;

Front 154

What are the definitions for a subquery in terms of result of the subquery

Back 154

scalar
multi-valued
table-valued

Front 155

Return only customers without orders

C.custid, C.companyname, O.orderid, O.orderdate from
Sales.Customer and Sales.Order

Back 155

SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
WHERE O.orderid IS NULL;

Front 156

What subqueries can be in relation to the outer query

Back 156

self-contained - independent of the outer query
correlated - having a reference to a columnt from the table in the outer query

Front 157

What are the definitions for a subquery in terms of result of the subquery

Back 157

scalar
multi-valued
table-valued

Front 158

Self-contained subqueries (def)

Back 158

are subqueries that have no dependency on the outer query
you can highlight the inner query and run it independently

Front 159

What subqueries can be in relation to the outer query

Back 159

self-contained - independent of the outer query
correlated - having a reference to a columnt from the table in the outer query

Front 160

Correlated subqueries (def)

Back 160

are subqueries where the inner query has a reference to a column from the table in the ourter query

Front 161

Self-contained subqueries (def)

Back 161

are subqueries that have no dependency on the outer query
you can highlight the inner query and run it independently

Front 162

EXISTS predicate

Back 162

EXISTS predicate accepts a subquery as input and returns true when the subquery returns at least one row and false otherwise

the following query returns customers who placed orders on February 12, 2007.
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.orderdate = '20070212');

Front 163

Correlated subqueries (def)

Back 163

are subqueries where the inner query has a reference to a column from the table in the ourter query

Front 164

Table expression (def)

Back 164

Table expressions are named queries. You write an inner query that returns a relational result set, name it, and query it from an outer query. T-SQL supports four forms of table expressions:
■■ Derived tables
■■ Common table expressions (CTEs)
■■ Views (definition is preserved)
■■ Inline table-valued functions (definition is preserved)

Front 165

EXISTS predicate

Back 165

EXISTS predicate accepts a subquery as input and returns true when the subquery returns at least one row and false otherwise

the following query returns customers who placed orders on February 12, 2007.
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS
(SELECT * FROM Sales.Orders AS O
WHERE O.custid = C.custid
AND O.orderdate = '20070212');

Front 166

common table expression (CTE)

Back 166

a named table expression that is visible only to the statement that defines it a query against a CTE involves three main parts
■■The inner query
■■ The name you assign to the query and its columns
■■ The outer query

Front 167

Table expression (def)

Back 167

Table expressions are named queries. You write an inner query that returns a relational result set, name it, and query it from an outer query. T-SQL supports four forms of table expressions:
■■ Derived tables
■■ Common table expressions (CTEs)
■■ Views
■■ Inline table-valued functions

Front 168

CTEs sample syntax

Back 168

WITH <CTE_name>
AS
(
<inner_query>
)
<outer_query>;

Front 169

common table expression (CTE)

Back 169

a named table expression that is visible only to the statement that defines it
a query against a CTE involves three main parts
The inner query
■■ The name you assign to the query and its columns
■■ The outer query

Front 170

CTEs real example

Back 170

WITH C AS ( SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products )
SELECT categoryid, productid, productname, unitprice
FROM C WHERE rownum <= 2;
categoryid|productid|productname|unitprice
1 |24 |Product QOGNU|4.50
1 |75 |Product BWRLG |7.75
2 |3 |Product IMEHJ |10.00
2 |77 |Product LUNZZ |13.00
....

Front 171

CTEs sample syntax

Back 171

WITH <CTE_name>
AS
(
<inner_query>
)
<outer_query>;

Front 172

derived tables and CTE vs views and table-valued function

Back 172

derived tables and CTEs are table expressions that are visible only in the scope of the statement that defines them while
views and table-valued functions are stored as object in the db (being in an object in the database allows you to define permissions)

Front 173

CTEs real example

Back 173

WITH C AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products
)
SELECT categoryid, productid, productname, unitprice
FROM C
WHERE rownum <= 2;

Front 174

difference between views and inline table-valued functions is that

Back 174

views dont accept input parameters
table-valued functions do accept a parameter

Front 175

APPLY operator

Back 175

use to apply a table expression given to its as the right input to each for from a table expressing given to it as the left input
if compared to JOIN - APPLY allow the right table expression to be correlated to the left table
So conceptually, the right table expression is evaluated separately for each left row.

Front 176

CROSS APPLY operator

Back 176

operates on left and right table expressions as inputs. The right table expression can have a correlation to elements from the left table. The right table expression is applied to each row from the left input. What’s special about the CROSS APPLY operator as compared to OUTER APPLY is that if the right table expression returns an empty set for a left row, the left row isn’t returned

Front 177

OUTER APPLY operator

Back 177

does what the CROSS APPLY operator does, but also includes in the result rows from the left side that get an empty set back from the right side

Front 178

What is the difference between the APPLY and JOIN operators?

Back 178

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 179

T-SQL supports three set operators (list)

Back 179

UNION,
INTERSECT, and
EXCEPT; it also supports one multiset operator: UNION ALL

Front 180

What is the difference between UNION and UNION ALL

Back 180

if you want to keep duplicates you use UNION ALL
otherwise use UNION

Front 181

Set operators precedence:

Back 181

INTERSECT
UNION, EXCEPT (considered equal)

Front 182

HAVING vs WHERE (def)

Back 182

unlike the WHERE clause, which evaluated at the row level
HAVING clauses uses a predicate per group as opposed to per row

Front 183

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 183

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

Front 184

from a logical query processing perspective what is the order of clause execution

Back 184

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Front 185

Following query

SELECT S.shipperid, S.companyname, COUNT(*) AS numorders
FROM Sales.Shippers AS S
JOIN Sales.Orders AS O
ON S.shipperid = O.shipperid
GROUP BY S.shipperid;
------------ returns -----------------
Msg 8120, Level 16, State 1, Line 1
Column 'Sales.Shippers.companyname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
---------------- WHY ----------------

Back 185

S.companyname column neither appear in the GROUP BY list nor is it contained in an aggregate function, it's not allowed in the HAVING, SELECT and ORDER BY clauses
----------- WORKAROUND ----------------
SELECT S.shipperid, S.companyname,
COUNT(*) AS numorders
FROM Sales.Shippers AS S
INNER JOIN Sales.Orders AS O
ON S.shipperid = O.shipperid
GROUP BY S.shipperid, S.companyname;

Front 186

T-SQL supports three clauses that allow defined multiple define multiple grouping sets in the same query (list):

Back 186

GROUPING SETS
CUBE
ROLLUP

Front 187

You can use the GROUPING SETS clause to list all grouping sets that you want to define in the query. As an example, the following query defines four grouping sets.

Back 187

SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY GROUPING SETS
(
( shipperid, YEAR(shippeddate) ),
( shipperid ),
( YEAR(shippeddate) ),
( )
);

Front 188

CUBE clause

Back 188

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
SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY CUBE( shipperid, YEAR(shippeddate) );
The CUBE clause defines all four possible grouping sets from the two inputs:
1. ( shipperid, YEAR(shippeddate) )
2. ( shipperid )
3. ( YEAR(shippeddate) )
4. ( )

Front 189

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 189

D Correct: A grouped query returns only one row per group. For this reason, all expressions that appear in phases that are evaluated after the GROUP BY clause
(HAVING, SELECT, and ORDER BY) must guarantee returning a single value per group. That’s where the restriction comes from.

Front 190

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 190

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

GROUPING SETS, CUBE, and ROLLUP.

Front 191

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 191

Correct Answers: B, C, and D

Front 192

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 192

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.

Front 193

Pivoting def
Unpivoting def

Back 193

Pivoting is a specialized case of grouping and aggregating of data.
When you pivot data, you need to identify three things: the grouping element, spreading element, and aggregation element

Unpivoting is, in a sense, the inverse of pivoting.

Front 194

all pivot queries, you need to identify three elements

Back 194

¡¡ What do you want to see on rows? This element is known as the on rows, or grouping element.
¡¡ What do you want to see on columns? This element is known as the on cols, or spreading element.
¡¡ What do you want to see in the intersection of each distinct row and column value? This element is known as the data, or aggregation element.

Front 195

PIVOT general syntax

Back 195

WITH PivotData AS (
SELECT
custid , -- grouping column
shipperid, -- spreading column
freight -- aggregation column
FROM Sales.Orders )
SELECT custid, [1], [2], [3]
FROM PivotData
PIVOT(SUM(freight) FOR shipperid IN ([1],[2],[3]) ) AS P;

custid 1 2 3
------- -------- -------- --------
1 95.03 61.02 69.53
2 43.90 NULL 53.52
3 63.09 116.56 88.87
4 41.95 358.54 71.46
5 189.44 1074.51 295.57
6 0.15 126.19 41.92
7 217.96 215.70 190.00
8 16.16 175.01 NULL
9 341.16 419.57 597.14
10 129.42 162.17 502.36

Front 196

What function isn't allowed as the aggregate function used by the PIVOT operator?

PIVOT operator limitation for aggregate functions usage?

Back 196

COUNT(*)
you can use COUNT(<col name>)

PIVOT operator is limited to using only one aggregate functions

Front 197

UNPIVOT general syntax

Back 197

SELECT < column list >, < names column >, < values column >
FROM < source table >
UNPIVOT( < values column > FOR < names column > IN( <source columns> ) ) AS U;

Front 198

What UNPIVOT does with rows that contain NULL values?

What type of language constructs are PIVOT and UNPIVOT implemented as?

Back 198

Teh assumption is htat hose represent inapplicable cases

PIVOT and UNPIVOT are implemented as table operators.

Front 199

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 199

Correct answer all

Front 200

Window aggregate functions list

Back 200

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
expression SUM(val) OVER() represents the grand total of all rows in the underlying query
SUM(val) OVER(PARTITION BY custid) represents the current customer’s total.

Front 201

SELECT custid, orderid,
val,
SUM(val) OVER(PARTITION BY custid) AS custtotal,
SUM(val) OVER() AS grandtotal
FROM Sales.OrderValues;

query against the Sales.OrderValues view returning for each order the customer ID, order ID, and order value; using window functions, the query also returns the grand total of all values and the customer total

Back 201

custid orderid val custtotal grandtotal
------- -------- ------- ---------- -----------
1 ||10643| 814.50| 4273.00| 1265793.22
1 ||10692| 878.00| 4273.00| 1265793.22
1 ||10702| 330.00| 4273.00| 1265793.22
1 ||10835| 845.80| 4273.00| 1265793.22
1 ||10952| 471.20| 4273.00| 1265793.22
1 ||11011| 933.50| 4273.00| 1265793.22
2 ||10926| 514.40| 1402.95| 1265793.22
2 ||10759| 320.00| 1402.95| 1265793.22

Front 202

Window aggregate functions support another filtering option called framing (def)

Back 202

define ordering within the partition by using a window order clause, and then based on that order, you can confine a frame of rows between two delimiters
You define the delimiters by using a window frame clause.
The window frame clause requires a window order clause to be present because a set has no order, and without order, limiting rows between two delimiters would have no meaning.

Front 203

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 203

D Correct: The type of the values column is the same as the type of the columns that you unpivot, and therefore they must all have a common type.

B. Incorrect: The type of the values column is not necessarily always an NVARCHAR(128)—that’s the case with the names column.

Front 204

ROWS window frame unit, you can indicate the delimiters as one of three options:

Back 204

■■ UNBOUNDED PRECEDING or FOLLOWING, meaning the beginning or end of the partition,
respectively
■■ CURRENT ROW, obviously representing the current row
■■ <n> ROWS PRECEDING or FOLLOWING, meaning n rows before or after the current,
respectively

Front 205

suppose that you wanted to query the Sales.OrderValues view and compute the running total values from the beginning of the current customer’s activity until the current order.
You need to use the SUM aggregate
You partition the window by custid
You order the window by orderdate, orderid.
You then frame the rows from the beginning of the partition (UNBOUNDED PRECEDING) until the current row

Back 205

SELECT custid, orderid, orderdate, val,
SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runningtotal

custid orderid orderdate val runningtotal
------- -------- ----------- ------- -------------
1 10643 2007-08-25 814.50 814.50
.
.
1 11011 2008-04-09 933.50 4273.00
2 10308 2006-09-18 88.80 88.80
.
.
2 10926 2008-03-04 514.40 1402.95

Front 206

you need to filter the result of the last query, returning only those rows where the running total is less than 1,000.00. The following code achieves this by defining a common table expression (CTE)
Because window functions are supposed to operate on the underlying query’s result set, they are allowed only in the SELECT and ORDER BY clauses. If you need to refer to the result of a window function in any clause that is evaluated before the SELECT clause, you need to use a table expression

Back 206

WITH RunningTotals AS (
SELECT custid, orderid, orderdate, val,
SUM(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS runningtotal
FROM Sales.OrderValues )
SELECT *
FROM RunningTotals
WHERE runningtotal < 1000.00;

custid orderid orderdate val runningtotal
------- -------- ----------- ------- -------------
1 10643 2007-08-25 814.50 814.50
2 10308 2006-09-18 88.80 88.80
2 10625 2007-08-08 479.75 568.55
2 10759 2007-11-28 320.00 888.55
3 10365 2006-11-27 403.20 403.20

Front 207

example for a window frame extent, if you wanted the frame to include only the last three rows, you would use the form

Back 207

ROWS BETWEEN 2 PRECEDING AND CURRENT
ROW.

Front 208

ROWS vs. RANGE

Back 208

In SQL Server 2012, the ROWS option usually gets optimized much better than RANGE when using the same delimiters

Front 209

Window Ranking Functions

Back 209

window ranking functions, you can rank rows within a partition based on specified ordering
T-SQL supports four window ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE.

Front 210

Ranking functions example

Back 210

SELECT custid, orderid, val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum,
RANK() OVER(ORDER BY val) AS rnk,
DENSE_RANK() OVER(ORDER BY val) AS densernk,
NTILE(100) OVER(ORDER BY val) AS ntile100
FROM Sales.OrderValues;

Front 211

ROW_NUMBER function def

Back 211

computes a unique sequential integer starting with 1 within the window partition based on the window ordering.
Function is not deterministic, and if there is no explicit tiebreaker which row gets which number is based on optimization
SELECT custid, orderid, val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM Sales.OrderValues;

Front 212

RANK function def

Back 212

RANK function returns the number of rows in the partition that have a lower ordering value than the current, plus 1. For example, consider the rows in the sample query’s result that have an ordering value of 45.00. Nine rows have ordering values that are lower than 45.00; hence, these rows got the rank 10
(9 + 1).
111345

Front 213

DENSE_RANK function

Back 213

returns the number of distinct ordering values that are lower than the current, plus 1. For example, the same rows that got the rank 10 got the dense rank 9. That’s because these rows have an ordering value 45.00, and there are eight distinct ordering values that are lower than 45.00
1112223333

Front 214

NTILE function

Back 214

you can arrange the rows within the partition in a requested number of equally sized tiles, based on the specified ordering. You specify the desired number of tiles as input to the function. In the sample query, you requested 100 tiles. There are 830 rows in the result set, and hence the base tile size is 830 / 100 = 8 with a remainder of 30. Because there is a remainder of 30, the first 30 tiles are assigned with an additional row.

Front 215

T-SQL supports the following window offset functions

Back 215

LAG, LEAD, FIRST_VALUE, and LAST_VALUE.

Front 216

SELECT country, YEAR(hiredate) AS yearhired
FROM HR.Employees
WHERE yearhired >= 2003;
This query fails with the following error.

Msg 207, Level 16, State 1, Line 3
Invalid column name 'yearhired'.

Why?

Back 216

A typical mistake made by people who don’t understand logical query processing is attempting to refer in the WHERE clause to a column alias defined in the SELECT clause. This isn’t allowed because the WHERE clause is evaluated before the SELECT clause.

Front 217

main query clauses specified in the order that you are supposed to type them (known as “keyed-in order”):

logical query processing order of the six main query clauses:

Back 217

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

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

Front 218

What is the difference between
CAST, CONVERT, PARSE and
TRY_CAST, TRY_CONVERT, TRY_PARSE

Back 218

Those without TRY fail if the value isn't convertible
Those with TRY return a NULL if the value isn't convertible

Front 219

CASE Example

Back 219

SELECT productid, productname, unitprice, discontinued,
CASE discontinued
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END AS discontinued_desc
FROM Production.Products;

Front 220

typical use of COALESCE is to substitute a NULL with something else give example

Back 220

COALESCE(region, '') returns region if it’s not NULL and returns an empty string if it is NULL.

Front 221

Two tables T1 and T2 and you need to join them based on a match between T1.col1 and T2.col1. The attributes do allow NULLs (col1 and col2 are integer)

you might try to use the predicate COALESCE(T1.col1, -1) = COALESCE(T2. col1, -1), or ISNULL(T1.col1, -1) = ISNULL(T2.col1, -1)

What is the problem?

Back 221

because you apply manipulation to the attributes you’re comparing, SQL Server will not rely on index ordering. This can result in not using available indexes efficiently. Instead, it is recommended to use the longer form:
T1.col1 = T2.col1 OR (T1.col1 IS NULL AND T2.col1 IS NULL),

Front 222

IIF(<predicate>, <true_result>, <false_or_unknown_result>)
is equivalent
IIF is non-standard used for MS Access

Back 222

CASE WHEN <predicate> THEN <true_result> ELSE <false_or_unknown_result> END

Front 223

CHOOSE function allows

is non-standard used for MS Access

Back 223

CHOOSE(<pos>, <exp1>, <exp2>, …, <expn>)

example, the expression CHOOSE(2, 'x', 'y', 'z') returns 'y'

Front 224

way to compute the end of the current month

way to compute the end of the current year

Back 224

SELECT EOMONTH(SYSDATETIME()) AS end_of_current_month

SELECT DATEFROMPARTS(YEAR(SYSDATETIME()), 12, 31) AS end_of_current_year;

Front 225

Write a query against the Production.Products table that returns the existing numeric product ID, in addition to the product ID formatted as a fixed-sized string with 10 digits with leading zeros. For example, for product ID 42, you need to return the string '0000000042'.

Back 225

SELECT productid,
FORMAT(productid, 'd10') AS str_productid
FROM Production.Products;

Front 226

Optimize wiht search arguments following query
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;

Back 226

because here you apply manipulation to the filtered column, the predicate is not considered a search argument, and therefore, SQL Server won’t be able to rely on index ordering. You could revise your predicate as a range, like the following.
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate >= '20070201' AND orderdate < '20070301';
Now that you don’t apply manipulation to the filtered column, the predicate is considered a search argument, and there’s the potential for SQL Server to rely on index ordering.

Front 227

Standard SQL supports the options _________________ and ______________________ to control how NULLs sort, but T-SQL doesnt support this optiopn

According to standard SQL, a query with an ORDER BY clause conceptually returns a ___________ not a relation

Back 227

NULL FIRST and
NULL LAST

cursor

Front 228

Note that the form '2007-02-12' is considered language-neutral only for the data types

Back 228

types DATE, DATETIME2, and DATETIMEOFFSET

this form is considered language-dependent for the types DATETIME and SMALLDATETIME

Front 229

suppose that you need to filter only orders placed in
February 2007. You can use the YEAR and MONTH functions, as in the following

Back 229

SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
However, because here you apply manipulation to the filtered column, the predicate is not considered a search argument
SELECT orderid, orderdate, empid, custid
FROM Sales.Orders
WHERE orderdate >= '20070201' AND orderdate < '20070301';

Front 230

You can sort by elements that do not appear in the SELECT list unless the ______________ clause is also specified

Back 230

DISTINCT

Front 231

In addition to supporting the syntax for cross joins with the CROSS JOIN keyword both SQL and TSQL support an older syntax where you specify _______________

Back 231

a comma between the names as in FROM T1, T2

Front 232

When talking about INNER JOIN
T1 JOIN T2 is equal to

Back 232

T1 INNER JOIN T2
also
LEFT OUTER JOIN <> LEFT JOIN
RIGHT OUTER JOIN <> RIGHT JOIN
FULL OUTER JOIN <> FULL JOIN

Front 233

LEFT OUTER JOIN

Back 233

LEFT OUTER JOIN (or LEFT JOIN) preserves the left table
returns rows from the left that had no matches in the right table (call those outer rows), with NULLs used as placeholders in the right side

Front 234

What plays matching role in an OUTER JOIN

Back 234

the ON clause play a matching role aka a row in the preserved side will be returned whether the ON predicate finds a match for it or not
WHERE clause plays a final filtering role to determine which rows will be presented

Front 235

RIGHT OUTER JOIN

FULL OUTER JOIN

Back 235

RIGHT OUTER JOIN (or RIGHT JOIN in short) preserves the right side

T-SQL also supports a full outer join (FULL OUTER JOIN, or FULL JOIN in short), that preserves both sides.

Front 236

multi-join query evaluates the joins conceptually from

Back 236

left to right, result of one join is used as the left input to the next join.

Front 237

Note that if what’s supposed to be a scalar subquery returns in practice more than one value,

Back 237

the code fails at run time
if the scalar subquery returns an empty set, it is converted to a NULL.

Front 238

Requirements for the internal query when working with TE

Back 238

because a table expression is supposed to represent a relation, the inner query defining it needs to be
relational
- all columns returned by inner query need name
- all columns must be unique
- inner query is not allowed ORDER BY

Front 239

Derived Tables def

Back 239

a form of TE that closely resembles a subquery that returns an entire table result but a subquery that returns an entire table result
You define the derived table’s inner query in parentheses in the FROM clause of the outer query, and specify the name of the derived table after the parentheses.

Front 240

SELECT categoryid, productid, productname, unitprice
FROM (SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products) AS D
WHERE rownum <= 2;

Back 240

the derived table is defined in the FROM clause of the outer query in parentheses, followed by the derived table name.
Then the outer query is allowed to refer to column aliases that were assigned by the inner query.

Front 241

You don’t nest CTEs like you do derived tables. If you need to define multiple CTEs, you simply separate them by commas.
Sample code

Back 241

WITH C1 AS (
SELECT ...
FROM T1
WHERE ...
),
C2 AS (
SELECT
FROM C1
WHERE ...
)
SELECT ...
FROM C2
WHERE ...;

Front 242

CTEs also have a recursive form sample

Back 242

The body of the recursive query has two or more queries, usually separated by a UNION ALL operator

WITH EmpsCTE AS (
SELECT empid, mgrid, firstname, lastname, 0 AS distance
FROM HR.Employees
WHERE empid = 9
UNION ALL
SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
FROM EmpsCTE AS S
JOIN HR.Employees AS M
ON S.mgrid = M.empid )
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;

Front 243

notes about recursive form of CTE

Back 243

1 of the query is called anchor member - returns a valid relational result, invoked once
At least 1 of the query is called recursive member - has a reference to the CTE name, invoked repeatedly until it returns an empty result set

Front 244

example for a typical correlated sub-query. In this example we are finding the list of employees (employee number and names) having more salary than the average salary of all employees in that employee's department.

Back 244

ELECT employee_number, name
FROM employee AS e1
WHERE salary > (SELECT avg(salary)
FROM employee
WHERE department = e1.department);

Front 245

sample TE

Back 245

SELECT categoryid, productid, productname, unitprice
FROM (SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products) AS D
WHERE rownum <= 2;

Front 246

Syntax to crate a view

Back 246

CREATE VIEW Sales.RankedProducts
AS
SELECT
ROW_NUMBER() OVER(PARTITION BY categoryid
ORDER BY unitprice, productid) AS rownum,
categoryid, productid, productname, unitprice
FROM Production.Products;
GO
Note that it’s not the result set of the view that is stored in the database; rather, only its definition is stored.
SELECT categoryid, productid, productname, unitprice
FROM Sales.RankedProducts
WHERE rownum <= 2;

Front 247

inline table-valued functions def

Back 247

similar to view in concepts , however as mentioned, they do support input parameters

Front 248

inline table-valued functions sample

Back 248

CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE AS
RETURN
WITH EmpsCTE AS (
SELECT empid, mgrid, firstname, lastname, 0 AS distance
FROM HR.Employees
WHERE empid = @empid
UNION ALL
SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
FROM EmpsCTE AS S
JOIN HR.Employees AS M
ON S.mgrid = M.empid )
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE; GO

SELECT *
FROM HR.GetManagers(9) AS M;

Front 249

APPLY operator

Back 249

powerful operator that you can use to apply a table expression given to it as the right input to each row from a table expression given to it as the left input
The two forms of the APPLY operator—CROSS and OUTER.

Front 250

Suppose you query a table with an attribute called fullname formatted as '<first> <last>', and you need to write an expression that extracts the first name part

PATINDEX func. expl.

Back 250

LEFT(fullname, CHARINDEX(' ', fullname) -1)

PATINDEX used for when you are looking for a pattern in a string

Front 251

The following example finds the position at which the pattern ensure starts in a specific row of the DocumentSummary column in the Document table.

Back 251

USE AdventureWorks2012;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentNode = 0x7B40;
GO

Front 252

set operators follows a number of guidelines

Back 252

1)Number of columns in the queries has to be the same and the column types of corresponding columns need to be compatible
2)Two NULLs are considered equal
3)Individual queries are not allow to have ORDER BY as we are working with sets
4)Column names of the result columns are determined by the first query

Front 253

UNION/UNION ALL operator

Back 253

UNION set operator unifies the results of the two input queries
UNION has an implied DISTINCT
UNION ALL unifies the results of the two input queries, but doesn't try to eliminate duplicates

Front 254

INTERSECT operator

Back 254

INTERSECT operator returns only distinct rows that are common to boht sets

Front 255

EXCEPT operator

Back 255

EXCEPT operator performs set difference. It returns distinct rows that appear in the first query but not in the second
With EXCEPT the order of the input query matters

Front 256

Set operators have precedence

Back 256

INTERSECT > UNION = EXCEPT

Front 257

You have a grouping set
SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY GROUPING SETS
(
( shipperid, YEAR(shippeddate) ),
( shipperid ),
( YEAR(shippeddate) ),
( )
);
How can you achieve the same functionality?

Back 257

By writing four separate grouped queries - each defining only single grouping set - and unifying their results with a UNION ALL operator

Front 258

Write following GROUPING SET query by using CUBE clause
SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY GROUPING SETS
(
( shipperid, YEAR(shippeddate) ),
( shipperid ),
( YEAR(shippeddate) ),
( )
);

Back 258

SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY CUBE( shipperid, YEAR(shippeddate) );

Front 259

ROLLUP clause def

Back 259

also an abbreviation of the GROUPING SETS clause, but you use it when there’s a hierarchy formed by the input elements

SELECT shipcountry, shipregion, shipcity, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY ROLLUP( shipcountry, shipregion, shipcity );

Front 260

GROUPING function

Back 260

accepts a single element as input and returns 0 when the element is part of the grouping set and 1 when it isn’t. The following query demonstrates using the GROUPING function.
SELECT
shipcountry, GROUPING(shipcountry) AS grpcountry,
shipregion , GROUPING(shipregion) AS grpcountry,
shipcity , GROUPING(shipcity) AS grpcountry,
COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY ROLLUP( shipcountry, shipregion, shipcity );

shipcountry grpcountry shipregion grpcountry shipcity grpcountry numorders
Argentina 0 NULL 0 Buenos Aires 0 16
Argentina 0 NULL 0 NULL 1 16
Argentina 0 NULL 1 NULL 1 16

Front 261

So remember, in every unpivoting task, you need to identify the three elements involved:
■■ The set of source columns that you’re unpivoting (in this case, [1],[2],[3])
■■ The name you want to assign to the target values column (in this case, freight)
■■ The name you want to assign to the target names column (in this case, shipperid)

Back 261

SELECT < column list >, < names column >, < values column >
FROM < source table >
UNPIVOT( < values column > FOR < names column > IN( <source columns> ) ) AS U;

Front 262

custid 1 2 3
------- -------- -------- --------
1 95.03 61.02 69.53
2 43.90 NULL 53.52
...
SELECT custid, shipperid, freight
FROM Sales.FreightTotals
UNPIVOT( freight FOR shipperid IN([1],[2],[3]) ) AS U;
Result

Back 262

custid shipperid freight
------- ----------- --------
1 1 95.03
1 2 61.02
1 3 69.53
...

UNPIVOT operator filters out rows with NULLs in the value column (freight in this case).

Front 263

Window offset functions def

Back 263

Window offset functions return an element from a single row that is in a given offset from the current row in the window partition, or from the first or last row in the window frame. T-SQL supports the following window offset functions: LAG, LEAD, FIRST_VALUE, and LAST_VALUE
LAG and LEAD functions rely on an offset with respect to the current row
FIRST_VALUE and LAST_VALUE functions operate on the first or last row in the frame

Front 264

following query uses the LAG and LEAD functions to return along with each order the value of the previous customer’s order, in addition to the value from the next customer’s order.

Back 264

SELECT custid, orderid, orderdate, val,
LAG(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS prev_val,
LEAD(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid) AS next_val
FROM Sales.OrderValues;
custid orderid orderdate val prev_val next_val
------- -------- ----------- ------- --------- ---------
1 | 10643|2007-08-25| 814.50| NULL | 878.00
1 |10692 |2007-10-03| 878.00| 814.50| 330.00
1 |10702 |2007-10-13| 330.00| 878.00| 845.80
1 |10835 |2008-01-15| 845.80| 330.00| 471.20
1 |10952 |2008-03-16| 471.20| 845.80| 933.50
1 |11011 |2008-04-09| 933.50| 471.20|NULL
2 |10308 |2006-09-18| 88.80 |NULL |479.75

Front 265

FIRST_VALUE and LAST_VALUE functions return a value expression from the first or last
rows in the window frame, respectively. Naturally, the functions support window partition,
order, and frame clauses. As an example, the following query returns along with each order
the values of the customer’s first and last orders.

Back 265

SELECT custid, orderid, orderdate, val,
FIRST_VALUE(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS first_val,
LAST_VALUE(val) OVER(PARTITION BY custid
ORDER BY orderdate, orderid
ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING) AS last_val
FROM Sales.OrderValues;
custid orderid orderdate val first_val last_val
------- -------- ----------- ------- ---------- ----------
1 |11011 |2008-04-09 |933.50| 814.50| 933.50
1 |10952 |2008-03-16 |471.20| 814.50| 933.50
1 |10835 |2008-01-15 |845.80| 814.50| 933.50
1 |10702 |2007-10-13 |330.00| 814.50| 933.50
1 |10692 |2007-10-03 |878.00| 814.50| 933.50
1 |10643 |2007-08-25 |814.50| 814.50| 933.50
2 |10926 |2008-03-04 |514.40| 88.80 | 514.40
2 |10759 |2007-11-28 |320.00| 88.80 | 514.40

Front 266

Simple terms search
Prefix terms search
Generation terms search
Proximity terms search

Back 266

Simple terms search - one or more specific phrases
Prefix terms search - terms the words or phrases begin with
Generation terms search - meaning inflection forms of words
Proximity terms search - words or phrases closes to another word

Front 267

Thesaurus terms search
Weighted terms search
Statistical semantic search
Similar documents search

Back 267

Thesaurus terms search - synonymous of a word
Weighted terms search - words or phrases that use values with your custom weight
Statistical semantic search - key phrases in a document
Similar documents search - similarity is defined by semantic key phrases

Front 268

can check whether Full-Text Search is installed by using the following query

You can create full-text indexes on columns of type

Back 268

SELECT SERVERPROPERTY('IsFullTextInstalled');

You can create full-text indexes on columns of type CHAR, VARCHAR, NCHAR, NVARCHAR, TEXT, NTEXT, IMAGE

Front 269

You need appropriate filters for documents. Filters, called ifilters in full-text terminology, extract the textual information and remove formatting from the documents. You can check which filters are installed in your instance by using the following query.

Back 269

EXEC sys.sp_help_fulltext_system_components 'filter';
or
SELECT document_type, path
FROM sys.fulltext_document_types;

Front 270

You can use the following query to check which languages are supported in SQL Server.

Back 270

SELECT lcid, name
FROM sys.fulltext_languages
ORDER BY name;

Front 271

You can prevent indexing such noise words by creating stoplists of stopwords. You can check current stopwords and stoplists in your database by using the following queries.

Back 271

SELECT stoplist_id, name
FROM sys.fulltext_stoplists;
SELECT stoplist_id, stopword, language
FROM sys.fulltext_stopwords;

Front 272

After you edit the thesaurus file for a specific language, you must load it with the following system procedure call.

Back 272

EXEC sys.sp_fulltext_load_thesaurus_file 1033;

Front 273

CONTAINS Predicate

Back 273

■■ Words and phrases in text
■■ Exact or fuzzy matches
■■ Inflectional forms of a word
■■ Text in which a search word is close to another search word
■■ Synonyms of a searched word
■■ A prefix of a word or a phrase only

Front 274

FREETEXT Predicate

Back 274

FREETEXT predicate is less specific and thus returns more rows than the CONTAINS predicate. It searches for the values that match the meaning of a phrase and not just exact words.

Front 275

FOR XML RAW

Back 275

SELECT custid, orderid, orderdate FROM Sales.Orders FOR XML RAW
<row custid="85" orderid="10248" orderdate="2006-07-04T00:00:00" />
<row custid="79" orderid="10249" orderdate="2006-07-05T00:00:00" />
<row custid="34" orderid="10250" orderdate="2006-07-08T00:00:00" />
The XML creates is quite close to the relational presentation of the data. In RAW mode, every row from returned rowset converts to a single element named row, and columns translate to the attributes of this element.

Front 276

FOR XML RAW enhance the RAW mode by renaming the row element

Back 276

SELECT TOP 3 custid as Temp, orderid, orderdate FROM Sales.Orders FOR XML RAW
<row Temp="85" orderid="10248" orderdate="2006-07-04T00:00:00" />
<row Temp="79" orderid="10249" orderdate="2006-07-05T00:00:00" />
<row Temp="34" orderid="10250" orderdate="2006-07-08T00:00:00" />

Front 277

FOR XML AUTO def

Back 277

FOR XML AUTO option gives you nice XML documents with nested elements, and it is not complicated to use.

Front 278

ELEMENTS keyword
WITH NAMESPACES clause
XMLSCHEMA directive
def In RAW and AUTO mode

Back 278

ELEMENTS - used to produce element-centric XML
WITH NAMESPACES clause, preceding the SELECT part of the query, defines namespaces and aliases in the returned XML
XMLSCHEMA directive allows you to return the XSD scheme

Front 279

With the last two flavors of the FOR XML clause —the EXPLICIT and PATH options—you can manually define the XML returned

Back 279

EXPLICIT mode is included for backward compatibility only; it uses proprietary T-SQL syntax for formatting XML.
PATH mode uses standard XML XPath expressions to define the elements and attributes of the XML you are creating.

Front 280

Here is an example of a simple XPATH query

Back 280

SELECT Customer.custid AS [@custid],
Customer.companyname AS [companyname]
FROM Sales.Customers AS Customer
WHERE Customer.custid <= 2
ORDER BY Customer.custid
FOR XML PATH ('Customer'), ROOT('Customers');
The query returns the following output.
<Customers>
<Customer custid="1">
<companyname>Customer NRZBB</companyname>
</Customer>
<Customer custid="2">
<companyname>Customer MLTDN</companyname>
</Customer>
</Customers>

Front 281

Return the custid and companyname columns from the Sales.Customers table, and orderid and orderdate columns from the Sales.Orders table. Make the result element-centric by using TK461-CustomersOrders as the namespace and CustomersOrders as the root element. You can use the following code.
WITH XMLNAMESPACES('TK461-CustomersOrders' AS co)
SELECT [co:Customer].custid AS [co:custid],
[co:Customer].companyname AS [co:companyname],
[co:Order].orderid AS [co:orderid],
[co:Order].orderdate AS [co:orderdate]
FROM Sales.Customers AS [co:Customer]
INNER JOIN Sales.Orders AS [co:Order]
ON [co:Customer].custid = [co:Order].custid
ORDER BY [co:Customer].custid, [co:Order].orderid
FOR XML AUTO, ELEMENTS, ROOT('CustomersOrders');

Back 281

<CustomersOrders xmlns:co="TK461-CustomersOrders">
<co:Customer>
<co:custid>1</co:custid>
<co:companyname>Customer NRZBB</co:companyname>
<co:Order>
<co:orderid>10643</co:orderid>
<co:orderdate>2007-08-25T00:00:00</co:orderdate>
</co:Order>
<co:Order>
<co:orderid>10692</co:orderid>
<co:orderdate>2007-10-03T00:00:00</co:orderdate>
</co:Order>
...
</co:Customer>
<co:Customer>
<co:custid>2</co:custid>
<co:companyname>Customer MLTDN</co:companyname>
<co:Order>
<co:orderid>10308</co:orderid>
<co:orderdate>2006-09-18T00:00:00</co:orderdate>
</co:Order>
..
</co:Customer>

Front 282

Return the third XML as a fragment, not as a document. Return the top element Customer with custid and companyname attributes. Return the Order nested element with orderid and orderdate attributes. Use the FOR XML PATH clause for explicit formatting of XML. You can use the following code.

Back 282

SELECT Customer.custid AS [@custid],
Customer.companyname AS [@companyname],
(SELECT [Order].orderid AS [@orderid],
[Order].orderdate AS [@orderdate]
FROM Sales.Orders AS [Order]
WHERE Customer.custid = [Order].custid
AND [Order].orderid %2 = 0
ORDER BY [Order].orderid
FOR XML PATH('Order'), TYPE)
FROM Sales.Customers AS Customer
WHERE Customer.custid <= 2
ORDER BY Customer.custid
FOR XML PATH('Customer');

Front 283

XQuery user-defined functions are ___ supported in SQL Server

Back 283

XQuery user-defined functions are not supported in SQL Server because you already have T-SQL and CLR functions available.

Front 284

The following query uses the aggregate functions count() and max() to retrieve information about orders for each customer in an XML document.

Back 284

SELECT @x.query('
for $i in //Customer
return
<OrdersInfo>
{ $i/@companyname }
<NumberOfOrders>
{ count($i/Order) }
</NumberOfOrders>
<LastOrder>
{ max($i/Order/@orderid) }
</LastOrder>
</OrdersInfo>
');

Front 285

/x/y[1] means the

(/x/y)[1] means the

Back 285

first y child element of each x element

first element out of all nodes selected by x/y.

Front 286

xQuery Boolean predicates select all nodes for which the predicate evaluates to true. They work on both atomic values and sequences. For sequences, if one atomic value in a sequence leads to a true exit of the expression, the whole expression is evaluated to
true. Look at the following example.

DECLARE @x AS XML = N'';
SELECT @x.query('(1, 2, 3) = (2, 4)');
SELECT @x.query('(5, 6) < (2, 4)');
SELECT @x.query('(1, 2, 3) = 1');
SELECT @x.query('(1, 2, 3) != 1');

Back 286

DECLARE @x AS XML = N'';
SELECT @x.query('(1, 2, 3) = (2, 4)'); -- true 2 is in both
SELECT @x.query('(5, 6) < (2, 4)'); -- false no value in first sequence is less than value in second sequence
SELECT @x.query('(1, 2, 3) = 1'); -- true there is an atomic value in first sequence equal to an atomic value in second
SELECT @x.query('(1, 2, 3) != 1'); -- true this equence is both equal and not equal to atomic value 1

Front 287

following example shows usage of value comparison operators

Back 287

DECLARE @x AS XML = N'';
SELECT @x.query('(5) lt (2)'); -- false
SELECT @x.query('(1) eq 1'); -- true
SELECT @x.query('(1) ne 1'); -- false
GO
DECLARE @x AS XML = N'';
SELECT @x.query('(2, 2) eq (2, 2)'); -- error
GO

Front 288

XQuery also supports conditional if..then..else expressions with the following syntax.

Back 288

if (<expression1>)
then
<expression2>
else
<expression3>

Front 289

The following code shows usage of a conditional expression

Back 289

DECLARE @x AS XML = N'
<Employee empid="2">
<FirstName>fname</FirstName>
<LastName>lname</LastName>
</Employee>
';
DECLARE @v AS NVARCHAR(20) = N'FirstName';
SELECT @x.query('
if (sql:variable("@v")="FirstName") then
/Employee/FirstName
else
/Employee/LastName
') AS FirstOrLastName;
GO

Front 290

FLWOR Expressions abr

Back 290

For
Let
Where
Order by
Return

Front 291

Here is an example of usage of all FLWOR clauses.

Back 291

SELECT @x.query('for $i in CustomersOrders/Customer/Order
let $j := $i/orderdate
where $i/@orderid < 10900
order by ($j)[1]
return
<Order-orderid-element>
<orderid>{data($i/@orderid)}</orderid>
{$j}
</Order-orderid-element>')
AS [Filtered, sorted and reformatted orders with let clause];

The query iterates, as you can see from the for clause, through all Order nodes using an iterator variable and returns those nodes. The name of the iterator variable must start with a dollar sign ($) in XQuery. The where clause limits the Order nodes processed to those with an orderid attribute smaller than 10900.

Front 292

DECLARE @x AS XML;
SET @x = N'
<CustomersOrders>
<Customer custid="1">
<!-- Comment 111 -->
<companyname>Customer NRZBB</companyname>
<Order orderid="10692">
<orderdate>2007-10-03T00:00:00</orderdate>
</Order>
<Order orderid="10702">
<orderdate>2007-10-13T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Order>
</Customer>
<Customer custid="2">
<!-- Comment 222 -->
<companyname>Customer MLTDN</companyname>
<Order orderid="10308">
<orderdate>2006-09-18T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-04T00:00:00</orderdate>
</Order>
</Customer>
</CustomersOrders>';

Back 292

Write a query that selects Customer nodes with child nodes. Select principal nodes (elements in this context) only. The result should be similar to the abbreviated result
here.
1. Principal nodes
--------------------------------------------------------------------------------
<companyname>Customer NRZBB</companyname><Order orderid="10692"><orderdate>2007-

SELECT @x.query('CustomersOrders/Customer/*')
AS [1. Principal nodes];
2. All nodes
SELECT @x.query('CustomersOrders/Customer/node()')
AS [2. All nodes];
3. Comment nodes
SELECT @x.query('CustomersOrders/Customer/comment()')
AS [3. Comment nodes];

Front 293

XML data type includes five methods that accept XQuery as a parameter.

Back 293

query() - supports querying
value() - retrieving atomic values
exist() - check existence
modify() - modifying section within the XML data
nodes() - shredding XML data into multiple rows

Front 294

SQL Server XQuery support three DML keywords for data modification

Back 294

insert
delete
replace

Front 295

You can get information about schema collections by querying the catalog views

Back 295

sys.xml_schema_collections, sys.xml_schema_namespaces, sys.xml_schema_components

Front 296

SQL Server provides its own language extensions to support data modification with XQuery.

Back 296

SQL Server XQuery supports three data manipulation language (DML) keywords for data modification:
insert, delete, and replace value of.

Front 297

You create the schema collection by using what statement

Back 297

CREATE XML SCHEMA COLLECTION T-SQL

You have to supply the XML schema, an XSD document, as input

Front 298

The first index you create on an XML column is the

secondary XML indexes is created on

Back 298

is the primary XML index

secondary XML indexes is created on PATH, VALUE or PROPERTY

Front 299

Write a query that checks whether companyname and address nodes exist under the Customer node
<CustomersOrders>
<Customer custid="1"> <!-- Comment 111 -->
<companyname>Customer NRZBB</companyname>
<Order orderid="10692">
<orderdate>2007-10-03T00:00:00</orderdate>
</Order>
<Order orderid="10702">
<orderdate>2007-10-13T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Order>
</Customer>
<Customer custid="2"> <!-- Comment 222 -->
<companyname>Customer MLTDN</companyname>
<Order orderid="10308">
<orderdate>2006-09-18T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-04T00:00:00</orderdate>
</Order>
</Customer>
</CustomersOrders>';

Back 299

SELECT @x.exist('(/CustomersOrders/Customer/companyname)')
AS [Company Name Exists],
@x.exist('(/CustomersOrders/Customer/address)')
AS [Address Exists];

Company Name Exists Address Exists
------------------- --------------
1 0

Front 300

Write a query that retrieves the first customer name as a scalar value
<CustomersOrders>
<Customer custid="1"> <!-- Comment 111 -->
<companyname>Customer NRZBB</companyname>
<Order orderid="10692">
<orderdate>2007-10-03T00:00:00</orderdate>
</Order>
<Order orderid="10702">
<orderdate>2007-10-13T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Order>
</Customer>
<Customer custid="2"> <!-- Comment 222 -->
<companyname>Customer MLTDN</companyname>
<Order orderid="10308">
<orderdate>2006-09-18T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-04T00:00:00</orderdate>
</Order>
</Customer>
</CustomersOrders>';

Back 300

SELECT @x.value('(/CustomersOrders/Customer/companyname)[1]',
'NVARCHAR(20)')
AS [First Customer Name];

First Customer Name
--------------------
Customer NRZBB

Front 301

Write a query that returns all orders for the first customer as XML
<CustomersOrders>
<Customer custid="1"> <!-- Comment 111 -->
<companyname>Customer NRZBB</companyname>
<Order orderid="10692">
<orderdate>2007-10-03T00:00:00</orderdate>
</Order>
<Order orderid="10702">
<orderdate>2007-10-13T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Order>
</Customer>
<Customer custid="2"> <!-- Comment 222 -->
<companyname>Customer MLTDN</companyname>
<Order orderid="10308">
<orderdate>2006-09-18T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-04T00:00:00</orderdate>
</Order>
</Customer>
</CustomersOrders>';

Back 301

<Order orderid="10692">
<orderdate>2007-10-03T00:00:00</orderdate>
</Order>
<Order orderid="10702">
<orderdate>2007-10-13T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Order>
Use the following query to get the desired result.
SELECT @x.query('//Customer[@custid=1]/Order')
AS [Customer 1 orders];

Front 302

Write a query that shreds all orders information for the first customer.
<CustomersOrders>
<Customer custid="1"> <!-- Comment 111 -->
<companyname>Customer NRZBB</companyname>
<Order orderid="10692">
<orderdate>2007-10-03T00:00:00</orderdate>
</Order>
<Order orderid="10702">
<orderdate>2007-10-13T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Order>
</Customer>
<Customer custid="2"> <!-- Comment 222 -->
<companyname>Customer MLTDN</companyname>
<Order orderid="10308">
<orderdate>2006-09-18T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-04T00:00:00</orderdate>
</Order>
</Customer>
</CustomersOrders>';

Back 302

Order Id Order Date
----------- -----------------------
10692 2007-10-03 00:00:00.000
10702 2007-10-13 00:00:00.000
10952 2008-03-16 00:00:00.000

Use the following query to get the desired result.

SELECT T.c.value('./@orderid[1]', 'INT') AS [Order Id],
T.c.value('./orderdate[1]', 'DATETIME') AS [Order Date]
FROM @x.nodes('//Customer[@custid=1]/Order')
AS T(c);

Front 303

Write a query that updates the name of the first customer and then retrieve the new name
<CustomersOrders>
<Customer custid="1"> <!-- Comment 111 -->
<companyname>Customer NRZBB</companyname>
<Order orderid="10692">
<orderdate>2007-10-03T00:00:00</orderdate>
</Order>
<Order orderid="10702">
<orderdate>2007-10-13T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-16T00:00:00</orderdate>
</Order>
</Customer>
<Customer custid="2"> <!-- Comment 222 -->
<companyname>Customer MLTDN</companyname>
<Order orderid="10308">
<orderdate>2006-09-18T00:00:00</orderdate>
</Order>
<Order orderid="10952">
<orderdate>2008-03-04T00:00:00</orderdate>
</Order>
</Customer>
</CustomersOrders>';

Back 303

SET @x.modify('replace value of
/CustomersOrders[1]/Customer[1]/companyname[1]/text()[1]
with "New Company Name"');
SELECT @x.value('(/CustomersOrders/Customer/companyname)[1]',
'NVARCHAR(20)')
AS [First Customer New Name];

Front 304

Table variables def

Back 304

Table variables are variables that can store data but only for the duration of a T-SQL batch

Front 305

database schema def

Back 305

database schema is a named container (a namespace) that you can use to group tables and other database objects.

Front 306

database schema vs table schema

Back 306

Do not confuse the term database schema with table schema. A database schema is a database-wide container of objects. A table schema is the definition of a table that includes the CREATE TABLE statement with all the column definitions.

Front 307

guest schema is used to

INFORMATION_SCHEMA schema is used by

sys database schema is reserved by

Back 307

contain objects that would be available to the guest user. This schema is rarely used.

INFORMATION_SCHEMA schema is used by the Information Schema views, which provide ANSI standard access to metadata.

sys database schema is reserved by SQL Server for system objects such as system tables and views.

Front 308

following statement creates the Production database schema

Back 308

CREATE SCHEMA Production AUTHORIZATION dbo;
GO

Front 309

You can move a table from one schema to another by using the ALTER SCHEMA TRANSFER statement. Assuming there is no object named Categories in the Sales database schema, the following statement moves the Production.Categories table to the Sales database schema.

Back 309

ALTER SCHEMA Sales TRANSFER Production.Categories;
To move the table back, issue the following.
ALTER SCHEMA Production TRANSFER Sales.Categories;

Front 310

Because constraint and index names must also be identifiers, they cannot exceed the maximum identifier length of

When you need to store character strings, if they will likely vary in length, use the

Back 310

128

Front 311

When you need to store character strings, if they will likely vary in length, use the __________

Back 311

When you need to store character strings, if they will likely vary in length, use the
NVARCHAR or VARCHAR data types rather than the fixed NCHAR or CHAR

Front 312

Use what for replacement for depreciated TEXT, NTEXT, and IMAGE data types

Use ROWVERSION instead of the deprecated

Back 312

VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX)

TIMESTAMP.

Front 313

When you define the property in a CREATE TABLE statement, you can specify a seed value (that is, the value to begin with), and then an increment amount (that is, the amount to increment each new sequence number by). The most common values for seed and increment are

Back 313

(1,1)
CREATE TABLE Production.Categories(
categoryid INT IDENTITY(1,1) NOT NULL,
…

Front 314

SQL Server provides the __________________ stored procedure to help you determine whether a table with data in it would benefit from compression

Back 314

sp_estimate_data_compression_savings

Front 315

To list the primary key constraints in a database, you can query the

Back 315

SELECT *
FROM sys.key_constraints
WHERE type = 'PK';
Just as with the primary key constraints, you can list unique constraints in a database by querying the sys.key_constraints table filtering on a type of UQ.
SELECT *
FROM sys.key_constraints
WHERE type = 'UQ';
The following query finds the row for the FK_Products_Categories table.
SELECT *
FROM sys.foreign_keys
WHERE name = 'FK_Products_Categories';

Front 316

following query shows the unique index declared on the Production.Categories table for the PK_Categories primary key constraint

Back 316

SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('Production.Categories') AND name = 'PK_Categories';

Front 317

A view is created with the view option called SCHEMABINDING, which guarantees
=======================
CREATE VIEW statement must be the ____________ statement in a batch

Back 317

that the underlying table structures cannot be altered without dropping the view
========================
CREATE VIEW statement must be the first statement in a batch

Front 318

Using WITH ENCRYPTION when creating a view ....
================
Using WITH VIEW_METADATA when creating a view ...

Back 318

specifies that the view text should be stored in an obfuscated maner
======================
WITH VIEW_METADATA, when specified, returns the metadata of the view instead of the base table.

Front 319

WITH CHECK OPTION when creating a view

Back 319

If you define a view with a filter restriction in the WHERE clause of the SELECT statement, and then you modify rows of a table through the view, you could change a value so that the affected row no longer matches the WHERE clause filter. It is even possible to update rows that fall outside the filter.
WITH CHECK OPTION prevents such disappearing rows from occurring when you update through the view, and it restricts modifications to only rows that match the filter condition.

Front 320

Restrictions when creating a view (list 4)

Back 320

1)You cannot add ORDER BY to the SELECT statement in a view. A view must appear just like a table
2)You cannot pass parameters to view
3)View cannot create a table, whether permanent or temporary aka no SELECT/INTO
4)View can only reference permanent tables

Front 321

When you need to create a new view and conditionally replace the old view, you must first drop the old view and then create the new view. The following example shows one method.

Back 321

IF OBJECT_ID('Sales.OrderTotalsByYear', 'V') IS NOT NULL
DROP VIEW Sales.OrderTotalsByYear;
GO
CREATE VIEW Sales.OrderTotalsByYear
...
The 'V' parameter in the OBJECT_ID() function looks for views in the current database and then returns an object_id if a view with that name is found.

Front 322

To explore view metadata you can query

Back 322

sys.views catalog view

USE TSQL2012;
GO
SELECT name, object_id, principal_id, schema_id, type
FROM sys.views;

Front 323

you can use an inline table-valued function to simulate passing a

Back 323

you can use an inline table-valued function to simulate passing a parameter to a view, or in other words, simulate a parameterized view.
An inline table-valued function returns a rowset based on a SELECT statement you coded into the function.

Front 324

create an inline function that would operate just like the Sales.OrderTotalsByYear view, with no parameters, as follows.

Back 324

CREATE FUNCTION Sales.fn_OrderTotalsByYear ()
RETURNS TABLE
AS
RETURN (
SELECT
YEAR(O.orderdate) AS orderyear,
SUM(OD.qty) AS qty
FROM Sales.Orders AS O
JOIN Sales.OrderDetails AS OD
ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate) );
GO

Front 325

Inline functions have two significant options, both shared with views:

Back 325

You can create a function by using WITH ENCRYPTION, making it difficult for users to discover the SELECT text of the function.
You can add WITH SCHEMABINDING, which binds the table schemas of the underlying objects, such as tables or views, to the function. The referenced objects cannot be
altered unless the view is dropped or the WITH SCHEMABINDING option is removed

Front 326

Use inline function

Back 326

SELECT customercompany, shippercompany, orderyear, qty, val
FROM Sales.fn_OrderTotalsByYearCustShip (100, 200)
ORDER BY customercompany, shippercompany, orderyear;

Front 327

Synonyms def

Back 327

Synonyms are names stored in a database that can be used as substitutes for other object names. These names are also scoped to the database, and qualified with a schema name.

Front 328

Creating a Synonym

Back 328

CREATE SYNONYM dbo.Categories FOR Production.Categories;
GO
Then the end user can select from Categories without needing to specify a schema.
SELECT categoryid, categoryname, description
FROM Categories;

Front 329

Synonyms cannot refer to other ____________

Back 329

Synonyms cannot refer to other synonyms. They can only refer to database objects such as
tables,
views,
stored procedures, and
functions.
In other words, synonym chaining is not allowed.

Front 330

Dropping a Synonym cmd

Synonym and SCHEMA BINDINGS

Back 330

DROP SYNONYM dbo.Categories

Synonym and SCHEMA BINDINGS
There is no such thing as WITH SCHEMABINDIGN. If you drop an object in a DB, it will be dropped whether or not a synonym references it.

Front 331

Synonym Permissions

Back 331

To create a synonym, you must have the CREATE SYNONYM permission, which inherits from the CONTROL SERVER permission.

Front 332

No _____________ or __________ is stored with a synonym. Only the object referenced is stored with a synonym

Back 332

No T-SQL code or any data is stored with a synonym. Only the object referenced is stored with a synonym

Front 333

How to crate an orderid column with a seed 1 and an increment that will generate the values in that column automatically when rows are inserted

Back 333

...orderid INT NOT NULL IDENTITY(1, 1)...

CREATE TABLE Sales.MyOrders
(
orderid INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY,
custid INT NOT NULL,
empid INT NOT NULL,
orderdate DATE NOT NULL
CONSTRAINT DFT_MyOrders_orderdate DEFAULT (CAST(SYSDATETIME() AS DATE)),
shipcountry NVARCHAR(15) NOT NULL,
freight MONEY NOT NULL
);

Front 334

INSERT VALUES statement

Back 334

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight)
VALUES(2, 19, '20120620', N'USA', 30.00);

Without the target column list, you must specify the values in column definition order

Front 335

INSERT VALUES statement does not specify a value for a column with an IDENTITY property because the property generates the value for the column automatically.
If you do want to provide your own value instead of letting the IDENTITY property do it for you, you need to first turn on a session option called IDENTITY_INSERT, as follows.

Back 335

SET IDENTITY_INSERT <table> ON;

Front 336

A column can have a default constraint associated with
it like the orderdate column in the Sales.MyOrders table. If the INSERT statement doesn’t specify a value for the column explicitly, SQL Server will use the default expression to generate that value.
For example, the following statement doesn’t specify a value for orderdate, and...

Back 336

therefore SQL Server uses the default expression. INSERT INTO Sales.MyOrders(custid, empid, shipcountry, freight)
VALUES(3, 11, N'USA', 10.00);

Front 337

Using DEFAULT keyword when inserting

Back 337

INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight)
VALUES(3, 17, DEFAULT, N'USA', 30.00);

Front 338

INSERT SELECT statement inserts the result set returned by a query into the specified target table.

Back 338

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate, shipcountry, freight)
SELECT orderid, custid, empid, orderdate, shipcountry, freight
FROM Sales.Orders
WHERE shipcountry = N'Norway';

Front 339

INSERT EXEC statement, you can insert the result set (or sets) returned by a dynamic batch or a stored procedure into the specified target table

Back 339

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate, shipcountry, freight)
EXEC Sales.OrdersForCountry
@country = N'Portugal';
-----------------
CREATE PROC Sales.OrdersForCountry
@country AS NVARCHAR(15)
AS
SELECT orderid, custid, empid, orderdate, shipcountry, freight
FROM Sales.Orders
WHERE shipcountry = @country;
GO

Front 340

Unqualified Updates def

Back 340

an unqualified UPDATE statement affects all rows in the target table. You should be especially careful about unintentionally highlighting and executing only the UPDATE and SET clauses of the statement without the WHERE clause

Front 341

Nondeterministic UPDATE def

Back 341

You should be aware that the proprietary T-SQL UPDATE syntax based on joins can be nondeterministic. The statement is nondeterministic when multiple source rows match one target row. Unfortunately, in such a case, SQL Server doesn’t generate an error or even a warning. Instead, SQL Server silently performs a nondeterministic UPDATE where one of the source rows arbitrarily “wins.”

Front 342

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

Back 342

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

Front 343

An UPDATE with a variable can both ..........

Back 343

modify a column value and collect the result into a variable using one visit to the row.

Front 344

DELETE statement

Back 344

you can delete rows from a table. You can optionally specify a predicate to restrict the rows to be deleted. The general form of a DELETE statement looks like the following.
DELETE FROM <table>
WHERE <predicate>;

Front 345

you can split your large delete into smaller chunks. You can achieve this by using a DELETE statement with a TOP option that limits the number of affected rows in a loop (Example)

Back 345

WHILE 1 = 1
BEGIN
DELETE TOP (1000) FROM Sales.MyOrderDetails
WHERE productid = 12;
IF @@rowcount < 1000 BREAK;
END

Front 346

1)DELETE statement writes significantly more to the transaction log compared to the TRUNCATE statement
2)The DELETE statement doesn’t attempt to reset an identity property if one is associated with a column in the target table. The TRUNCATE statement does.

Back 346

3)The DELETE statement is supported if there’s a foreign key pointing to the table in question as long as there are no related rows in the referencing table. TRUNCATE is not
allowed if a foreign key is pointing to the table
4)The DELETE statement is allowed against a table involved in an indexed view. A TRUNCATE statement is disallowed in such a case.
5)DELETE statement requires DELETE permissions on the target table. The TRUNCATE statement requires ALTER permissions on the target table.

Front 347

DELETE Based on a Join example

Back 347

DELETE FROM O
FROM Sales.MyOrders AS O
INNER JOIN Sales.MyCustomers AS C
ON O.custid = C.custid
WHERE C.country = N'USA';

Front 348

DELETE Using Table Expressions example

Back 348

WITH OldestOrders AS
(
SELECT TOP (100) *
FROM Sales.MyOrders
ORDER BY orderdate, orderid
)
DELETE FROM OldestOrders;

Front 349

SQL Server 2012 editions

Back 349

Primary editions:
Standard
Business Intelligence
Enterprise
Other editions:
Web
Developer
Express
Compact

Front 350

When you insert rows into the table, don’t specify a value for the IDENTITY column because it gets its values automatically
INSERT INTO Sales.MyOrders(custid, empid, orderdate) VALUES (1, 2, '20120620')
For cases in which you insert rows into the table and you want to specify your own values for the orderid column, you need to

Back 350

SET IDENTITY_INSERT <table> to ON
INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate) VALUES (44, 1, 2, '20120620')
------------------------------
SET IDENTITY_INSERT Sales.MyOrders ON;
INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate) VALUES (44, 1, 2, '20120620')

Front 351

@@IDENTITY function returns

IDENT_CURRENT function accepts _____________ and returns

SCOPE_IDENTITY function returns

Back 351

the last identity value generated in your session regardless of scope.

The IDENT_CURRENT function accepts a table as input and returns the last identity value generated in the input table regardless of session.

the last identity value generated in your session in the current scope.

SELECT
SCOPE_IDENTITY() AS SCOPE_IDENTITY,
@@IDENTITY AS [@@IDENTITY],
IDENT_CURRENT('Sales.MyOrders') AS IDENT_CURRENT;

Front 352

to reseed the current identity value, use the DBCC CHECKIDENT command, as follows.

Back 352

DBCC CHECKIDENT('Sales.MyOrders', RESEED, 4);

TRUNCATE statement resets the IDENTITY to its initial value

Front 353

IDENTITY property has no cycling support explain

Back 353

This means that after you reach the maximum value in the type, the next insertion will fail due to an overflow error. To get around this, you need to reseed the current identity value before such an attempt is made.

Front 354

The IDENTITY property _____________ support cycling.
A TRUNCATE statement _____________the identity value.
You cannot________ an IDENTITY column.

Back 354

The IDENTITY property doesn’t support cycling.
A TRUNCATE statement resets the identity value.
You cannot update an IDENTITY column.

Front 355

You create a sequence object as an independent object in the database. It’s not tied to a particular column in a particular table. You use the __________ cmd to create a sequence

Back 355

CREATE SEQUENCE <schema>.<object>;

Front 356

CREATE SEQUENCE options
INCREMENT BY
MINVALUE
MAXVALUE
CYCLE|NO CYCLE
START WITH

Back 356

■ INCRE MENT BY Increment value. The default is 1.
■ MINVA LUE The minimum value to support. The default is the minimum value in the type. For example, for an INT type, it will be -2147483648.
■ MAXVALUE The maximum value to support. The default is the maximum value in the type.
■ CYC LE | NO CYC LE Defines whether to allow the sequence to cycle or not. The default is NO CYCLE.
■ START WITH The sequence start value. The default is MINVALUE for an ascending sequence (positive increment) and MAXVALUE for a descending one.

Front 357

To request a new value from the sequence, use the NEXT ....

Back 357

VALUE FOR <sequence name> function. For example, run the following code three times.

SELECT NEXT VALUE FOR Sales.SeqOrderIDs;

Front 358

With NO CACHE, SQL Server has tо...

Back 358

write to disk for every request of a new sequence value. With some caching, the performance is much better. The default cache value is 50 at the date of this writing,

Front 359

The sequence object also supports a caching option that controls how often the current sequence value is written to disk versus written to memory

Back 359

a sequence with a CACHE 100 defined for it will write to disk once every 100 changes

The benefit is better performance for allocation of sequence values. The risk is losing a range up to the size of the cache value in case there’s an unordered shutdown of the service.

Front 360

MERGE statement

Back 360

you can MERGE date from a source table or table expression into a target table

MERGE INTO <target table> AS TGT
USING <SOURCE TABLE> AS SRC
ON <merge predicate>
WHEN MATCHED [AND <predicate>] -- two clauses allowed:
THEN <action> -- one with UPDATE one with DELETE
WHEN NOT MATCHED [BY TARGET] [AND <predicate>] -- one clause allowed:
THEN INSERT... –- if indicated, action must be INSERT
WHEN NOT MATCHED BY SOURCE [AND <predicate>] -- two clauses allowed:
THEN <action>; -- one with UPDATE one with DELETE

Front 361

upsert logic

SERIALIZABLE or HOLDLOCK options for MERGE statement

Back 361

update where exists, insert where not exists. Usually used when talking about MERGE statement

if you are having concurrent processes MERGING data in 1 table and 1st process inserts a key then 2nd process tries to insert same key 2nd process will fail. To prevent such a failure, use the hint SERIALIZABLE or HOLDLOCK (both have equivalent meanings) against the target

MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT
.....

Front 362

You can add a predicate that says that at least one of the nonkey column values in the source and the target must be different in order to apply the UPDATE action. Your code would look like the following.

Back 362

DECLARE
@orderid AS INT = 1,
@custid AS INT = 1,
@empid AS INT = 2,
@orderdate AS DATE = '20120620';
MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT
USING (VALUES(@orderid, @custid, @empid, @orderdate))
AS SRC( orderid, custid, empid, orderdate)
ON SRC.orderid = TGT.orderid
WHEN MATCHED AND ( TGT.custid <> SRC.custid
OR TGT.empid <> SRC.empid
OR TGT.orderdate <> SRC.orderdate) THEN UPDATE
SET TGT.custid = SRC.custid,
TGT.empid = SRC.empid,
TGT.orderdate = SRC.orderdate
WHEN NOT MATCHED THEN INSERT
VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate);

Now the code updates the target row only when the source order ID is equal to the target order ID, and at least one of the other columns have different values in the source and the target. If the source order ID is not found in the target, the statement will insert a new row like before.

Front 363

T-SQL extends standard SQL by supporting a third clause called WHEN NOT MATCHED BY SOURCE
List the two clauses and explain

Back 363

UPDATE and DELETE

With WHEN NOT MATCHED BY SOURCE clause you can define an action to take against the target row when the target row exist but is not matched by a source row

Front 364

OUTPUT clause

Back 364

the design of the OUTPUT clause is very similar to that of the SELECT clause. You can specify expressions and assign them with result column aliases. One difference from the SELECT clause is that in the OUTPUT clause, is that when you refer to columns from the modified rows, yo need to prefix the column names with the keyword inserted or deleted.

Front 365

INSERT with OUTPUT

Back 365

OUTPUT clause can be used in an INSERT statement to return information from the inserted rows. An example for a very practical use case is when you have a multirow INSERT statement that generates new keys by using the IDENTITY property or a sequence, and you need to know which new keys were generated.
INSERT INTO Sales.MyOrders(custid, empid, orderdate)
OUTPUT
inserted.orderid, inserted.custid, inserted.empid, inserted.orderdate
SELECT custid, empid, orderdate
FROM Sales.Orders
WHERE shipcountry = N'Norway';

Front 366

DELETE with OUTPUT

Back 366

You can use the OUTPUT clause to return information from deleted rows in a DELETE statement. You need to prefix the columns that you refer to with the keyword deleted.
DELETE FROM Sales.MyOrders
OUTPUT deleted.orderid
WHERE empid = 1;

Front 367

MERGE with OUTPUT

Back 367

MERGE statement can apply different actions
against the target table t when returning output rows, you need to know which action (INSERT, UPDATE, or DELETE) affected the output row. For this purpose,
SQL Server provides you with the $action function. This function returns a string ('INSERT', 'UPDATE', or ‘DELETE') indicating the action.
--------------------------------
OUTPUT
$action AS the_action,
COALESCE(inserted.orderid, deleted.orderid) AS orderid;
This code generates the following output.
the_action orderid
---------- ---
INSERT 1
INSERT 5
UPDATE 2
UPDATE 3

Front 368

TIP MERGE and OUTPUT

Back 368

In INSERT, UPDATE, and DELETE statements, you can only refer to columns from the target table in the OUTPUT clause. In a MERGE statement you can refer to columns from both the target and the source.

Front 369

composable DML

Back 369

Suppose you need to capture output from a modification statement, but you are interested only in a subset of the output rows and not all of them. T-SQL has a solution for this in the form of what some platforms refer to as composable DML

Front 370

aCID properties of Transactions

Back 370

Automicity
Consistency
Isolation
Durability

Front 371

If XACT_ABORT is OFF

Back 371

which is the default, you can insert code to decide whether to roll back the transaction or commit it

Front 372

You can observe the names of transactions by inspecting the name column of the dynamic management view
(DMV)

Back 372

sys.dm_tran_active_transactions

Front 373

@@TRANCOUNT can be queried to find the level of transaction

Back 373

A level of 0 indicates that at this point, the code is not within a transaction.
A level > 0 indicates that there is an active transaction, and a number > 1 indicates the nesting level of nested transactions.

Front 374

XACT_STATE( ) can be queried to find the state of the transaction.

Back 374

A state of 0 indicates that there is no active transaction.
A state of 1 indicates that there is an uncommitted transaction, and it can be committed, but the nesting level is not reported.
A state of -1 indicates that there is an uncommitted transaction, but it cannot be committed due to a prior fatal error.

Front 375

Transaction Modes

Back 375

Autocommit
Implicit transaction
Explicit transaction

Front 376

ACID

Back 376

Atomicity
Consistency
Isolation
Durability

Front 377

SQL Server has two basic types of transactions

Back 377

System transactions SQL Server maintains all its internal persistent system tables by using transactions that it classifies as system transactions. These transactions are not under user control
User transactions These are transactions created by users in the process of changing and even reading data, whether automatically, implicitly, or explicitly

Front 378

You can observe the names of transactions by inspecting the name column of the dynamic management view (DMV)

Back 378

sys.dm_tran_active_transactions

The default name for user transactions is user_transaction. You can assign your own name to a transaction by using explicit transactions, as described in the following section.

Front 379

Every transaction consists of a

To commit a transaction

To roll back a transaction

Back 379

BEGIN TRAN

COMMIT TRANSACTION

ROLLBACK TRANSACTION command, or alternatively, ROLLBACK TRAN, ROLLBACK WORK, or just ROLLBACK.

Front 380

Autocommit transaction mode def

Back 380

In the autocommit mode, single data modification and DDL T-SQL statements are executed in the context of a transaction that will be automatically committed when the statement succeeds, or automatically rolled back if the statement fails.

Front 381

Implicit transaction mode def

Back 381

implicit transaction mode, when you issue one or more DML or DDL statements, or a SELECT statement, SQL Server starts a transaction, increments @@TRANCOUNT, but does not automatically commit or roll back the statement. You must issue a COMMIT or ROLLBACK interactively to finish the transaction, even if all you issued was a SELECT statement

Front 382

Implicit transaction mode is not the SQL Server default. You enter that mode by issuing the following command.

Back 382

SET IMPLICIT_TRANSACTIONS ON;

Front 383

explicit transaction def

Back 383

explicit transaction occurs when you explicitly issue the BEGIN TRANSACTION or BEGIN TRAN command to start a transaction.

Front 384

Nested Transactions

Back 384

When explicit transactions are nested—that is, placed within each other—they are called nested transactions. The behavior of COMMIT and ROLLBACK changes when you nest transactions

Front 385

Note that transactions can span batches

Back 385

This includes both implicit transactions and explicit transactions—that is, GO statements. However, it is often a best practice to make sure that each transaction takes place in one batch.

Front 386

An inner COMMIT statement has no real effect on the transaction,

Back 386

only decrementing @@TRANCOUNT by 1.
Just the outermost COMMIT statement, the one executed when @@TRANCOUNT = 1, actually commits the transaction.

Front 387

Note that it doesn't matter at what level you issue the ROLLBACK command

Back 387

A transaction can contain only one ROLLBACK command, and it will roll back the entire transaction and reset the @@TRANCOUNT counter to 0 despite at what level @@TRANCOUNT is

Front 388

Marking a Transaction

Back 388

putting the name after the BEGIN TRAN statement marks the transaction
Transaction names must follow the rules for SQL Server identifiers; however, SQL Server only recognizes the first 32 characters as a unique name

USE TSQL2012;
BEGIN TRANSACTION Tran1;

Note that SQL Server only records transaction names for the outermost transaction. If you have nested transactions, any names for the nested transactions are ignored.

Front 389

You have a transaction with mark

USE TSQL2012;
BEGIN TRAN Tran1 WITH MARK;
-- <transaction work>
COMMIT TRAN; -- or ROLLBACK TRAN
-- <other work>

If you need to restore the database to the transaction mark later, you can run the following code.

Back 389

RESTORE DATABASE TSQ2012 FROM DISK = 'C:SQLBackups\TSQL2012.bak'
WITH NORECOVERY;
GO
RESTORE LOG TSQL2012 FROM DISK = 'C:\SQLBackups\TSQL2012.trn'
WITH STOPATMARK = 'Tran1';
GO

Front 390

Note the following about using WITH MARK:

Back 390

Note the following about using WITH MARK:
■■ You must use the transaction name with STOPATMARK.
■■ You can place a description after the clause WITH MARK, but SQL Server ignores it.
■■ You can restore to just before the transaction with STOPBEFOREMARK.
■■ You can recover the dataset by restoring with either WITH STOPATMARK or STOPBEFOREMARK.
■■ You can add RECOVERY to the WITH list, but it has no effect.

Front 391

SAVE TRANSACTION <savepoint name>

Back 391

You can define a savepoint by using the SAVE TRANSACTION <savepoint name> command.

Front 392

ROLLBACK statement

Back 392

ROLLBACK statement must reference the savepoint. Otherwise, if the statement is unqualified, it will roll back the entire transaction

Front 393

Cross-database transactions def

Back 393

A transaction may span two or more databases on a single SQL Server instance without any additional work on the user’s part

Front 394

Distributed transactions def

Back 394

It is possible to make a transaction span more than one
server, by using a linked server. In that case, the transaction is known as a distributed (as opposed to local) transaction.

Front 395

READ COMMITTED def

Back 395

default isolation level
All readers in that session will only read data changes that have been committed.
All SELECT statements will attempt to acquire shared locks, and any underlying data resources that are being changed by a different session, and therefore have exclusive locks, will block the READ COMMITTED session

Front 396

READ UNCOMMITTED def

Back 396

allows readers to read uncommitted data
This setting removes the shared locks taken by SELECTS statements so that readers no longer are blocked by writers
However the results of a SELECT statement could read uncommitted data that was changed during a transaction and then later was rolled back to its initial stated.
This is called reading dirty data

Front 397

READ COMMITTED SNAPSHOT def

Back 397

it is an optional way of using the default READ COMMITTED isolation level, the default isolation level in Windows Azure SQL Database
uses tempdb to store original versions of changed data. These versions are only stored as long as they are needed to allow readers to read underlying data in its original state. As a result, SELECT statements no longer need shared locks on the underlying resource while only reading (originally) committed data

Front 398

REPEATABLE READ def

Back 398

Could be set per session
Guarantees that whatever data is read in a transaction can be re-read later in the transaction
Updates and deletes of rows already selected are prevented as result shared locks are kept until the end of a transaction. However the transaction may see new rows added after its first read - this is called a phantom read

Front 399

SNAPSHOT def

Back 399

this isolation level uses row versioning in tempdb
It is enabled as a persistent database property and then set per transaction.
A transaction using SNAPSHOT isolation level will be able to repeat any reads and it will not see any phantom reads.
New rows may be added to a table, but the transaction will not see them
Because of using row versioning the SNAPSHOT isolation level does not require shared lock on the underlying data

Front 400

SERIALAIZABLE def

Back 400

the strongets isolation level and is set per session.
At this level all reads are repeatable and new rows are not allowed in the underlying tables that would satisfy the conditions of the SELECT statements in the transaction

Front 401

T-SQL provides you with ways of detecting SQL Server errors and raising errors of your own. When SQL Server generates an error condition, the system function ______________ will have a positive integer value indicating the error number.

Back 401

@@ERROR

Front 402

SQL Server error messages are numbered from
Custom error messages are numbered from
error number 50000 is reserved for a

Back 402

1 to 49999
50001 up
custom message that does not have a custom error number.

Front 403

severity level SQL Server defines 26 severity levels numbered from 0 through 25

Back 403

errors with a severity level of 16 or higher are logged automatically to the SQL Server log and the Windows Application log
Errors with a severity level from 19 through 25 can be specified only by members of the sysadmin fixed server role.
Errors with a severity level from 20 through 25 are considered fatal and cause the connection to be terminated and any open transactions to be rolled back.
Errors with severity level 0 through 10 are informational only

Front 404

RAISERROR command uses the following syntax

Back 404

RAISERROR ( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]

RAISERROR ('Error in usp_InsertCategories stored procedure', 16, 0);

Front 405

THROW command behaves mostly like RAISERROR, with some important exceptions. The basic syntax of THROW is the following.

Back 405

THROW [ { error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable }
] [ ; ]

THROW 50000, 'Error in usp_InsertCategories stored procedure', 0;

Front 406

THROW has many of the same components as RAISERROR but with the following significant differences:

Back 406

THROW does not use parentheses to delimit parameters.
■■
THROW can be used without parameters, but only in the CATCH block of a TRY/CATCH construct.
■■
When parameters are supplied, error_number, message, and state are all required.
■■
The error_number does not require a matching defined message in sys.messages.
■■
The message parameter does not allow formatting, but you can use FORMATMESSAGE() with a variable to get the same effect.
■■
The state parameter must be an integer that ranges from 0 to 255.
■■
Any parameter can be a variable.
■■
There is no severity parameter; the severity is always set to 16.
■■
THROW always terminates the batch except when it is used in a TRY block.

Front 407

The statement before the ThROW statement must be terminated by

Back 407

The statement before the ThROW statement must be terminated by a semicolon (;). This reinforces the best practice to terminate all T-SQL statements with a semicolon.

Front 408

You can use the system stored procedure _________ to add your own custom error message

Back 408

sp_addmessage

Front 409

What is severity level 0 used for?

Back 409

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.

Front 410

atom feed

Back 410

an XML structure that contains metadata bout content, such as the language version and the date when the content was last modified, and is sent to the subscriber by using Atom Publishing Protocol (AtomPub).

Front 411

talk to you later

Back 411

vous parler plus tard
воу парле плу тард

Front 412

Temporary tables def

Back 412

Temporary tables are base tables that exist in tempdb and last only as long as a session or scope referencing them endures

Front 413

Table variables def

Back 413

Table variables are variables that can store data but only for the duration of a T-SQL batch

Front 414

Views def

Back 414

Views, which are not base tables but are derived from queries against base tables, appear just like tables but do not store data

Front 415

Indexed views def

Back 415

Indexed views store data but are defined as views and are updated whenever the base tables are updated

Front 416

Derived tables and table expressions

Back 416

Derived tables and table expressions are subqueries that are referenced like tables in queries

Front 417

You can create a table in T-SQL in two ways:

Back 417

CREATE TABLE statement
SELECT INTO statement

Front 418

database schema def

Back 418

is a named container (a namespace) that you can use to group tables and
other database objects

Front 419

The following four built-in database schemas cannot be dropped

Back 419

■■ The dbo database schema is the default database schema for new objects created by users having the db_owner or db_ddl_admin roles.
■■ The guest schema is used to contain objects that would be available to the guest user. This schema is rarely used.
■■ The INFORMATION_SCHEMA schema is used by the Information Schema views, which provide ANSI standard access to metadata.
■■ The sys database schema is reserved by SQL Server for system objects such as system tables and views.

Front 420

You can move a table from one schema to another by using the ALTER SCHEMA TRANSFER

Back 420

ALTER SCHEMA Sales TRANSFER Production.Categories;
To move the table back, issue the following.
ALTER SCHEMA Production TRANSFER Sales.Categories;

Front 421

Variables must begin with
Temporary tables or procedures must begin with

Back 421

Variables must begin with an at sign (@).
Temporary tables or procedures must begin with a number sign (#).

Front 422

computed column

Back 422

CREATE TABLE Sales.OrderDetails
(
orderid INT NOT NULL,
…
initialcost AS unitprice * qty -- computed column
);

Front 423

Table Compression

Back 423

CREATE TABLE Sales.OrderDetails
(
orderid INT NOT NULL,
…
)
WITH (DATA_COMPRESSION = ROW);

Front 424

apply page compression

Back 424

ALTER TABLE Sales.OrderDetails
REBUILD WITH (DATA_COMPRESSION = PAGE);

Front 425

Primary Key Constraints example

Back 425

CREATE TABLE Production.Categories
(
categoryid INT NOT NULL IDENTITY,
categoryname NVARCHAR(15) NOT NULL,
description NVARCHAR(200) NOT NULL,
CONSTRAINT PK_Categories PRIMARY KEY(categoryid)
);

Front 426

Another way of declaring a column as a primary key is to use the ALTER TABLE statement, which you could write as follows.

Back 426

ALTER TABLE Production.Categories
ADD CONSTRAINT PK_Categories PRIMARY KEY(categoryid);
GO

Front 427

To list the primary key constraints in a database, you can query the sys.key_constraints table filtering on a type of PK.

Back 427

SELECT *
FROM sys.key_constraints
WHERE type = 'PK';

Front 428

query shows the unique index declared on the Production.Categories table for the PK_Categories primary key constraint

Back 428

SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID('Production.Categories') AND name = 'PK_Categories';

Front 429

declare a unique constraint on the categoryname
column, with the following.

Back 429

ALTER TABLE Production.Categories
ADD CONSTRAINT UC_Categories UNIQUE (categoryname);
GO

Front 430

list unique constraints in a database by querying the sys.key_constraints table filtering on a type of UQ.

Back 430

SELECT *
FROM sys.key_constraints
WHERE type = 'UQ';

Front 431

Can a primary key on one table have the same name as the primary key in another table in the same database?

Back 431

No, all table constraints must have unique names in a database.

Front 432

create the foreign key

Back 432

USE TSQL2012
GO
ALTER TABLE Production.Products WITH CHECK
ADD CONSTRAINT FK_Products_Categories FOREIGN KEY(categoryid)
REFERENCES Production.Categories (categoryid)
GO

Front 433

following query finds the row for the FK_Products_Categories table.

Back 433

SELECT *
FROM sys.foreign_keys
WHERE name = 'FK_Products_Categories';

Front 434

list the check constraints for a table by querying sys.check_constraints, as in the following.

Back 434

SELECT *
FROM sys.check_constraints
WHERE parent_object_id = OBJECT_ID('Production.Products');

Front 435

query finds all the default constraints for the Production.Products table.

Back 435

SELECT *
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('Production.Products');

Front 436

query the inline function but pass the year you want to see

Back 436

SELECT orderyear, qty FROM Sales.fn_OrderTotalsByYear(2007);

Front 437

Get a list of all columns in a DB

Back 437

Use UserDB;

SELECT * FROM sys.all_columns