Set 1 Flashcards

Question

What is a literal?

Answer 1

A constant, also known as a literal or a scalar value, is a symbol that represents a specific data value. The format of a constant depends on the data type of the value it represents. A literal is an explicit numeric, character, string, or boolean value not represented by an identifier. For example, TRUE, 786, NULL, 'tutorialspoint' are all literals of type Boolean, number, or string.

Answer 2

A literal has a type, if you write an expression that invovles operands of different types, SQL Server will have to apply implicit conversion to align the types.

Answer 3

Implicit conversion can sometimes hurt performance.

Answer 4

If it is Unicode then SQL server has to implicitly convert from non unicode to unicode e.g. Employee = 'John' is not best practice, best practice is Employee = N'John'.

Answer 5

Any items that begins with John

Answer 6

Any string where the second character is J.

Answer 7

Any string where the first letter is S or Y.

Answer 8

Any string where the first single character from a range of 0-9.

Answer 9

Any string where the first character is not in the range 0-9.

Answer 10

Any string including an empty one.

Answer 11

A single character

Answer 12

A single character that is not in the list or range

Answer 13

If you want to look for a character that is considered a wildcard, you can indicate it after a character that you designate as an escape character by using the ESCAPE keyword, such as Employee LIKE '#_Fred% ESCAPE '#'

Answer 14

For example, the form '20160212' is always interpreted as ymd.

Answer 15

‘2019-03-10’ is considered language-neutral only for the data types DATE, DATETIME2, and DATETIMEOFFSET.

Answer 16

Use CONVERT and change it the right format.

Answer 17

The precision of DATETIME is three and a third milliseconds. Because 999 is not a multiplication of this precision, the value is rounded up to the next millisecond, which happens to be the midnight of the next day.

Answer 18

With closed-open interval such as: SELECT Employee FROM Employee WHERE hiredate >= '20150503' AND hiredate

Answer 19

Correct understanding of this aspect of the language ties directly to the foundations of T-SQL—particularly mathematical set theory. If you understand this from the very early stages of writing T-SQL code, you will have a much easier time than many who simply have incorrect assumptions and expectations from the language.

Answer 20

A table in T-SQL is supposed to represent a relation; a relation is a set, and a set has no order to its elements. With this in mind, unless you explicitly instruct the query otherwise, the result of a query has no guaranteed order.

Answer 21

The database engine can—and sometimes does—change choices that can affect the order in which rows are returned, knowing that it is free to do so. Examples for such changes in choices include changes in data distribution, availability of physical structures such as indexes, and availability of resources like CPU and memory. Also, with changes in the engine after an upgrade to a newer version of the product, or even after application of a service pack, optimization aspects can change.

Answer 22

No, ordering by a non-unique column would not guarantee deterministic ordering. Such as, orderging by Country Name in worldwide dataset of people.

Answer 23

No, Ordering by ordinal positions (numbers) is nonrelational since the heading of a realtion is set of attributes, and sets have no order. Also, it is easier to make mistakes in the keyed in order of the query.

Answer 24

Together and first in T-SQL.

Answer 25

No, a query with no ORDER BY is a relation and a relation has no order. According to standard SQL an ORDER BY clause conceptually returns a cursor and not a relation.

Answer 26

TOP and OFFSET-FETCH use numbers to filter.

Answer 27

Use SELECT TOP (#) WITH TIES or add an ORDER BY to break ties.

Answer 28

The OFFSET and FETCH clauses appear right after the ORDER BY clause, and in fact, in T-SQL, they require an ORDER BY clause to be present. Example: Select EmployeeName FROM EmpTable ORDER BY empID DESC OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY.

Answer 29

You can use NEXT or FIRST after FETCH.

Answer 30

If you want an arbitrary order with OFFSET FETCH, you can use (SELECT NULL) in the ORDER BY clause.

Answer 31

OFFSET-FETCH allows expressions as inputs.

Answer 32

SELECT EmployeeName, EmpID FROM EmpTable ORDER BY EmpID OFFSET 10 ROWS FETCH NEXT 1 ROWS ONLY. or OFFSET 10 ROW FETCH FIRST 1 ROW ONLY.

Answer 33

Think of them as an extension of the GROUP BY clause.

Answer 34

After the GROUP BY.

Answer 35

UNION, UNION ALL, INTERSECT, EXCEPT.

Answer 36

1. Same number of columns. 2. Implicitly convertible column types between the corresponding columns of each set. 3. Operators use DISTINCTNESS-based comparison NOT equality-based comparison. So a comparison between two NULLs yields true, unlike filtering clauses like WHERE, ON, and HAVING. 4. These are SET operators and not CURSOR operators, so no GROUP BY on sets only on the final result 5. Column Names are determined by the first query

Answer 37

WHERE, ON, HAVING

Answer 38

Mathematical Set operators allow different types between two merged sets, whereas relational table do not.

Answer 39

The UNION operator unifies the results of two input queries. As a set operator, UNION has an implied DISTINCT property, meaning it does NOT return duplicate rows.

Answer 40

It keeps duplicates as opposed to the UNION operator.

Answer 41

It returns only distinct rows that are common to both set.

Answer 42

The EXCEPT operator performs set difference. It returns distinct rows that appear in the result of the first query but not the second.

Answer 43

INTERSECT precedes UNION and EXCEPT and UNION and EXCEPT are evaluated from LEFT to RIGHT based on their position in the expression.

Answer 44

Performs a Cartesian product of two input tables.

Answer 45

No, as long as it can return the correct result.

Answer 46

It is returning the results as fast as possible, hence SQL is not bound to follow logical query processing.

Answer 47

It is applying the predicate before a join, such as a CROSS JOIN followed by a predicate.

Answer 48

In self joins.

Answer 49

FROM table1, table2 instead of FROM table1 CROSS JOIN table2 Use the newer syntax.

Answer 50

Equiijoin is using a predicate with an equality operator such as table1.keycol = table2.keycol

Answer 51

They evaluate to NULL so it discards them.

Answer 52

SQL Server creates a unique index on the constraint columns to enforce the constraint's uniqueness property.

Answer 53

No, but it is considered best practice for clarity sake.

Answer 54

For INNER joins it does not matter. Both filter only rows where the predicate evaluates to true. In logical query processing, the WHERE is evaluated right after the FROM. So conceptually it is equivalent to concatenating the predicates with an AND operator, forming a conjunction of predicates.

Answer 55

The standard made JOIN for inner join since it is the most common join.

Answer 56

No, since LEFT JOINS (LEFT OUTER JOINS) and RIGHT JOINS (RIGHT OUTER JOINS) preserve all their rows on their respective side, the ON keyword cannot filter the table, all the rows are preserved for that table. This is not the case with INNER join so WHERE and ON are interchangeable.

Answer 57

When you need to join tables that are related based on multiple columns, the join is called a composite join and the ON clause typically consists of a conjunction of predicates( predicates separated by AND operators) that match the corresponding columns from the two sides.

Answer 58

A composite join and the ON clause typically consists of a conjunction of predicates.

Answer 59

A unique key constraint permits NULLs.

Answer 60

Because NULL is unknown therefore cannot be a value..

Answer 61

You can use ISNULL(Col, "text") but it cannot preserve ordering and can negatively affect the ability to rely on index ordering during optimization. Applying manipulation to join columns breaks the ordering property of the data, and therefore even if ti's preordered in an index the optimizer cannot trust this order.

Answer 62

EL.region = CL.region OR (EL.region IS NULL AND CL.region IS NULL))

Answer 63

SELECT t1.col, t1.col2, t2.col, t2.col2 FROM table1 t1 INNER JOIN table2 t2 ON EXISTS(SELECT t1.col, t1.col2 INTERSECT SELECT t2.col, t2.col1);

Answer 64

LEFT to RIGHT however inner and cross-joins the order cannot affect the meaning.

Answer 65

One solution is to use a LEFT OUTER JOIN instead of the INNER JOIN. Another solution is to continue to an INNER JOIN but put the ON statement AFTER the INNER JOIN. For example, FROM Production.S AS S LEFT OUTER JOIN (Production.P AS P INNER JOIN Production.C AS C ON C.categoryid = P.categoryid) ON S.supplierid = P.supplierid

Answer 66

It discards the OUTER rows that were produced by the OUTER JOIN. Use the workarounds to avoid this. Either convert the INNER JOIN to a LEFT JOIN or move the ON predicate for the OUTER JOIN after the INNER JOIN.

Answer 67

CAST and CONVERT.

Answer 68

The PARSE function is an alternative to CONVERT when you want to parse a character string input to a target type, but instead of using cryptic style numbers, it uses a more userfriendly .NET culture name. For instance, the expression PARSE(‘01/02/2017’ AS DATE USING ‘en-US’) uses the English US culture, parsing the input as a date meaning January 2, 2017.

Answer 69

The entire query fails and stop processing. Use TRY_CAST, TRY_CONVERT, and TRY_PARSE instead. This will return NULL instead of failing.

Answer 70

Lastly, the FORMAT function is an alternative to the CONVERT function when you want to format an input expression of some type as a character string, but instead of using cryptic style numbers, you specify a .NET format string and culture, if relevant.

Answer 71

The DATEDIFF function returns a value of an INT type. If the difference doesn’t fit in a four-byte integer, use the DATEDIFF_BIG function instead. Which brings back BIGINT.

Answer 72

SWITCHOFFSET, TODATETIMEOFFSET, and AT TIME ZONE.

Answer 73

SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘-08:00’). Regardless of the offset of the SQL Server instance you are connected to, you request to present the current date and time value in terms of offset ‘-08:00’. If the system’s offset is, say, ‘-05:00’, the function will compensate for this by subtracting three hours from the input value.

Answer 74

The TODATETIMEOFFSET function is used for a different purpose. You use it to construct a DATETIMEOFFSET value from two inputs: the first is a date and time value that is not offset-aware, and the second is the offset. You can use this function to convert a value that is not offset aware to a target offset typed value without the need to manually convert the value and the offset to character strings with the right style and then to DATETIMEOFFSET.

Answer 75

SELECT SWITCHOFFSET('20170212 14:00:00.0000000 -05:00', '-08:00') AS [SWITCHOFFSET], TODATETIMEOFFSET('20170212 14:00:00.0000000', '-08:00') AS [TODATETIMEOFFSET];

Answer 76

T-SQL supports a function called AT TIME ZONE that can be used instead of both the SWITCHOFFSET and the TODATETIMEOFFSET functions, and that uses named time zones instead of offsets. This way you don’t need to worry about capturing the right offset and whether it’s daylight savings time or not, you just capture the time zone name.

