Set 1 Flashcards
T-SQL is a dialect of what? SQL is a standard of which organizations? What are the major revisions of SQL?
standard SQL,ISO and ANSI, [ 86, 89, 92, 99, 03, 06, 08, 11, 16 ]
Is writing in the standard way a best practice? e.g. CAST vs CONVERT? The standard requires what as a terminator?
Yes, semicolon
What is the source for the term “relation”?
The heading of a relation is?
What is a relation?
What are the three characteristics of a SET?
What is a body?
What is meant by whole?
- Comes from the mathematical concept relation.
- A set of Attributes
- A relation has a heading and a body. The heading is a set of attributes and the body is a set of tuples. Relation is also the mathematical term for a table.
- Whole, same type, and distinct, so no order, no duplicates, same type.
N.O.T. - A body is a set of tuples.
- You don’t interact with individual elements but with the whole set.
What is a predicate?
Another definition of a predicate?
A predicate is an expression that when attributed to some object makes it true or false, for example, gamesplayed greater than 10, where city = Atlanta, or where person = James.
A predicate is a parameterized proposition.
What is a relational schema?
What is a relational database?
What is a relational schema consist of?
What is a relational instance?
- Made up of attributes.
- A relational database is a set of relations
- Relational name, name and type of each attribute, eg. Employee (EmpID:int, EmpName:varchar)
- Is a table made of attributes and tuples, or columns and rows.
Degree refers to what?
What does cardinality refer to?
To the number of attributes or columns in a relation.
Cardinaltiy refers to the number of rows.
What are values called in a relation or table?
What are the formal vs informal terms used?
What are the properties of a relation?
Domains
Informal: Table, Columns, Rows, Table definition, values Formal: relation, attributes, tuples, schema definition, domains.
Relation has a distinct name. Each cell contains one value. Each attributes is distinct name. Each tuple is distinct. The order of tuples and attributes has no significance.
How to avoid iterative solutions?
Thinking in iterative terms creates iterative solutions.
While predicates theoretically evaluate to only true or false, what is the exception in SQL?
NULL, which results in unknown., Codd theorized two types, applicable but missing, and missing but inapplicable. SQL only has NULL which still extends beyond just true/false.
Why should you not use fields and records as terminology?
Fields and records are physical, Fields are what you have in user interfaces in client applications, and records are what you have in files and cursors. Tables are logical and they have logical rows and columns.
Why is “NULL value” self-contradictory?
Because NULL has no value.
Why is * considered bad practice?
Possibly prevents SQL from using covering indexes. More traffic across the network. Table definition changes could pull more than you need.
Is using AS for aliasing standard? Why
Yes, because of this SELECT LastName FirstName from Person. Only one column is returned.
Which one is standard “” delimiter or [ ] delimiters?
”” is standard [] is proprietary.
Should you delimit?
Maybe not, if identifier is regular then doing so may clutter your code.
What is the precedence rules for logical evaluation of operators?
NOT>AND>OR
What is a statement in SQL?
A statement performs some kind of action or controls the flow of the code.
What is an expression in SQL?
An expression is a combination of one or more values, operators and SQL functions that evaluate to a value.
Is the Case keyword expression or statement?
Expression
Does where prevent the CAST failure? WHERE propertytype = ‘INT’ AND CAST(propertyval AS INT) > 10
No, the all-at-once concept does not require the evalution of the expressions from left to right. There is a short circuit concept but due other cost related reasons it may choose to process the expressions in a different order.
What happens to the predicate evaluates a column with NULL values?
The predicate evaluates to unknown for rows that have a NULL in the attribute, and that the WHERE clause discards such rows. So, looking for Employee <> N’Dave’ would also discard NULLs which are evaluated to unknown.
What do NULLS evaluate to in predicates
Nulls evaluate to unknown.
What is considered equal to a NULL?
Nothing is considered equal to a NULL.
How do you include NULL cases in your result?
Use IS NULL.
What is a literal?
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.
Do literals have a type?
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.
Do implicit conversion hurt performance?
Implicit conversion can sometimes hurt performance.
Why use N in front of unicode string literals?
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’.
Is LIKE a predicate
Yes.
‘John%’ would filter for what?
Any items that begins with John
‘_J’ would filter for what?
Any string where the second character is J.
‘[SY]%’ would filter for what?
Any string where the first letter is S or Y.
‘[0-9]%’ would filter for what?
Any string where the first single character from a range of 0-9.
’[^0-9]%’ would filter for what?
Any string where the first character is not in the range 0-9.
% (percent sign) means what?
Any string including an empty one.
_ (underscore) neans what?
A single character
[^] means what?
A single character that is not in the list or range
How do you look for a character that is considered a wildcard?
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 ‘#’
What is the language-neutral version of the date?
For example, the form ‘20160212’ is always interpreted as ymd.
What is the format considered language neutral for? ‘2019-03-10’?
‘2019-03-10’ is considered language-neutral only for the data types DATE, DATETIME2, and DATETIMEOFFSET.
How to get the right format of dates?
Use CONVERT and change it the right format.
What is the date problem with this query: SELECT Employee FROM Employee WHERE hiredate BETWEEN ‘20150503’ AND ‘20160430 23:59:59.999’;
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.
What is the best way to express a datetime range?
With closed-open interval such as: SELECT Employee FROM Employee WHERE hiredate >= ‘20150503’ AND hiredate
Why is it important to understand the sorting of date?
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.
Will a query have order?
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.
What can affect the order in which rows are returned?
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.
What order in a query is assumed by default?
ASC
Will an ORDER BY always create deterministic ordering?
No, ordering by a non-unique column would not guarantee deterministic ordering. Such as, orderging by Country Name in worldwide dataset of people.
Is this best practice? ORDER BY 5, 1, 2
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.
Can you use an ORDER BY with a column non in the SELECT list?
Yes.
How are NULLs sordted?
Together and first in T-SQL.
Is a query with an ORDER BY a relation?
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.
What other filter based on the WHERE predicate, what other filters are there?
TOP and OFFSET-FETCH use numbers to filter.
When using TOP how do you guarantee repeatable and deterministic results?
Use SELECT TOP (#) WITH TIES or add an ORDER BY to break ties.
What is the syntax of the OFFSET and FETCH clause
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.
How do you make the OFFSET and FETCH read more intuitively?
You can use NEXT or FIRST after FETCH.
How would you use (SELECT NULL)?
If you want an arbitrary order with OFFSET FETCH, you can use (SELECT NULL) in the ORDER BY clause.
How could you use OFFSET FETCH in a paging solutin?
OFFSET-FETCH allows expressions as inputs.
What is the syntax of OFFSET-FETCH
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.
As far as logical query processing, where are do the OFFSET-FETCH filters get processed?
Think of them as an extension of the GROUP BY clause.
When do TOP, DISTINCT, OFFSET-FETCH, and ROW_NUMBER get applied?
After the GROUP BY.
What are the five SET operators?
UNION, UNION ALL, INTERSECT, EXCEPT.
What are the 5 guidelines for SET operators?
- Same number of columns.
- Implicitly convertible column types between the corresponding columns of each set.
- 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.
- These are SET operators and not CURSOR operators, so no GROUP BY on sets only on the final result
- Column Names are determined by the first query
Name three filtering clauses?
WHERE, ON, HAVING
The term SET operator is not a precise term to describe UNION, INERSECT, and EXCEPT, why?
Mathematical Set operators allow different types between two merged sets, whereas relational table do not.
What does the UNION opeator do?
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.
What does UNION ALL do?
It keeps duplicates as opposed to the UNION operator.
What does the INTERSECT operator do?
It returns only distinct rows that are common to both set.
What does the EXCEPT operator do?
The EXCEPT operator performs set difference. It returns distinct rows that appear in the result of the first query but not the second.
What is the precedence with the set operators?
INTERSECT precedes UNION and EXCEPT and UNION and EXCEPT are evaluated from LEFT to RIGHT based on their position in the expression.
What is a CROSS JOIN?
Performs a Cartesian product of two input tables.
Does SQL Server have to follow logical query processing literally?
No, as long as it can return the correct result.
What is optimization?
It is returning the results as fast as possible, hence SQL is not bound to follow logical query processing.
What is predicate pushdown? What is an example?
It is applying the predicate before a join, such as a CROSS JOIN followed by a predicate.
What situation is table aliasing mandatory?
In self joins.
What is the older syntax that can be used instead of the keyword CROSS JOIN? Which one is recommended?
FROM table1, table2
instead of
FROM table1 CROSS JOIN table2
Use the newer syntax.
What is meant by the term equiijoin?
Equiijoin is using a predicate with an equality operator such as table1.keycol = table2.keycol
How does the join predicate ON handle NULLS?
They evaluate to NULL so it discards them.
When you create a primary key or a unique constraint what does SQL do?
SQL Server creates a unique index on the constraint columns to enforce the constraint’s uniqueness property.
Is prefixing columns names with aliases in the select clause always mandatory?
No, but it is considered best practice for clarity sake.
What is the difference between the ON and the WHERE clauses, dand does it matter if yo uspedify your preodicate in one or hte other?
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.
What does the JOIN indicate as a type of join?
The standard made JOIN for inner join since it is the most common join.
Do the ON and WHERE clause work the same for all joins?
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.
What is a composite join?
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.
Another definition of a composite join?
A composite join and the ON clause typically consists of a conjunction of predicates.
What is one difference between a unique key constraint and a primary key constraint?
A unique key constraint permits NULLs.
Why is NULL value stupid?
Because NULL is unknown therefore cannot be a value..
What are few ways to handle joins on NULLs
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.
What is an example of code that preserves the logical meaning and is order preserving by the optimizer?
EL.region = CL.region OR (EL.region IS NULL AND CL.region IS NULL))
What is an example of a merge join hint?
?
What is a solution that uses SET operators and is order preserving and preserves the logical meaning?
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);
Which direction are multi-join queries evaluated?
LEFT to RIGHT however inner and cross-joins the order cannot affect the meaning.
How to solve the nullification of a LEFT join followed by an INNER join?
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
What is the one pitfall of following an OUTER JOIN with a INNER JOIN?
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.
What are the two fundamental functions that T-SQL supports for conversion purposes?
CAST and CONVERT.
What is the PARSE function?
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.
What happens if the CAST, CONVERT, or PARSE functions fail to convert?
The entire query fails and stop processing. Use TRY_CAST, TRY_CONVERT, and TRY_PARSE instead. This will return NULL instead of failing.
What is the FORMAT function?
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.
What type does DATEDIFF bring back?
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.
What are the three functions related to time values with an offset?
SWITCHOFFSET, TODATETIMEOFFSET, and AT TIME ZONE.
What does SWITCHOFFSET(SYSDATETIMEOFFSET(), ‘-8:00’)?
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.
The TODATETIMEOFFSET function is used for a different purpose, what is that?
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.
Give an example of TODATETIMEOFFSET and SWITCHOFFSET?
SELECT SWITCHOFFSET(‘20170212 14:00:00.0000000 -05:00’, ‘-08:00’) AS [SWITCHOFFSET], TODATETIMEOFFSET(‘20170212 14:00:00.0000000’, ‘-08:00’) AS [TODATETIMEOFFSET];
When to use AT TIME ZONE?
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.
What data type does SWITCHOFFSET and TODATETIMEOFFSET return?
DATETIMEOFFSET
Give an example of using AT TIME ZONE
SYSDATETIMEOFFSET() AT TIME ZONE ‘Pacific Standard Time’
For instance, suppose that you have a column called lastmodified that is typed as DATETIME2 and holds the value in UTC terms. You want to present it in the time zone Pacific Standard Time. How do you do that?
You use the following expression: lastmodified AT TIME ZONE ‘UTC’ AT TIME ZONE ‘Pacific Standard Time.’
When any input to the + function is NULL what happens?
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.
Why should you use CONCAT?
Another option is to use the CONCAT function which, unlike the + operator, substitutes a NULL input with an empty string.
What is the difference between PATINDEX and CHARINDEX?
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.
What is an example of PATINDEX?
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.
Does LEN remove trailing spaces?
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.
What does DATALENGTH do?
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.
What is the STUFF function and what is it’s syntax?
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’.
What is the FORMAT function? How does it work? Give an example.
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’.
How would you split a string based on a delimiter? What function would you use, can you give an example?
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
Why is the expression CASE “statement” a misnomer?
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.
What are few functions that are considered abbreviates of the CASE expression?
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.
What is the @@ROWCOUNT?
The @@ROWCOUNT function is a very popular function that returns the number of rows affected by the last statement that you executed.
What are the COMPRESS and DECOMPRESS functions?
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.
Give code example of COMPRESS and DECOMPRESS?
INSERT INTO dbo.MyNotes(notes) VALUES(COMPRESS(@notes));
SELECT keycol CAST(DECOMPRESS(notes) AS NVARCHAR(MAX)) AS notesFROM dbo.MyNotes;
What does NILADIC mean?
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.
What is context_info?
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;
What is the preferred alternative to context_info?
-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.
What is the code for SESSION_CONTEXT?
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
How to create a unique key for a SESSION_CONTEXT?
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
What is the type returned by NEWID and NEWSEQUENTIAL ID?
UNIQUEIDENTIFIER typed value.
If you need a numeric key generator what do you do?
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.
What does SCOPE_IDENTITY do?
Gets the last identity value that was generated in the same session.
What does the CREATE SEQUENCE do?
It is an independent object in the database, every time you need a value you invoke the function NEXT VALUE FOR .
How do you get the last identity value generated in your session, irrespective of scope?
Query the system function @@IDENTITY
What are the classic arithmetic operators rules?
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:
With INTEGER division such as 7/2 how do you get numeric division?
Either cast or multiply the first operand by a numeric constant and force by implicit conversion.
Do aggregate functions ignore NULL inputs when applied to an expression?
Yes.
The COUNT(*) aggregate just counts rows, and returns the result as an INT value. What if it is a BIG value?
Use COUNT_BIG.
How to count just distinct values?
If you want to apply an aggregate function to distinct values, add the DISTINCT clause, as in COUNT(DISTINCT custid).
Give a query to provide the median.
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;
What is meant by SARGABLE fitler?
A search argument is a filter predicate that enables the optimizer to rely on index order.
What are the criteria that makes a filter sargable?
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.
Give an example of a SARGABLE LIKE predicate?
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
Equality based comparisons and NULLS
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.
Example of using intersect to select on a NULLABLE column.
DECLARE @dt AS DATE = NULL;
SELECT orderid, shippeddate
FROM Sales.Orders
WHERE EXISTS (SELECT shippeddate INTERSECT SELECT @dt);
Define function determinism.
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.
How to get truly random values?
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());
Is specifying the target column considered best practice in the INSERT VALUES statement?
Yes. INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES(2, 19, ‘20170620’, N’USA’, 30.00);
If you do want to provide your own value instead of letting the identity property do it for you, you need to?
first turn on a session option called IDENTITY_INSERT, as follows:SET IDENTITY_INSERT ON. When you’re done remember to turn it off.
How can you run into blocking situations with SELECT INTO?
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
Give an example of a compound assigment operator with UPDATE statement?
UPDATE Sales.MyOrderDetails SET discount += 0.05 WHERE orderid = 10251;
If you’re using a cursor how can you modify the table row that the cursor is currently positioned at?
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
What is a good technique to use to create an UPDATE statement?
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’;
The T-SQL update syntax on join can be nondeterministic, true?
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.
How could update a customer’s postal code with the shipping postal code from the custom’er first order, give an example.
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;
Suppose that you need to modify the row, increasing the discount by five percent, and col-lect the new discount into a variable called @newdiscount. You can achieve this using a single UPDATE statement, as follows.
DECLARE @newdiscount AS NUMERIC(4, 3) = NULL;UPDATE Sales.MyOrderDetails
SET @newdiscount = discount += 0.05
WHERE orderid = 10250 AND productid = 51; SELECT @newdiscount;
Explain the DELETE statement.
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.
How to prevent locking with the DELETE statement?
WHILE 1 = 1 BEGIN DELETE TOP (1000) FROM Sales.MyOrderDetails WHERE productid = 12; IF @@rowcount < 1000 BREAK; END
Syntax for deleting with cursor.
DELETE FROM dbo.MyTable WHERE CURRENT OF MyCursor;
Why is TRUNCATE table faster way of deleting?
TRUNCATE table statement uses an optimized logging mode and therefore is significantly faster.
What are the differences between DELETE and TRUNCATE?
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.
What is the syntax of a DELETE statement with a JOIN?
DELETE FROM O FROM Sales.MyOrders AS O INNER JOIN Sales.MyCustomers AS C ON O.custid = C.custid WHERE C.country = N'USA';
Syntax of MERGE statement.
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
Can you define a derived table with the VALUES clausE?
Yes.
How to create a sequence?
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.
How to handle merge conflicts?
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
An example of using WITH SERIALIZABLE
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
An alternative to update with MERGE
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
About the using clause in a MERGE statement.
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.
How to TRUNCATE a table and RESTART a sequence.
RUNCATE TABLE Sales.MyOrders; ALTER SEQUENCE Sales.SeqOrderIDs RESTART WITH 1;
What is the $action function do?
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.
An example of the $action function.
WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action AS the_action, COALESCE(inserted.orderid, deleted.orderid) AS orderid;
Can you capture SRC in an OUTPUT clause?
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;
Can you wrap a MERGE with an INSERT?
Yes, you can to catpure OUTPUT data, but you can only use the WHERE clause.
How to an NOT NULL column to a table?
WITH VALUES clause as follows:ALTER TABLE Sales.MyOrders ADD requireddate DATE NOT NULL CONSTRAINT DFT_MyOrders_requireddate DEFAULT (‘19000101’) WITH VALUES;
Three reasons dropping a column would fail?
■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.
When to use WITH CHECK and WITH NOCHECK?
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.
What is ONLINE ON used for?
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.
What are the two types of subqueries?
Subqueries can be self-contained or correlated.
What are self contained sub queries?
Queries that have no dependency on the outer query.
What happens if a scalar subquery returns more than one value? What about a NULL?
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.