Answer 77

DATETIMEOFFSET

Answer 78

SYSDATETIMEOFFSET() AT TIME ZONE 'Pacific Standard Time'

Answer 79

You use the following expression: lastmodified AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Pacific Standard Time.’

Answer 80

The + operator returns a NULL. That standard behavior that can be changed by turning off a session option called CONCAT_NULL_YIELDS_NULL, but it is not recommened to rely on such NON STANDARD behavior.

Answer 81

Another option is to use the CONCAT function which, unlike the + operator, substitutes a NULL input with an empty string.

Answer 82

T-SQL also supports a function called PATINDEX that, like CHARINDEX, you can use to locate the first position of a string within another string. But whereas with CHARINDEX you’re looking for a constant string, with PATINDEX you’re looking for a pattern.

Answer 83

As an example, the expression PATINDEX(‘%[0-9]%’, ‘abcd123efgh’) looks for the first occurrence of a digit (a character in the range 0–9) in the second input, returning the position 5 in this case.

Answer 84

Note that it returns the number of characters, not bytes, whether the input is a regular character or Unicode character string. For example, the expression LEN(N’xyz’) returns 3. If there are any trailing spaces, LEN removes them.

Answer 85

The DATALENGTH function returns the length of the input in terms of number of bytes. This means, for example, that if the input is a Unicode character string, it will count 2 bytes per character. For example, the expression DATALENGTH(N’xyz’) returns 6. Note also that, un-like LEN, the DATALENGTH function doesn’t remove trailing spaces.

Answer 86

The 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’.

Answer 87

As mentioned earlier, with the FORMAT function, you can format an input value based on a .NET format string. I demonstrated an example with date and time values. As another example, this time with numeric values, the expression FORMAT(1759, ‘0000000000’) formats the input number as a character string with a fixed size of 10 characters with leading zeros, returning ‘0000001759’.

Answer 88

T-SQL supports a table-valued function called STRING_SPLIT that accepts a charter string with a separated list of values provided as the first input, and a character string with the separator as the second input, and returns a result set with a column called value holding the individual split strings

Answer 89

t. A statement performs some kind of an action or controls the flow of the code, and that’s not what CASE does; CASE returns a value, and hence is an expression.

Answer 90

Those are the standard COALESCE and NULLIF functions, and the nonstandard ISNULL, IIF, and CHOOSE. As for IIF and CHOOSE, these are nonstandard T-SQL functions that were added to simplify migrations from Microsoft Access platforms.

Answer 91

The @@ROWCOUNT function is a very popular function that returns the number of rows affected by the last statement that you executed.

Answer 92

T-SQL supports a function called COMPRESS that enables you to compress an input character or binary string using the GZIP algorithm into a result binary string. It also supports a function called DECOMPRESS that allows you to decompress a previously compressed string.

Answer 93

INSERT INTO dbo.MyNotes(notes) VALUES(COMPRESS(@notes)); SELECT keycol CAST(DECOMPRESS(notes) AS NVARCHAR(MAX)) AS notesFROM dbo.MyNotes;

Answer 94

However, certain modules in T-SQL, for example, triggers, are by design niladic, meaning they don’t support parameters. One technique to pass information between an outer level and a niladic module is to use either context info or session context.

Answer 95

Context info is a binary string of up to 128 bytes that is associated with your session. You write to it using the SET CONTEXT_INFO command and read it using the CONTEXT_INFO function. For example, the following code writes the value ‘us_english,' after converting it to a binary string, as the current session’s context info:DECLARE @mycontextinfo AS VARBINARY(128) = CAST('us_english' AS VARBINARY(128));SET CONTEXT_INFO @mycontextinfo; You can read the context info from anywhere in your session, including triggers as follows:SELECT CAST(CONTEXT_INFO() AS VARCHAR(128)) AS mycontextinfo;

Answer 96

-SQL provides a tool called session context as a more convenient and robust alternative to context info. With session context, you store key-value pairs, where the key is a name of a sysname type (internally mapped to NVARCHAR(128)) that you assign to your session’s vari-able, and the value is a SQL_VARIANT typed value that is associated with the key.

Answer 97

``` EXEC sys.sp_set_session_context @key = N'language', @value = 'us_english', @read_only = 1; Then when you need to read the value from anywhere in your session, you use the follow-ing code: SELECT SESSION_CONTEXT(N'language') AS [language];This code generates the following output:Language ----------- us_english ```

Answer 98

GUID and identity functions T-SQL provides a number of solutions for generating values that you can use as keys for your rows. T-SQL also provides system functions to generate and query the newly generated keys.If you need to generate a key that is globally unique, even across systems, you use the NEWID function to generate it as a UNIQUEIDENTIFER typed value. As an example, run the following code:SELECT NEWID() AS myguid;You can run this code several times and see that every time you get a different globally unique identifier (GUID).f you want the GUIDs to always increase within the machine, use the NEWSEQUENTIALID system function instead

Answer 99

UNIQUEIDENTIFIER typed value.

Answer 100

If you need a numeric key generator, you use either a sequence object or the identity column property. The former is an independent object in the database that you create using the CREATE SEQUENCE command. Once created, every time you need a new value, you invoke the function NEXT VALUE FOR . The latter is a property of a column in a table. SQL Server generates a new key only as part of an INSERT statement that you submit against the target table, where you ignore the column with the identity property. After adding the row, you query the system function SCOPE_IDENTITY to get the last identity value that was generated in the same session and scope.

Answer 101

Gets the last identity value that was generated in the same session.

Answer 102

It is an independent object in the database, every time you need a value you invoke the function NEXT VALUE FOR .

Answer 103

Query the system function @@IDENTITY

Answer 104

They follow classic arith-metic operator precedence rules, which say that multiplication, division and modulo precede addition and subtraction. To change precedence of operations, use parentheses because they precede arithmetic operators. For example, consider the following expression:

Answer 105

Either cast or multiply the first operand by a numeric constant and force by implicit conversion.

Answer 106

Use COUNT_BIG.

Answer 107

If you want to apply an aggregate function to distinct values, add the DISTINCT clause, as in COUNT(DISTINCT custid).

Answer 108

DECLARE @cnt AS INT = (SELECT COUNT(*) FROM Sales.OrderValues); SELECT AVG(1.0 * qty) AS median, FROM ( SELECT qty FROM Sales.OrderValues ORDER BY qty OFFSET (@cnt - 1) / 2 ROWS FETCH NEXT 2 - @cnt % 2 ROWS ONLY ) AS D;

Answer 109

A search argument is a filter predicate that enables the optimizer to rely on index order.

Answer 110

Such a filter is sargable if:1.You don’t apply manipulation to the filtered column.2.The operator identifies a consecutive range of qualifying rows in the index. That’s the case with operators like =, >, >=, , LIKE with a wildcard as a prefix.

Answer 111

The sargable alternative is to use the LIKE predicate as follows:SELECT empid, lastname FROM HR.Employees WHERE lastname LIKE N'D%';When using the LIKE predicate with a known prefix SQL Server internally translates the pattern to a closed-open interval, and process the filter as a range that is greater than or equal to the first delimiter and less than the second delimiter. The plan for this query is similar to the one shown earlier in Figure 1-16

Answer 112

This query uses an equality-based comparison, where a comparison between anything and a NULL, including between two NULLs, yields unknown, and therefore the result is an empty set.

Answer 113

DECLARE @dt AS DATE = NULL; SELECT orderid, shippeddate FROM Sales.Orders WHERE EXISTS (SELECT shippeddate INTERSECT SELECT @dt);

Answer 114

Deterministic - same state, same input, same results. All aggregate functions, all string functions deterministic, all trigonometric functions. all power and log functions, all date manipulation functions, YEAR, MONTH, DAY, DATEDIFF, DATEADD. All NULL functions, NULLIF, COALESCE, ISNULL. All MATH related functions, TRIG, LOG, SIGNS, ROUNDING.

Answer 115

To get different random values in the different rows, order by NEWID, or for even better random distribution, apply CHECKSUM to NEWID as follows:SELECT TOP (3) empid, firstname, lastname FROM HR.Employees ORDER BY CHECKSUM(NEWID());

Answer 116

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

Answer 117

first turn on a session option called IDENTITY_INSERT, as follows:SET IDENTITY_INSERT ON. When you're done remember to turn it off.

Answer 118

Also, remember that SELECT INTO involves both creating a table and populating it with data. This means that both the metadata related to the target table and the data are exclusively locked with Transact-SQL locked until the SELECT INTO transaction finishes. As a result, you can run into blocking situations due to conflicts related to both data and metadata access

Answer 119

UPDATE Sales.MyOrderDetails SET discount += 0.05 WHERE orderid = 10251;

Answer 120

If you’re using a cursor to iterate through rows of a table you can modify the table row that the cursor is currently positioned on by using the filter WHERE CURRENT OF

Answer 121

SELECT OD.* FROM Sales.MyCustomers AS C INNER JOIN Sales.MyOrders AS O ON C.custid = O.custid INNER JOIN Sales.MyOrderDetails AS OD ON O.orderid = OD.orderidWHERE C.country = N'Norway'; This query identifies 16 order lines, all currently with a discount value of 0.000.In order to perform the desired update, simply replace the SELECT clause from the last query with an UPDATE clause, indicating the alias of the table that is the target for the UPDATE (OD in this case), and the assignment in the SET clause, as follows: UPDATE OD SET OD.discount += 0.05FROM Sales.MyCustomers AS C INNER JOIN Sales.MyOrders AS O ON C.custid = O.custid INNER JOIN Sales.MyOrderDetails AS OD ON O.orderid = OD.orderidWHERE C.country = N'Norway';

Answer 122

Yes. 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 it arbitrarily chooses one of the source rows. As to which source row gets chosen for each target row, the choice isn’t exactly random, but arbitrary; in other words, it’s optimization-dependent. At any rate, you do not have any logical elements in the language to control this choice.

Answer 123

UPDATE C SET C.postalcode = A.shippostalcode FROM Sales.MyCustomers AS C CROSS APPLY (SELECT TOP (1) O.shippostalcode FROM Sales.MyOrders AS O WHERE O.custid = C.custid ORDER BY orderdate, orderid) AS A;

Answer 124

DECLARE @newdiscount AS NUMERIC(4, 3) = NULL;UPDATE Sales.MyOrderDetails SET @newdiscount = discount += 0.05 WHERE orderid = 10250 AND productid = 51; SELECT @newdiscount;

Answer 125

DELETE statement is fully logged (you can find details on the transaction log at https://msdn.microsoft.com/en-us/library/ms190925.aspx) and as a result, large deletes can take a long time to complete, and much longer to roll back if you need to terminate them. Such large deletes can cause the transaction log to increase in size dramatically during the process. They can also result in lock escalation, meaning that SQL Server escalates fine-grained locks like row or page locks to a full-blown table lock. Such escalation can result in blocking access to all table data by other processes.

Answer 126

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

Answer 127

DELETE FROM dbo.MyTable WHERE CURRENT OF MyCursor;

Answer 128

TRUNCATE table statement uses an optimized logging mode and therefore is significantly faster.

Answer 129

Besides the performance difference and the fact that TRUNCATE TABLE doesn’t support a filter, there are a few additional differences compared to the DELETE statement:■You cannot assign direct TRUNCATE TABLE permissions, rather at minimum you need ALTER permission on the target table. A common workaround is to place the TRUN-CATE TABLE statement in a module, like a stored procedure, and assign the required permission to the module using the EXECUTE AS clause.■If there’s a column with an identity property in the target table, DELETE doesn’t reset the property whereas TRUNCATE TABLE does.■If there are any foreign keys pointing to the target table, a DELETE statement is sup-ported as long as there are no related rows in the referencing table, but a TRUNCATE TABLE statement isn’t. You need to first drop the foreign keys, truncate the table, and then recreate the foreign keys.■If there are any indexed views based on the table, a DELETE statement is supported whereas a TRUNCATE TABLE statement isn’t.

Answer 130

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

Answer 131

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

Answer 132

CREATE SEQUENCE Sales.SeqOrderIDs AS INT MINVALUE 1 CACHE 10000; Notice that the sequence is defined to start with the value 1, and uses a cache size of 10,000 for performance reasons. The cache size defines how frequently to write a recoverable value to disk. To request a new key from the sequence, you use the function NEXT VALUE FOR . Our code defines a default constraint with the function call for the orderid column to automate the creation of keys when new rows are inserted.

Answer 133

To prevent such a failure, use the hint SERIALIZABLE or HOLDLOCK (both have equivalent meanings) against the target as shown in the previous statement. This hint means that the statement uses a serializable isolation level to serialize access to the data, meaning that once you get access to the data, it’s as if you’re the only one interacting with it

Answer 134

DECLARE @orderid AS INT = 1, @custid AS INT = 1, @empid AS INT = 2, @orderdate AS DATE = '20170212'; MERGE INTO Sales.MyOrders WITH (SERIALIZABLE) AS TGT USING (VALUES(@orderid, @custid, @empid, @orderdate)) AS SRC( orderid, custid, empid, orderdate) ON SRC.orderid = TGT.orderid

Answer 135

Alternatively, similar to the way you matched rows in a join using a set operator, you can identify a difference here between the source and target rows as follows:WHEN MATCHED AND EXISTS( SELECT SRC.* EXCEPT SELECT TGT.* ) THEN UPDATE

Answer 136

What’s interesting about the USING clause where you define the source for the MERGE op-eration is that it’s designed like the FROM clause in a SELECT statement. This means that you can define table operators like JOIN, APPLY, PIVOT, and UNPIVOT; and use table expressions like derived tables, CTEs, views, inline table functions, and even table functions like OPEN-ROWSET and OPENXML. You can refer to real tables, temporary tables, or table variables as the source. Ultimately, the USING clause returns a table result, and that table result is used as the source for the MERGE statement.

Answer 137

RUNCATE TABLE Sales.MyOrders; ALTER SEQUENCE Sales.SeqOrderIDs RESTART WITH 1;

Answer 138

Remember that one MERGE statement can apply different actions against the target table. And suppose that 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’, ‘UP-DATE’, or ‘DELETE’) indicating the action.

Answer 139

WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action AS the_action, COALESCE(inserted.orderid, deleted.orderid) AS orderid;

Answer 140

Yes. MERGE INTO Sales.MyOrders AS TGT USING ( SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE shipcountry = N’Norway’ ) AS SRC ON 1 = 2 WHEN NOT MATCHED THEN INSERT(custid, empid, orderdate) VALUES(custid, empid, orderdate)OUTPUT SRC.orderid AS srcorderid, inserted.orderid AS tgtorderid, inserted.custid, inserted.empid, inserted.orderdate;

Answer 141

Yes, you can to catpure OUTPUT data, but you can only use the WHERE clause.

Answer 142

WITH VALUES clause as follows:ALTER TABLE Sales.MyOrders ADD requireddate DATE NOT NULL CONSTRAINT DFT_MyOrders_requireddate DEFAULT ('19000101') WITH VALUES;

Answer 143

■Is used in an index. ■Is used in a default, check, foreign key, unique, or primary key constraint. ■Is bound to a default object or a rule.

Answer 144

If there are NULLs present in the data, an attempt to add the NOT NULL constraint fails. In a similar way, attempting to add a primary key or unique constraint fails if duplicates exist in the data. With check and foreign key constraints you do have control over whether existing data is verified or not. By default SQL Server uses a WITH CHECK mode that verifies that the existing data meets the constraint’s requirements, and fails the attempt to add the constraint if the data is invalid. However, you can specify the WITH NOCHECK option to ask SQL Server not to verify existing data.

Answer 145

For many column alteration operations, SQL Server supports indicating the option ONLINE = ON (it is OFF by default). With this option set to ON, the table is available while the alter operation is in progress. Examples for operations that can be done online include a change in the data type, nullability, precision, length and others.

Answer 146

Subqueries can be self-contained or correlated.

Answer 147

Queries that have no dependency on the outer query.

Answer 148

Note that if what’s supposed to be a scalar subquery returns in practice more than one value, the code fails at run time. If the scalar subquery returns an empty set, it is converted to a NULL.

Answer 149

Those are ALL, ANY, and SOME.

Answer 150

SELECT productid, productname, unitprice FROM Production.Products WHERE unitprice <= ALL (SELECT unitprice FROM Production.Products); The way the query is phrased is “return the products where the unit price is less than or equal to all product unit prices.”

Answer 151

The ANY and SOME predicates have identical meaning. SELECT productid, productname, unitprice FROM Production.Products WHERE unitprice > ANY (SELECT unitprice FROM Production.Products); The way the query is phrased is “return the products where the unit price is greater than any product unit prices.” This will be false only for the product with the minimum price.

Answer 152

SELECT categoryid, productid, productname, unitprice FROM Production.Products AS P1 WHERE unitprice = (SELECT MIN(unitprice) FROM Production.Products AS P2 WHERE P2.categoryid = P1.categoryid); Notice that the outer query and the inner query refer to different instances of the same table, Production.Products. In order for the subquery to be able to distinguish between the two, you must assign different aliases to the different instances.

Answer 153

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

Answer 154

When comparing the performance of solutions using subqueries versus solutions using joins, you will find that it’s not like one tool always performs better than the other.

Answer 155

The APPLY operator is a powerful operator that you can use to apply some query logic to each row from a table. The operator evaluates the left input first, and for each of its rows, applies a derived table query or table function that you provide as the right input.

Answer 156

This means that you can replace the use of cursors in some cases with the APPLY operator.

Answer 157

The two forms of the APPLY operator—CROSS and OUTER.

Answer 158

The CROSS APPLY operator operates on left and right 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. The reason that this operator is called CROSS APPLY is that per the left row, the operator behaves like a cross join between that row and the result set returned for it from the right input. Figure 2-3 shows an illustration of the CROSS APPLY operator.

Answer 159

Instead, you can use the CROSS APPLY operator, like so: SELECT S.supplierid, S.companyname AS supplier, A.* FROM Production.Suppliers AS S CROSS APPLY (SELECT TOP (2) productid, productname, unitprice FROM Production.Products AS P WHERE P.supplierid = S.supplierid ORDER BY unitprice, productid) AS A WHERE S.country = N'Japan';

Answer 160

The OUTER APPLY operator extends what the CROSS APPLY operator does by also including in the result rows from the left side that get an empty set back from the right side. NULLs are used as placeholders for the result columns from the right side.

Answer 161

T-SQL supports four forms of table expressions: derived tables, common table expressions (CTEs), views and inline table-valued functions.

Answer 162

Note that because a table expression is supposed to represent a relation, the inner query defining it needs to be relational. This means that all columns returned by the inner query must have names (use aliases if the column is a result of an expression), and all column names must be unique.

Answer 163

Table expressions, it first unnests, or inlines, the table expression’s logic, and therefore interacts with the underlying tables directly. It does not somehow persist the table expression’s result in an internal work table and then interact with that work table. If for optimization reasons you do need to persist the result of a query for further processing, you should be using a temporary table or table variable. Table expressions are good for data that you only need to interact with once.

Answer 164

Between table variables and temporary tables, the main difference from an optimization perspective is that SQL Server maintains full blown statistics on temporary tables but very minimal statistics on table variables. Therefore, cardinality estimates (estimates for row counts during optimization) tend to be more accurate with temporary tables. So, when dealing with very small amounts of data like just a few rows, typically it’s recommended to use table variables since that’s the assumption that the optimizer makes any way. With larger table sizes, the recommendation is to use temporary tables, to allow better estimates, that will hopefully result in more optimal plans.

Answer 165

There are cases where you will get more optimal treatment when using temporary tables (which you create like regular tables, and name with a # sign as a prefix, such as #T1) or table variables (which you declare, and name with the @ sign as a prefix, such as @T1). That’s typically the case when you have some expensive query, like one that scans large tables, joins them, and groups and aggregates the data. You need to interact with that query result multiple times—whether with a single query that joins multiple instances of the result or with multiple separate queries. If you use a table expression, the physical treatment repeats the work for each reference. In such cases you want to persist the result of the expensive work in a temporary table or table variable, and then interact with that temporary object a number of times.

Answer 166

A derived table is a named table subquery.

Answer 167

Only in the SELECT and ORDER BY clauses.

Answer 168

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; (Page 144).

Answer 169

Two column aliasing options are available to you when working with derived tables: both inline and external. With the inline form, you specify the column alias as part of the expression, as in AS alias.

Answer 170

With the external aliasing form, you don’t specify result column aliases as part of the column expressions; instead, you name all target columns right after the derived table’s name, as in FROM (…) AS D(rownum, categoryid, productid, productname, unitprice). With the external form, you must specify all target column names and not just those that are results of computations. If you use both inline and external aliases, the external ones prevail as far as the outer query is concerned.

Answer 171

One is the nested logic of multiple derived tables is complicated. The other is that a join treats its wo inputs as a set, meaning no order;the two inputs are evaluated in an all-at-once manner. As a result, if you define a dervied table as the left input of the join, that derived table is not visible to the right input of the join. Causing repetition of the code.

Answer 172

If the inner query performs expensive work, and generates a fairly small result set, a table expression might not be the best choice.

Answer 173

initial query (anchor member), recursive query, and termination condition

Answer 174

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;

Answer 175

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

Answer 176

DROP FUNCTION IF EXISTS HR.GetManagers; GO 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

Answer 177

For this, T-SQL provides the HAVING clause. Like the WHERE clause, the HAVING clause uses a predicate but evaluates the predicate per group as opposed to per row.

Answer 178

When using empty parentheses, the OVER clause represents the entire underlying query’s result set. For example, the expression SUM(val) OVER() represents the grand total of all rows in the underlying query.

Answer 179

No, the former ignores NULLs in the shippeddate column and therefore the counts are less than or equal to those produced by the latter.

Answer 180

You use them in the GROUP BY clause.

Answer 181

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

Answer 182

T-SQL supports two additional clauses called CUBE and ROLLUP, which you can consider as abbreviations of the GROUPING SETS clause.

Answer 183

The CUBE clause 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.

Answer 184

As mentioned, NULLs are used as placeholders when an element isn’t part of the grouping set. If all grouped columns disallow NULLs in the underlying table, you can identify the rows that are associated with a single grouping set based on a unique combination of NULLs and non-NULLs in those columns. A problem arises in identifying the rows that are associated with a single grouping set when a grouped column allows NULLs.

Answer 185

The GROUPING function accepts a single element as input and returns 0 when the element is part of the grouping set and 1 when it isn't. In other words, 0 defines a detail element and 1 defines a hyperaggregate.

Answer 186

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

Answer 187

The GROUPING_ID function takes the same list in the GROUP BY clause. It creates a binary string and then converts that binary string to decimal. The binary string is created by using the GROUPING function per columns, so GROUPING_ID(A, B, C) is the same as GROUPING(A) + GROUPING(B) + GROUPING(C). The result is a string of 1 AND 0 such as 101, that number is then converted to a decimal.

Answer 188

When you specify multiple GROUPING SETS, CUBE, ROLLUP clauses in GROUP BY cluase seperated by commas.

Answer 189

When you specify multiple GROUPING SETS, CUBE, ROLLUP clauses in the GROUP BY clause separated by commas, e.g. CUBE(a, b, c) 8 and ROLLUP(x, y, z) 4 you get 32

Answer 190

``` If you place a CUBE or ROLLUP clause within a GROUPING SETS clause, you achieve an addition effect. GROUPING SETS( (x, y, z), (z), CUBE(a, b, c) ) adds two grouping sets defined explicitly by the GROUPING SETS clause and the eight defined implicitly by the CUBE clause and produces ten grouping sets. ```

Answer 191

Pivoting moves from a state of rows to a state of columns. On rows or grouping elements, on cols or spreading element, on data or aggregation element.

Answer 192

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

Answer 193

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

Answer 194

From an indexing standpoint, an optimal index to support window functions is one created on the partitioning and ordering elements as the key list, and includes the rest of the elements from the query for coverage. I like to think of this index as a POC index as an acronym for partitioning, ordering and covering. With such an index in place SQL Server won’t need to explicitly sort the data, rather pull it preordered from the index.

Answer 195

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. You invoke the window function in the SELECT clause of the inner query, assigning the expression with a column alias. Then you can refer to that column alias in the outer query in all clauses.

Answer 196

As another example for a window frame extent, if you wanted the frame to include only the last three rows, you would use the form ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

Answer 197

The ROWS option gets optimized better than RANGE when using the same delimiters. If you define a window

Answer 198

One subtle difference between ROWS and RANGE when using the same delimiters is that the former doesn’t include peers (tied rows in terms of the ordering values) and the latter does.

Answer 199

No, if you need a guarantee you need to add a presentation ORDER BY clause.

Answer 200

Because RANK considers the count of rows and DENSE_RANK considers the count of distinct values, the former can have gaps between result ranking values, and the latter cannot have gaps.

Answer 201

Window offset functions return an element from a single row that is in a given offset from the current row int he windows partition, or from the first or last row in the window frame.

Answer 202

The LAG and LEAD functions rely on an offset with respect to the current row, and the FIRST_ VALUE and LAST_VALUE functions operate on the first or last row in the frame, respectively.

Answer 203

you want a different offset than 1, you specify it as the second argument, as in LAG(val, 3). Notice that if a row does not exist in the requested offset, the function returns a NULL by default. If you want to return a different value in such a case, specify it as the third argument, as in LAG(val, 3, 0).

Answer 204

Window offset functions return an element from a single row that is in a given offset from the current row int he windows partition, or from the first or last row in the window frame.

Answer 205

The LAG and LEAD functions rely on an offset with respect to the current row, and the FIRST_ VALUE and LAST_VALUE functions operate on the first or last row in the frame, respectively.

Answer 206

you want a different offset than 1, you specify it as the second argument, as in LAG(val, 3). Notice that if a row does not exist in the requested offset, the function returns a NULL by default. If you want to return a different value in such a case, specify it as the third argument, as in LAG(val, 3, 0).

Answer 207

When you don't specify an explicity window frame clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Answer 208

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 ORDER BY custid, orderdate, orderid;

Answer 209

When you don't specify an explicity window frame clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Answer 210

For performance reasons.

Answer 211

The difference is in the way duplicate rows are treated. ROWS treat duplicates as distinct values, where as RANGE treats them as a single entity.

Answer 212

If you use RANGE which is the default, then duplicate are not added in the aggregate so you're running total won't be correct, it won't change for that value.

Answer 213

``` SELECT Name, Gender, Salary, LAST_VALUE(Name) OVER( ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastValue FROM Employees; ```

Answer 214

1, NULL. LAG(column, offset, default value)

Answer 215

With an option called SYSTEM_VERSIONSING.At the date of writing SQL Server currently supports only system-versioned temporal tables, meaning that the system transaction time determines the effective time of the change.

Answer 216

You use a clause called FOR SYSTEM_TIME.

Answer 217

You can set a change to be effective in a future period. For example, suppose that there's a planned price change of a product during a future period.

Answer 218

``` validfrom DATETIME2(3) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, validto DATETIME2(3) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, PERIOD FOR SYSTEM_TIME (validfrom, validto) ) WITH ( SYSTEM_VERSIONING = ON ( HISTORY_TABLE = dbo.ProductsHistory ) ); ```

Answer 219

SQL Server will create it for you. with the MSSQL_TemporalHistoryFor_.

Answer 220

If you update the same row multiple times in the same transaction, a curious thing happens. The original and last states of the row will naturally have nonzero length intervals as the validity period; however, the in-between states will have degenerate intervals as the validity period where the validfrom value will be equal to the validto value. That’s the result of using the transaction start time as the effective time of all changes in the transaction.

Answer 221

SELECT productid, supplierid, unitprice FROM dbo.Products FOR SYSTEM_TIME AS OF '20161101 14:06:00.000';

Answer 222

Yes, it is a case-sensitive UNICODE text.

Answer 223

If every begin tag has a corresponding end tag, and if tags are nested properly, the XML document is well-formed.

Answer 224

They're considered markup.

Answer 225

They need to be escaped using the special code &

Answer 226

As you can see from the example, elements can have attributes. Attributes have their own names, and their values are enclosed in quotes. This is attribute-centric presentation. However, you can write XML differently; every attribute can be a nested element of the original element. This is element-centric presentation..

Answer 227

A document has a single root node, if you delete the node you get a fragment.

Answer 228

Element names do not have to be unique, because they can be referred to by their position; however, to distinguish between elements from different business areas, different departments, or different companies, you can add namespaces.

Answer 229

In RAW mode, every row from the returned result set converts to a single element named row, and columns translate to the attributes of this element.

Answer 230

1 Customer NRZBB 10692 | 2015-10-03 … 2 Customer MLTDN …

Answer 231

1 Customer NRZBB 10692 2015-10-03 … 2 Customer MLTDN … (Page 196).

Answer 232

With the SELECT statement, you are actually formatting the returned XML. Without the ORDER BY clause, the order of rows returned is unpredictable, and you can get a weird XML document with an element repeated multiple times with just part of nested elements every time.

Answer 233

Converting XML to relational tables is known as shredding XML. You SHRED XML with the OPENXML rowset function.

Answer 234

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

Answer 235

No, You will notice that this article considers all window ranking functions as nondeterministic. That’s because it seems that SQL Server doesn’t consider the partitioning and ordering values technically as the function’s inputs.

Answer 236

The following are window offset functions, LAG, LEAD, FIRST_VALUE, and LAST_VALUE.

Answer 237

attribute-centric and element-centric

Answer 238

No, element names do not have to be unique, beause they can be referred to by their position. However, you can add namespaces to distinguish between the different elements.

Answer 239

In the root of the element. You can also use an alias for every single namespace.

Answer 240

XML Schema Description XSD.

Answer 241

It is called validating the document. A document with a predefined schema is said to be a typed XML document.

Answer 242

It is a fragment because it is missing a root element.

Answer 243

Even though all three SQL Sever function returns the current date time in SQL Server, there are some subtle differences between them. The main difference between GETDATE() and SYSDATETIME() is that GETDATE returns current date and time as DATETIME but SYSDATETIME returns a DATETIME2 value, which is more precise. The difference between GETDATE() and GETUTCDATE() is in timezone, the GETDATE() function return current date and time in the local timezone, the timezone where your database server is running, but GETUTCDATE() return current time and date in UTC (Universal Time Coordinate) or GMT timezone.

Answer 244

As seen in the above table, the datetime2 type has many advantages over datetime, including: larger date range larger default fractional precision optional user-specified precision higher accuracy, even when using using the same number of decimal places as datetime (i.e. 3) less storage size when using using the same number of decimal places as datetime, yet with higher accuracy* the option to use 2 bytes less storage than datetime (albeit with lower precision)* aligns with the SQL standards (ANSI & ISO 8601) * In some cases a datetime2 value uses an extra byte to store the precision, which would result in the same storage size as datetime when using the same number of decimal places. Microsoft recommends datetime2 over datetime for new work.

Answer 245

It is recommend never using BETWEEN unless you are dealing with the DATE data type or have otherwise guaranteed that your datetime values will never have a time component. One thing to be careful of, is if you are using this against a DATETIME, the match for the end date will be the beginning of the day: <= 20/10/2009 is not the same as: <= 20/10/2009 23:59:59 (it would match against <= 20/10/2009 00:00:00.000)

Answer 246

3NF – The Third Normal form is the normal form most often sought after for practical database implementations. It was loosely summed up by E.F. Codd saying “Every Non-key attribute must provide a fact about the key, the whole key, and nothing but the key.”

Answer 247

Give examples of clauses:

Answer 248

A SQL statement is a complete sentence that is usually terminated with a semicolon

Answer 249

Clauses can have sub-clauses. these are like INNER JOIN … ON or IN. These sub-clauses have no life of their own outside the clause they are placed within. You can’t put an IN clause anywhere outside a WHERE clause, for example. You cant have an INNER JOIN sub-clause outside its FROM clause.

Answer 250

8. SELECT 9. DISTINCT 11. TOP 1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. WITH CUBE/ROLLUP 7. HAVING 10. ORDER BY 12. OFFSET/FETCH 13. FOR XML Let’s arrange the order to make it easier to read. 1. FROM 2. ON 3. JOIN 4. WHERE 5. GROUP BY 6. WITH CUBE/ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10. ORDER BY 11. TOP 12. OFFSET/FETCH 13. FOR XML Yes, and that can be short-circuited away from the logical query processing.

Answer 251

The rDBMS has the freedom to generate more than one execution plan from a statement. It compares the generated execution plans with each other and runs the one it thinks is best in the given situation. Additionally, the end-user is freed from all the gory details of data access, e.g.: Which one of a set of WHERE criteria should be evaluated first.

Answer 252

You might even consider the TOP and OFFSET-FETCH filters as being processed in their own phase number 7.

Answer 253

Predicates specify conditions which can be evaluated to a boolean value. E.g.: a boolean comparison, BETWEEN, LIKE, IS NULL, IN, SOME/ANY, ALL, EXISTS

Answer 254

Expressions: Expressions are numeric or string values by itself, or the result of arithmetic or concatenation operators, or the result of functions.

Answer 255

Object names: Names of database objects like tables, views, columns, functions.

Answer 256

``` UPDATE P SET u.assid = s.assid += 1 FROM Person P INNER JOIN Sales S ON P.id = S.udid WHERE p.name = 'Don' AND P.Date >= '20190101' ```

Answer 257

Arithmetic, concatenation, comparison, and boolean.

Answer 258

NOT, AND, OR

Answer 259

The FOR XML AUTO option gives you nice XML documents with nested elements, and it is not complicated to use. In AUTO and RAW modes, you can use the keyword ELEMENTS to produce element-centric XML.

Answer 260

WITH XMLNAMESPACES('ER70761-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 WHERE [co:Customer].custid <= 2 AND [co:Order].orderid % 2 = 0 ORDER BY [co:Customer].custid, [co:Order].orderid FOR XML AUTO, ELEMENTS, ROOT('CustomersOrders');

Answer 261

Without the ORDER BY clause, the order of rows returned is unpredictable, and you can get a weird XML document with an element repeated multiple times with just part of nested elements every time. It is not only the ORDER BY clause that is important; the order of columns in the SELECT clause also influences the XML returned.

Answer 262

The XSD schema.

Answer 263

Shredding.

Answer 264

To prepare the DOM presentation of XML, you need to call the system stored procedure sys.sp_xml_preparedocument.

Answer 265

sys.sp_xml_removedocument.

Answer 266

■ An XML DOM document handle, returned by sp_xml_preparedocument ■ An XPath expression to find the nodes you want to map to rows of a rowset returned ■ A description of the rowset returned ■ Mapping between XML nodes and rowset columns EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument; -- Attribute- and element-centric mapping -- Combining flag 8 with flags 1 and 2 SELECT * FROM OPENXML (@DocHandle, '/CustomersOrders/Customer', 11) WITH (custid INT, companyname NVARCHAR(40)); -- Remove the DOM EXEC sys.sp_xml_removedocument @DocHandle; (Page 197).

Answer 267

XQuery returns sequences. Sequences can include atomic values or complex values (XML nodes). Any node, such as an element, attribute, text, processing instruction, comment, or document, can be included in the sequence. Of course, you can format the sequences to get well-formed XML.

Answer 268

XQuery is a standard language for browsing XML instances and returning XML output.

Answer 269

XQuery data types are divided into node types and atomic types. The node types include attribute, comment, element, namespace, text, processinginstruction, and document-node. The most important atomic types you might use in queries are xs:boolean, xs:string, xs:QName, xs:date, xs:time, xs:datetime, xs:float, xs:double, xs:decimal and xs:integer. Just as there are many data types, there are dozens of functions in XQuery as well.

Answer 270

The real power of XQuery lies in the so-called FLWOR expressions. FLWOR is the acronym for for, let, where, order by, and return. A FLWOR expression is actually a for each loop. You can use it to iterate through a sequence returned by an XPath expression. Although you typically iterate through a sequence of nodes, you can use FLWOR expressions to iterate through any sequence. You can limit the nodes to be processed with a predicate, sort the nodes, and format the returned XML. The parts of a FLWOR statement are: ■ For With a for clause, you bind iterator variables to input sequences. Input sequences are either sequences of nodes or sequences of atomic values. You create atomic value sequences using literals or functions. ■ Let With the optional let clause, you assign a value to a variable for a specific iteration. The expression used for an assignment can return a sequence of nodes or a sequence of atomic values. ■ Where With the optional where clause, you filter the iteration. ■ Order by Using the order by clause, you can control the order in which the elements of the input sequence are processed. You control the order based on atomic values. ■ Return The return clause is evaluated once per iteration, and the results are returned to the client in the iteration order. With this clause, you format the resulting XML. (Page 199).

Answer 271

The real power of XQuery lies in the so-called FLWOR expressions. FLWOR is the acronym for for, let, where, order by, and return. A FLWOR expression is actually a for each loop. You can use it to iterate through a sequence returned by an XPath expression. Although you typically iterate through a sequence of nodes, you can use FLWOR expressions to iterate through any sequence. You can limit the nodes to be processed with a predicate, sort the nodes, and format the returned XML. The parts of a FLWOR statement are: ■ For With a for clause, you bind iterator variables to input sequences. Input sequences are either sequences of nodes or sequences of atomic values. You create atomic value sequences using literals or functions. ■ Let With the optional let clause, you assign a value to a variable for a specific iteration. The expression used for an assignment can return a sequence of nodes or a sequence of atomic values. ■ Where With the optional where clause, you filter the iteration. ■ Order by Using the order by clause, you can control the order in which the elements of the input sequence are processed. You control the order based on atomic values. ■ Return The return clause is evaluated once per iteration, and the results are returned to the client in the iteration order. With this clause, you format the resulting XML.

Answer 272

Yes, however, plain text representations but it means having no knowledge of the structure built into an XML document.

Answer 273

To test if a specific node exists in an XML instance. Similar to where in SQL.

Answer 274

Similarly, you can index XML columns with specialized XML indexes. The first index you create on an XML column is the primary XML index. This index contains a shredded persisted representation of the XML values.

Answer 275

The primary XML index has to be created first. It can be created only on tables with a clustered primary key.

Answer 276

``` DECLARE @json AS NVARCHAR(MAX)= N' { "Customer":{ "Id":1, "Name":"Customer NRZBB", "Order":{ "Id":10692, "Date":"2015-10-03", "Delivery":null } } }'; SELECT * FROM OPENJSON(@json) WITH(CustomerId INT '$.Customer.Id', CustomerName NVARCHAR(20) '$.Customer.Name', Orders NVARCHAR(MAX) '$.Customer.Order' AS JSON); ```

Answer 277

ISJSON: tests the validity of a JSON document JSON_VALUE: extracts JSON fragments and values JSON_QUERY: extract array JSON_MODIFY: modify JSON

Answer 278

derived tables, CTEs, views and inline tablevalued functions.

Answer 279

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

Answer 280

WITH C AS (SELECT custid, ISNULL([1], 0.00) AS [1], ISNULL([2], 0.00) AS [2], ISNULL([3], 0.00) AS [3] FROM Sales.FreightTotals) SELECT custid, shipperid, NULLIF(freight, 0.00) AS freight FROM C UNPIVOT(freight FOR shipperid IN([1], [2], [3])) AS U;

Answer 281

SQL Server 2016 starting with Service Pack 1 and Azure SQL Database

Answer 282

A view is a reusable named query, or table expression, whose definition is stored as an object in the database. It is accessible to users who were granted with permission to query it. You can also modify data in underlying tables through it.

Answer 283

If you need to alter the structure of tables, you can use views to provide the application with backward compatible representation of the data. Views can also be used as a security layer, by granting users with access to the view but not to the underlying tables. This way, users can see only the customized representation of the data that you want them to see.

Answer 284

No, unless it supports a TOP or OFFSET filter.

Answer 285

No, it internally keeps the query text and some additional metadata information about the view and its columns in catalog objects.

Answer 286

PRINT OBJECT_DEFINITION(OBJECT_ID(tablename));

Answer 287

This option prevents structural changes to the underlying objects while the view exists.

Answer 288

T-SQL supports a view attribute called ENCRYPTION that causes SQL Server to obfuscate the object definition that is stored internally.

Answer 289

CREATE OR ALTER VIEW Sales.USACusts WITH SCHEMABINDING, ENCRYPTION AS SELECT custid,

Answer 290

expressions. One of them is that if the inner query joins multiple tables, INSERT and UPDATE statements are allowed to affect only one target table at a time. Also, you cannot insert rows through a table expression if it doesn’t include at least one column from the underlying table that doesn’t somehow get its values automatically (for example by allowing NULLs or having a default value).

Answer 291

T-SQL supports an option called CHECK OPTION that prevents inserting or updating rows through the view if the change contradicts the inner query’s filter. This

Answer 292

``` CREATE OR ALTER VIEW Sales.USACusts WITH SCHEMABINDING AS SELECT custid, companyname, contactname, contacttitle, address, city, region, postalcode, country, phone, fax FROM Sales.Customers WHERE country = N'USA' WITH CHECK OPTION; GO ```

Answer 293

Create a clustered index on the view, and this way you persist the view's result within the clustered index B-tree structure.

Answer 294

The VIEW header has to have the WITH SCHEMABINDING attribute and another is if the query is grouped then it has to include the COUNT_BIG attribute. Also, any aggregate value cannot then manipulated suc as, CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount)) AS NUMERIC(12, 2)) AS val, The CAST function on the aggregate value will cause the INDEX to fail.

Answer 295

SQL Server needs to track the group row counts to know when a group needs to be eliminated as a result of deletes or updates of underlying detail rows. Here is an example:

Answer 296

``` CREATE OR ALTER VIEW Sales.OrderTotals WITH SCHEMABINDING AS SELECT O.orderid, O.custid, O.empid, O.shipperid, O.orderdate, O.requireddate, O.shippeddate, SUM(OD.qty) AS qty, CAST(SUM(OD.qty * OD.unitprice * (1 - OD.discount)) AS NUMERIC(12, 2)) AS val FROM Sales.Orders AS O INNER JOIN Sales.OrderDetails AS OD ON O.orderid = OD.orderid GROUP BY O.orderid, O.custid, O.empid, O.shipperid, O.orderdate, O.requireddate, O.shippeddate; GO ```

Answer 297

You will need to include the NOEXPAND. | SELECT * FROM TABLE WITH (NOEXPAND)

Answer 298

A user-defined function (UDF) is a routine that accepts parameters, applies calculations, and returns either a scalar-valued or a table-valued result. SQL Server supports developing functions using either T-SQL, or the common language runtime (CLR).

Answer 299

A user-defined function can appear in places in your code where a scalar-valued or table-valued expression can appear, such as in a query, a computed column, and a CHECK constraint. It can even appear in the definition of another user-defined function. You can use a user-defined function to replace a stored procedure when you want to be able to query its result. You can use it as an alternative to a view with parameters, since views don’t support parameters. You can even use a user-defined function to query an indexed view, and with parameter support, improve its functionality. You can also use a user-defined function to define a filter for a security policy as part of an implementation of row level security.

Answer 300

``` ■ Use error handling ■ Modify data (other than in table variables) ■ Use data definition language (DDL) ■ Use temporary tables ■ Use dynamic SQL ```

Answer 301

T-SQL supports three kinds of user-defined functions: scalar, inline table-valued, multistatement table-valued

Answer 302

A scalar user-defined function accepts parameters, applies calculations, and returns a single value.

Answer 303

``` CREATE OR ALTER FUNCTION dbo.SubtreeTotalSalaries(@mgr AS INT) RETURNS MONEY WITH SCHEMABINDING AS BEGIN DECLARE @totalsalary AS MONEY; WITH EmpsCTE AS (SELECT empid, salary FROM dbo.Employees WHERE empid = @mgr UNION ALL SELECT S.empid, S.salary FROM EmpsCTE AS M INNER JOIN dbo.Employees AS S ON S.mgrid = M.empid) SELECT @totalsalary = SUM(salary) FROM EmpsCTE; RETURN @totalsalary; END; ```

Answer 304

With scalar UDFs, you must use the two-part name including the schema.

Answer 305

when you invoke the function, if you wish to rely on the default value, you must specify the keyword DEFAULT instead of passing a value. You cannot just omit the parameter like you do with stored procedures.

Answer 306

When you invoke a nondeterministic builtin function indirectly, from within a user-defined function, and then invoke the user-defined function in a query, the function gets executed once per row.

Answer 307

As mentioned, most non-deterministic built-in functions are invoked once per query. Also observe that NEWID behaves differently; it is invoked once per row. (Page 242).

Answer 308

T-SQL supports invoking nondeterministic built-in functions within user-defined functions, as long as they don’t have any side effects on the system. The functions SYSDATETIME, RAND (without an input seed), and NEWID are all nondeterministic functions. The SYSDATETIME function doesn’t have any side effects on the system and therefore is allowed in user-defined functions. As an example, run the following code to create the user-defined function MySYSDATETIME: CREATE OR ALTER FUNCTION dbo.MySYSDATETIME() RETURNS DATETIME2 AS BEGIN RETURN SYSDATETIME(); END; GO The function is created successfully. The NEWID and RAND functions both have side effects in the sense that one function call leaves a mark behind that affects a subsequent function call. Consequently, you’re not allowed to invoke NEWID and RAND within user-defined functions. As an example, run the following code in attempt to create a user-defined function called MyRand that calls the built-in RAND function:

Answer 309

Curiously, if you invoke the built-in side-effecting function from a view, SQL Server does allow you to query the view from within a user-defined function. This allows you to circumvent the aforementioned restriction. As an example, run the following code to create a view called VRAND that invokes the built-in function RAND and returns the result as a column called myrand:

Answer 310

Next, run the following code to create the function MyRAND, which returns the result of a query against the view: run the following code to query the Sales.Orders table and invoke the functions MySYSDATETIME and MyRAND: SELECT orderid, dbo.MySYSDATETIME() AS mysysdatetime, dbo.MyRAND() AS myrand FROM Sales.Orders;

Answer 311

The function needs to be deterministic.

Answer 312

CREATE TABLE dbo.T1 ( keycol INT NOT NULL IDENTITY CONSTRAINT PK_T1 PRIMARY KEY, dt DATE NOT NULL, dtendofyear AS dbo.ENDOFYEAR(dt) PERSISTED );

Answer 313

An inline table-valued user-defined function is very similar in concept to a view in the sense that it’s based on a single query, and you interact with it like a table expression, only unlike a view, it supports input parameters. So you could think of such a function as a parameterized view.

Answer 314

The reason that it’s called an inline function is because SQL Server inlines, or expands, the inner query definition, and constructs an internal query directly against the underlying tables.

Answer 315

CREATE OR ALTER FUNCTION dbo.GetPage(@pagenum AS BIGINT, @pagesize AS BIGINT) RETURNS TABLE WITH SCHEMABINDING AS RETURN ``` WITH C AS (SELECT ROW_NUMBER() OVER( ORDER BY orderdate, orderid) AS rownum, orderid, orderdate, custid, empid FROM Sales.Orders) SELECT rownum, orderid, orderdate, custid, empid FROM C WHERE rownum BETWEEN(@pagenum - 1) * @pagesize + 1 AND @pagenum * @pagesize; GO ```

Answer 316

Notice that the function’s header defines the input parameters and says that it returns a table result (RETURN TABLE). There’s no body to the function (no BEGIN-END block), rather just a RETURN clause with a query.

Answer 317

A multistatement table-valued user-defined function is a table function, so from the user’s perspective, it’s used as a source table in a query much like an inline table-valued function is used. However, instead of being based on a single query, the multistatement function declares a returned table variable in its header, and then its body is responsible for filling the returned table variable with rows. Whenever you query the function, behind the scenes SQL Server creates the table variable that is defined in the function’s header, runs the flow in the function’s body to fill it with rows, and then as soon as the function executes the RETURN command, SQL Server hands the table variable to the calling query.4

Answer 318

CREATE FUNCTION dbo.GetSubtree (@mgrid AS INT, @maxlevels AS INT = NULL ) RETURNS @Tree TABLE (empid INT NOT NULL PRIMARY KEY, mgrid INT NULL, empname VARCHAR(25) NOT NULL, salary MONEY NOT NULL, lvl INT NOT NULL, sortpath VARCHAR(892) NOT NULL, INDEX idx_lvl_empid_sortpath NONCLUSTERED(lvl, empid, sortpath) ) WITH SCHEMABINDING AS BEGIN DECLARE @lvl AS INT= 0; -- insert subtree root node into @Tree INSERT INTO @Tree(empid, mgrid, empname, salary, lvl, sortpath) SELECT empid, NULL AS mgrid, empname, salary, @lvl AS lvl, ‘.’ AS sortpath FROM dbo.Employees WHERE empid = @mgrid; WHILE @@ROWCOUNT > 0 AND (@lvl < @maxlevels OR @maxlevels IS NULL) BEGIN SET @lvl+=1; -- insert children of nodes from prev level into @Tree INSERT INTO @Tree (empid, mgrid, empname, salary, lvl, sortpath ) SELECT S.empid, S.mgrid, S.empname, S.salary, @lvl AS lvl, M.sortpath + CAST(S.empid AS VARCHAR(10)) + '.' AS sortpath FROM dbo.Employees AS S INNER JOIN @Tree AS M ON S.mgrid = M.empid AND M.lvl = @lvl - 1; END; RETURN; END; GO

Answer 319

If you’re wondering why bother implementing a task as a multistatement function instead of an inline one, there could be a number of reasons. The recursive query in the inline function uses a spool (worktable) to store the intermediate results, but you have no control over the indexing on that spool. With the multistatement function, you control the definition of the table variable, including indexing. Furthermore, the inline function is only allowed to return a query, whereas the multistatement function can have complete flow with procedural logic.

Answer 320

A stored procedure is a reusable routine that supports input and output parameters, and even returning result sets of queries. This section starts with a simple example for using input parameters and returning a result set of a query.

Answer 321

``` CREATE OR ALTER PROC dbo.GetOrders @orderid AS INT = NULL, @orderdate AS DATE = NULL, @custid AS INT = NULL, @empid AS INT = NULL AS SET XACT_ABORT, NOCOUNT ON; SELECT orderid, orderdate, shippeddate, custid, empid, shipperid FROM Sales.Orders WHERE(orderid = @orderid OR @orderid IS NULL) AND (orderdate = @orderdate OR @orderdate IS NULL) AND (custid = @custid OR @custid IS NULL) AND (empid = @empid OR @empid IS NULL); GO ```

Answer 322

The stored procedure’s code starts by setting the options XACT_ABORT and NOCOUNT to ON. The XACT_ABORT option determines the effect of run-time errors raised by T-SQL statements. When this option is OFF (the default in most cases), some errors cause an open transaction to roll back and the execution of the code to be aborted, whereas other errors leave the transaction open. To get a more reliable and consistent behavior, I consider it a best practice to set this option to ON, and this way all errors cause an open transaction to be rolled back and the execution of the code to be aborted.

Answer 323

The NOCOUNT option suppresses messages indicating how many rows were affected by data manipulation statements. When it’s OFF (the default), those messages can degrade query performance due to the network traffic that they generate, plus this causes trouble for client applications that perceive those as query results.

Answer 324

For each parameter the query’s WHERE clause has the following disjunction of predicates (predicates separated by an OR operator)

Answer 325

is a technique that involves building a batch of code as a character string, usually in a variable, and then telling SQL Server to execute the code that resides in that variable.

Answer 326

Replacing the parameters with constants.

Answer 327

Using a static query in our case is not ideal in terms of query performance. The reason for this has to do with the fact that SQL Server caches the execution plan for the query for reuse in subsequent executions of the stored procedure. The cached plan has to incorporate all filter predicates and not just the ones that are related to the parameters that are applicable in the execution that gets optimized. Otherwise, SQL Server would not be able to reuse the plan in subsequent executions that specify a different set of applicable parameters.

Answer 328

One way to get efficient plans is to add the query option RECOMPILE, as in: OPTION(RECOMPILE);

Answer 329

As you can realize, the likelihood to get efficient plans with this solution is quite high. But this comes at the cost of recompiling the query in every execution of the stored procedure.

Answer 330

One of the ways to achieve this is to dynamically build the query string with only the relevant parameterized predicates, and execute the code that you built with the sp_executesql procedure. Here’s the solution code that implements this approach:

Answer 331

There’s a certain caveat related to security when using dynamic SQL. Earlier I mentioned that SQL Server supports a security model where you grant users EXECUTE permissions on a stored procedure without granting them direct permissions against the underlying objects. This way users are able to perform the task only through the stored procedure and not directly. This capability is known as ownership chaining. However, ownership chaining is limited only to SELECT, INSERT, UPDATE, and DELETE statements using static SQL, and only when the owner of the calling and the called objects is the same. Because in our case the code is executed using dynamic SQL, ownership chaining doesn’t apply and the executing user needs direct SELECT permissions against the Sales.Orders table.

Answer 332

CREATE LOGIN login1 WITH PASSWORD = ‘J345#$)thb’; GO CREATE USER user1 FOR LOGIN login1; GO GRANT EXEC ON dbo.GetOrders TO user1; GO SELECT SUSER_NAME() AS [login], USER_NAME() AS [user]; GO login user ------------------------------- ----- MicrosoftAccount\ dbo EXECUTE AS LOGIN = ‘login1’; SELECT SUSER_NAME() AS [login], USER_NAME() AS [user]; login user ------- ------ login1 user1 EXEC dbo.GetOrders @orderdate = ‘20151111’, @custid = 85; REVERT;

Answer 333

As a workaround, you can define the stored procedure with an EXECUTE AS clause, to impersonate the security context of the procedure’s execution to that of the specified entity. For example, using the option EXECUTE AS OWNER impersonates the security context to that of the stored procedure’s owner. Alternatively, you can provide a specific user name that has the right permissions. Run the following code to recreate the stored procedure using the EXECUTE AS OWNER option:

Answer 334

The aforementioned method to impersonate the security context of the procedure’s execution is quite simple and straightforward, but not always ideal in terms of auditing and monitoring capabilities. There are more complex, yet more recommended alternatives, in which you sign the stored procedure with a certificate. You associate the certificate with a user that cannot login, and grant that user with the appropriate permissions. For more information on the topic see Erland Sommarskog’s text “The Curse and Blessings of Dynamic SQL” at http://www.sommarskog.se/dynamic_sql.html and “Giving Permissions through Stored Procedures” at http://www.sommarskog.se/grantperm.html.

Answer 335

You can do this by using output parameters.

Answer 336

A situation where you want a new sequence returned.

Answer 337

A cursor allows you to iterate through rows of some query result one at a time. Solutions that use cursors tend to be lengthy because you need explicit code to define the cursor, open it, iterate through its rows, and apply some logic per row, close, and deallocate it. With a stored procedure, you can hide all that complexity. Furthermore, if at a later point you manage to find a better solution, perhaps one that doesn’t use a cursor, you can simply alter the stored procedure’s implementation. This is transparent to the users of the stored procedure.

Answer 338

INSERT INTO dbo.Transactions(txid, qty) VALUES(1,2),(2,5),(3,4),(4,1),(5,10),(6,3),(7,1),(8,2),(9,1),(10,2),(11,1),(12,9);

Answer 339

ACID Atomicity, Consistency, Isolation, Durability

Answer 340

To explicitly mark the beginning of a transaction, use the BEGIN TRANSACTION statement (or BEGIN TRAN for brevity). To end the transaction and commit its work, use the COMMIT TRANSACTION statement (supported alternatives: COMMIT TRAN, COMMIT WORK and just COMMIT). To end a transaction and roll back its work, undoing all of its changes, use the ROLLBACK TRANSACTION statement (supported alternatives: ROLLBACK TRAN, ROLLBACK WORK and just ROLLBACK).

Answer 341

@@TRANCOUNT

Answer 342

Under this mode, when you issue a transactional statement, if a transaction is not open at that point, the system is supposed to open an implicit transaction for you. However, unlike under the default autocommit mode in SQL Server, under the standard implicit transactions mode you are responsible for explicitly closing the transaction by either committing it or rolling it back. You enable the standard implicit transactions mode by setting the session option IMPLICIT_TRANSACTIONS to ON, like so (make sure you run this code for the next demo to work): SET IMPLICIT_TRANSACTIONS ON;

Answer 343

The types of statements that are considered transactional statements include DML statements (such as SELECT against a table, INSERT, UPDATE, DELETE, TRUNCATE, MERGE), many DDL statements such as creating, altering and dropping tables, DCL statements like GRANT and REVOKE, and others. Assigning values to variables as well as modifying data in table variables are not transactional operations, so if you applied such activities in a transaction that ended up rolling back, those activities are not undone.

Answer 344

No, so if you rollback during a transaction the table created in a table variable is still available.

Answer 345

No, When you issue a BEGIN TRAN statement, SQL Server increases the value of @@TRANCOUNT in order to know when it needs to truly open a transaction— when @@TRANCOUNT changes from zero to a greater-than-zero value. Issuing a BEGIN TRAN statement when @@TRANCOUNT is already greater than zero has no real effect other than increasing the @@TRANCOUNT value.

Answer 346

When you issue a BEGIN TRAN statement, SQL Server increases the value of @@TRANCOUNT in order to know when it needs to truly open a transaction— when @@TRANCOUNT changes from zero to a greater-than-zero value. Issuing a BEGIN TRAN statement when @@TRANCOUNT is already greater than zero has no real effect other than increasing the @@TRANCOUNT value. When you issue a COMMIT TRAN statement SQL Server decreases the @@TRANCOUNT value by 1. SQL Server truly commits the transaction only if you issue a COMMIT TRAN statement when @@TRANCOUNT is 1 prior to committing. However, if you issue a ROLLBACK TRAN statement in an open transaction, never mind the current value of @@TRANCOUNT, SQL Server rolls back the entire transaction.

Answer 347

Observe that every BEGIN TRAN statement increased the @@TRANCOUNT value by 1, but as soon as the ROLLBACK TRAN statement was issued, @@TRANCOUNT dropped to 0. Also, observe that the INSERT statement that added three rows to the table T1 was rolled back even though you issued a COMMIT TRAN statement after it, because that statement did not really commit the transaction.

Answer 348

Just make sure to remember that if you issue a ROLLBACK TRAN statement at any point, even if you do so from an inner module that was not the one that actually opened the transaction, this causes the entire transaction to truly roll back and all of its changes to be undone.

Answer 349

The stored procedure actually committed the transaction even though it wasn’t the one that opened it. You get an error indicating the mismatch in the @@TRANCOUNT values when entering and leaving the procedure, but this error doesn’t stop the execution of the code and doesn’t cause the transaction to roll back. The table creation gets committed and the outer batch’s ROLLBACK TRAN statement doesn’t get the chance to execute.

Answer 350

Keep in mind, though, that the above examples are executed with XACT_ABORT set to OFF, as is the case by default. If you follow best practices and set this option to ON, in the second example the error—not the ROLLBACK TRAN statement itself—causes the open transaction to roll back and the execution of the code to abort.

Answer 351

You can also specify a transaction name in the COMMIT TRAN[SACTION] statement, as in COMMIT TRAN SomeName, but with this command the name is simply ignored. Naming transactions is a very uncommonly used practice. This book covers it mainly for the small chance that it would appear in the exam.

Answer 352

You mark a savepoint with the statement SAVE TRAN[ACTION] , and roll back to a savepoint using the statement ROLLBACK TRAN[SACTION] .

Answer 353

You mark a savepoint with the statement SAVE TRAN[ACTION] , and roll back to a savepoint using the statement ROLLBACK TRAN[SACTION] .

Answer 354

Yes, To be on the safe side, some people developed a practice to always precede the THROW command with a terminator, as if the command was actually ;THROW. This way you don’t (Page 294).

Answer 355

DECLARE @msg AS NVARCHAR(2048) = FORMATMESSAGE(‘This is a user-define error that occurred on %s.’, CONVERT(CHAR(10), SYSDATETIME(), 121)); THROW 50000, @msg, 1; (Page 295).

Answer 356

When XACT_ABORT is off and you execute THROW while in an open transaction, the transaction remains open and committable. When XACT_ABORT is on and you execute THROW while in an open transaction, if you’re not using TRY-CATCH, SQL Server aborts the transaction, and if you are using TRY-CATCH, SQL Server dooms the transaction. I explain what doomed transactions are in the next section. (Page 296).

Answer 357

The RAISERROR command is the predecessor to the THROW command. Some still find it useful even when writing new code because it supports certain options that THROW doesn’t. The RAISERROR command has the following syntax: RAISERROR ( { message | messageid }, severity, state [, arguments for parameter markers ] ) [ WITH options ]; (Page 297).

Answer 358

The two most commonly used ones are LOG and NOWAIT. To raise an error with severity 19 and up you have to add the option WITH LOG, which logs the error in the SQL Server error log as well as the Windows application log. The NOWAIT option causes SQL Server to send the message immediately to the client without waiting for its internal buffer to first fill up.

Answer 359

Note that other than errors with severity 20 and up, which terminate the connection, if you raise an error with a severity that is lower than 20 this doesn’t terminate the batch, nor does this terminate or doom the transaction, irrespective of the state of the XACT_ABORT option. This is one of the drawbacks of the RAISERROR command.

Answer 360

THROW: YES, RAISERROR: NO

Answer 361

THROW: YES, RAISERROR: YES, FOR 10 < severity < 20

Answer 362

THROW: YES, RAISERROR: NO

Answer 363

THROW: NO, RAISERROR: NO

Answer 364

THROW: YES, RAISERROR: NO

Answer 365

THROW: NO, RAISERROR: YES

Answer 366

THROW: NO, RAISERROR: YES

Answer 367

THROW: NO, RAISERROR: YES

Answer 368

THROW: NO, RAISERROR: YES

Answer 369

THROW: NO, RAISERROR: YES

Answer 370

THROW: YES, RAISERROR: NO

Answer 371

As mentioned earlier, there are quite a lot of possible outcomes of errors in T-SQL. Some errors abort the batch and some don’t. Some errors abort the transaction and some don’t. Some errors even terminate the connection. You can achieve better consistency and some degree of control over the outcome of errors by turning the XACT_ABORT option to on, and by using the TRY-CATCH construct. (Page 300).

Answer 372

The problem with cases where the transaction is aborted is that any changes done by the transaction are lost. What if as part of your error handling logic you wish to investigate data that was created by the transaction before you eventually roll it back. To this end, errors that normally cause the transaction to abort when you’re not using TRY-CATCH, cause the transaction to enter a special doomed state (also known as failed, and uncommittable state). Under this state, you’re not allowed to change data other than in table variables, but you are allowed to read data. You’re not allowed to commit a doomed transaction, rather you have to eventually roll it back. You’re not allowed to roll the transaction back to a savepoint, rather it has to be a full transaction rollback. (Page 302).

Answer 373

The transaction could be open and committable, open and uncommittable (doomed), and no open transaction. If you want your error handling code to react differently depending on the transaction state, it’s not enough to check the @@TRANCOUNT value because it only tells you whether a transaction is open or not; it doesn’t tell you whether it’s open and committable or doomed. T-SQL supports an alternative function called XACT_STATE that does make the distinction between the three states. It returns 0 when no transaction is open (equivalent to @@TRANCOUNT being 0), 1 when the transaction is open and committable, and -1 (minus one) when the transaction is doomed.

Answer 374

If you turn on the XACT_ABORT option, as recommended, most errors are treated as dooming errors. For instance, the example before the last with the divide by zero error results in a doomed transaction with XACT_ABORT turned on. Remember, though, that in case an error with severity 20 and up happens, the connection is terminated, so any error handling code that you have doesn’t have a chance to run.

Answer 375

If you need to write any error information to a log table, make sure that you do so after the ROLLBACK TRAN statement and before the THROW command. If you try to do so before rolling the transaction back, you get an error saying that writes are not allowed under a doomed transaction. Remember that writes to a table variable are not undone when a transaction rolls back. Your catch block would look like this:

Answer 376

(FLOAT, REAL)

Answer 377

Also, you want to make sure that you do not confuse the formatting of a value with its type. Sometimes, people use character strings to store dates because they think of storing a date in a certain format. The formatting of a value is supposed to be the responsibility of the application when data is presented. The type is a property of the value stored in the database, and the internal storage format shouldn’t be your concern. This aspect has to do with the physical data independence principle in the relational model.

Answer 378

TINYINT is 1 byte vs INT which is 4.

Answer 379

The benefit in these types is that they can represent very large and very small numbers beyond what any other numeric type that SQL Server supports can represent. So, for example, if you need to represent very large or very small numbers for scientific purposes and don’t need complete accuracy, you may find these types useful. They’re also quite economic (4 bytes for REAL and 8 bytes for FLOAT). But do not use them for things that are supposed to be precise. (Page 308).

Answer 380

Another important aspect in choosing a type has to do with choosing fixed types (CHAR, NCHAR, BINARY) vs. dynamic ones (VARCHAR, NVARCHAR, VARBINARY). Fixed types use the storage for the indicated size; for example, CHAR(30) uses storage for 30 characters, whether you actually specify 30 characters or less. This means that updates don’t require the row to physically expand, and therefore no data shifting is required. So for attributes that get updated frequently, where the update performance is a priority, you should consider fixed types. Note that when compression is used—specifically row compression—SQL Server stores fixed types like variable ones, but with less overhead. Variable types use the storage for what you enter, plus a couple of bytes for offset information (or 4 bits with row compression). So for widely varying sizes of strings, if you use variable types you can save a lot of storage. As already mentioned, the less storage used, the less there is for a query to read, and the faster the query can perform. So variable length types are usually preferable in such cases when read performance is a priority.

Answer 381

Variable types use the storage for what you enter, plus a couple of bytes for offset information (or 4 bits with row compression). So for widely varying sizes of strings, if you use variable types you can save a lot of storage. As already mentioned, the less storage used, the less there is for a query to read, and the faster the query can perform. So variable length types are usually preferable in such cases when read performance is a priority.

Answer 382

When using types that can have a length associated with them, such as CHAR and VARCHAR, T-SQL supports omitting the length and then uses a default length. However, in different contexts, the defaults can be different. It is considered a best practice to always be explicit about the length, as in CHAR(1) or VARCHAR(30).

Answer 383

At the end of the day, to make smart choices, it’s important to learn the theory, learn about others’ experiences, but eventually make sure that you run benchmarks in the target system.

Answer 384

The identity column property. The sequence object. The nonsequential GUIDS, sequential GUIDS, custom solutions.

Answer 385

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

Answer 386

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

Answer 387

Nonsequential GUIDs You can generate nonsequential global unique identifiers to be stored in an attribute of a UNIQUEIDENTIFIER type, which uses 16 bytes of storage. You can use the T-SQL function NEWID to generate a new GUID, possibly invoking it with a default expression attached to the column. You can also generate one from anywhere—for example, the client—by using an application programming interface (API) that generates a new GUID. The main advantage of GUIDs is that they are guaranteed to be unique across space and time. Their disadvantages are that they use a lot of storage and are quite awkward to work with.

Answer 388

Sequential GUIDs You can generate sequential GUIDs within the machine by using the T-SQL function NEWSEQUENTIALID. This function is only allowed in a default constraint that is associated with a column.

Answer 389

Custom solutions If you do not want to use the built-in tools that SQL Server provides to generate keys, you need to develop your own custom solution. The data type for the key then depends on your solution. An example would be storing the last used value in a table, and every time you need a new value, incrementing the existing value in the table with an UPDATE statement and using the new value when you insert a row into the target table.

Answer 390

One thing to consider regarding your choice of a key generator and the data type involved is the size of the data type. The bigger the type, the more storage is required, and hence the slower the reads are. A solution using an INT data type requires 4 bytes per value, BIGINT requires 8 bytes, UNIQUEIDENTIFIER requires 16 bytes, and so on. The storage requirements for your key can have a cascading effect if your clustered index is defined on the same key columns (the default for a primary key constraint). The clustered index key columns are used by all nonclustered indexes internally as the means to locate rows in the table. So if you define a clustered index on a column x, and nonclustered indexes—one on column a, one on b, and one on c—your nonclustered indexes are internally created on columns (a, x), (b, x), and (c, x), respectively. In other words, the effect is multiplied.

Answer 391

Starting with sequential keys, all rows go into the right end of the index. When a page is full, SQL Server allocates a new page and fills it. This results in less fragmentation in the index, which is beneficial for read performance. Also, insertions can be faster when a single session is loading the data, and the data resides on a single drive or a small number of drives. However, with high-end storage subsystems, the situation can be different. When loading data from multiple sessions, such as in typical OLTP workloads like order entry systems, you end up with a performance problem known as the rightmost page latch contention. Latches are objects used to synchronize access to database pages. Consider nonsequential keys, such as random ones generated with NEWID or with a custom solution. When trying to force a row into an already full page, SQL Server performs a classic page split—it allocates a new page and moves half the rows from the original page to the new one. A page split has a cost, plus it results in index fragmentation. Index fragmentation can have a negative impact on the performance of reads. However, when using a highend storage subsystem and loading data from multiple sessions, the random order can result in much better performance than sequential despite the splits. That’s because there’s no hot spot at the right end of the index, and you use the storage subsystem’s available throughput better. Page splits and index fragmentation can be mitigated by periodic index rebuilds as part of the usual maintenance activities. If for aforementioned reasons you decide to rely on keys generated in random order, you still need to decide between GUIDs and a custom random key generator solution. As already mentioned, GUIDs are stored in a UNIQUEIDENTIFIER type that is 16 bytes in size;

Answer 392

Curiously, T-SQL handles conversions from NUMERIC to INT differently than between NUMERIC with a higher scale to a lower one. With the former, T-SQL truncates the value, with the latter, it rounds it. The following example demonstrates this:

Answer 393

The previous section in this skill discussed data type conversions, including implicit conversions. There’s a curious difference between ISNULL and COALESCE in terms of how the data type of the returned value is determined and how implicit conversion is handled. With ISNULL, the data type of the result is determined like so: 1. If the first input has a data type (as opposed to being an untyped NULL literal), the result type is the type of the first input. 2. If the first input is an untyped NULL literal, and the second input has a data type, the result type is the type of the second input. 3. If both inputs are untyped NULL literals, the result data type is INT. With Coalese: 1. If at least one of the inputs has a type, the result type is the type with the highest precedence among the inputs. 2. If all inputs are untyped NULL literals, you get an error.

Answer 394

Another difference between the two functions has to do with the nullability of the result column when using them in a SELECT INTO statement. With ISNULL, if any of the input expressions is nonnullable, the result column is defined as NOT NULL. If both inputs are nullable, the result is a column defined as allowing NULLs. With COALESCE, only if all inputs are nonnullable, the result column is defined as NOT NULL, otherwise, it is defined as allowing NULLs.

Answer 395

``` Another important difference between the two functions has to do with performance when using subqueries. That is, when comparing an expression such as ISNULL((), 0) with COALESCE((), 0). The ISNULL function evaluates the subquery only once. If its result is not NULL, it returns its result. If it is NULL, it evaluates the second input and returns its result. With COALESCE, according to the SQL standard, the expression is translated to: CASE WHEN () IS NOT NULL THEN () ELSE 0 END ``` If the result of the execution of the subquery in the WHEN clause isn’t NULL, SQL Server executes it a second time in the THEN clause. In other words, in such a case it executes it twice. Only if the result of the execution in the WHEN clause is NULL, SQL Server doesn’t execute the subquery again, rather returns the ELSE expression. So when using subqueries, the ISNULL function has a performance advantage.

Answer 396

1. If first input has a type, that’s the type of the result. 2. Otherwise, if second input has a type, that’s the type of the result. 3. If both inputs are untyped NULL literals, the result type is INT.

Answer 397

1. If at least one input has a type, the result type is the type with the highest precedence. 2. If all inputs are untyped NULL literals, you get an error.

Answer 398

If any input is nonnullable, result is defined as NOT NULL, otherwise as NULL.

Answer 399

If all inputs are nonnullable, result is defined as NOT NULL, otherwise as NULL.

Answer 400

When comparing two columns using an equality operator (=), if either side is NULL, the outcome of the comparison is the logical value unknown. As part of a join predicate, the case is considered a nonmatch, and as part of a filter, the result row is discarded. (Page 318).

Answer 401

SELECT A.A_val, B.B_val FROM dbo.TableA AS A INNER JOIN dbo.TableB AS B ON EXISTS( SELECT A.key1, A.key2 INTERSECT SELECT B.key1, B.key2 );

Answer 402

Data type precedence, the data type with lower precedence will be converted with the higher precedence. Example, currency and quantity, if multiplied will take currency data type due to precedence.

Answer 403

Varbinary and nvarchar

Answer 404

It prevents any rows being updated to the underlying tables that are not visible through it

Answer 405

CONCAT() returns NULL if any argument is NULL

Set 1 Flashcards

(445 cards)