Exam Ref 70-761 Querying Data with Transact-SQL

OFFSET-FETCH

ORDER BY is mandatory to use. OFFSET clause is mandatory with FETCH. You can never use, ORDER BY ... FETCH. TOP cannot be combined with OFFSET and FETCH in the same query expression. Example 1: Skip first 10 rows from the sorted result set and return the remaining rows. SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS; Example 2- Skip first 10 rows from the sorted result set and return next 5 rows. SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

What is the difference between an outer and cross apply?

OUTER APPLY acts like left join, returning all rows from the left. CROSS APPLY returns only rows where there are matches. I prefer OUTER APPLY as I can see which values didn't return a result.

What type of subquery is cross apply operator?

Correlated subquery

Your company sells car engines. You have named dbo.Parts. A portion of the table definition is shown in the exhibit. You need to prepare a report that will present a hierarchy of the parts that are used to build each engine. What should you do?

Create a query that will use a common table expression (CTE)

Your company sells car engines. You have named dbo.Parts. A portion of the table definition is shown in the exhibit. You need to prepare a report that will present information abou the total number of parts that are used in an engine and the sub levels of the engine.

Create a query that will use a common table expression (CTE)

DATEADD(datepart, number, date) Syntax

DATEADD lets you add values to a given date and returns the result as a datetime data type. Entering SELECT DATEADD(DAY, 30, GETDATE()) adds 30 days to the date from the example above: 2009-08-06 12:01:38.950. year, yyyy, yy = Year quarter, qq, q = Quarter month, mm, m = month dayofyear = Day of the year day, dy, y = Day week, ww, wk = Week weekday, dw, w = Weekday hour, hh = hour minute, mi, n = Minute second, ss, s = Second millisecond, ms = Millisecond

DATENAME(datepart, date)

DATENAME returns the name of a given part of the date: SELECT DATENAME(MONTH, GETDATE()) It can return almost all parts of the date including the name of the quarter, the weekday, or as here, the month: July.

What is DDL?

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database. CREATE - to create database and its objects like (table, index, views, store procedure, function and triggers) ALTER - alters the structure of the existing database DROP - delete objects from the database TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed COMMENT - add comments to the data dictionary RENAME - rename an object

DELETE FROM syntax

DELETE FROM db.table WHERE col1 >29;

What are the differences between DELETE and TRUNCATE TABLE?

DELETE support a filter, is fully logged, and does not reset the current identity value. Unlike DELETE, TRUNCATE TABLE is disallowed if there's an indexed view based on the table, or a foreign key pointing to the table even if there are no related rows in the referencing table.

What is DML?

DML is short name of Data Manipulation Language which deals with data manipulation, and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve, delete and update data in database.

DROP FUNCTION syntax

DROP FUNCTION IF EXISTS <functionName>; GO

DROP VIEW syntax

DROP VIEW IF EXISTS name;

datetimeoffset definition datetimeoffset [ (fractional seconds precision) ]

Defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock.

Call procedure syntax

EXEC What_DB_is_that @ID=2;

WITH CHECK OPTION

Ensures any data inserted into a view will contain enough info to be included in the field or not.

COALESCE()

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that is not null. COALESCE ( expression [ ,...n ] ) The COALESCE expression is a syntactic shortcut for the CASE expression.

CUBE syntax

FROM * FROM tblname GROUP BY CUBE (col1, col2);

GROUPING SETS syntax

FROM * FROM tblname GROUP BY GROUPING SETS ( (col1, col2), (col1 ), (col2 ), ( ) );

What is the logical query processing order?

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

Differences Between RAISERROR and THROW

RAISERROR statement If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages. The msg_str parameter can contain printf formatting styles. The severity parameter specifies the severity of the exception. THROW statement The error_number parameter does not have to be defined in sys.messages. The message parameter does not accept printf style formatting. There is no severity parameter. The exception severity is always set to 16.

What is the default isolation level in SQL Server?

READ COMMITTED This makes sense, read what has been submitted. Read what as been commited even if it changes your numbers.

How should table types be passes as inputs in stored procedures?

READONLY

ISNULL(Transact-SQL)

Replaces NULL with the specified replacement value. Do not use ISNULL to find NULL values. Use IS NULL instead. ISNULL ( check_expression , replacement_value ) check_expression Is the expression to be checked for NULL. check_expression can be of any type. replacement_value Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.

nondeterministic function

Returns a different result set each time it is ran. For example: GETDATE()

INTERSECT definition

Returns any distinct values that are returned by both the query on the left and right sides of the operand. The number and the order of the columns must be the same in all queries. The data types must be compatible.

OUTPUT Clause

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

what is the difference between GROUPING SETS, CUBE, and ROLLUP?

GROUPING SETS - explicitly define the grouped sets CUBE - automatically group all possible sets, includes NULLs ROLLUP - automatically group all possible natural hierarchy sets formed by the input elements, includes NULLs ROLLUP (YEAR, MONTH, DAY) With a ROLLUP, it will have the following outputs: YEAR, MONTH, DAY YEAR, MONTH YEAR () With CUBE, it will have the following: YEAR, MONTH, DAY YEAR, MONTH YEAR, DAY YEAR MONTH, DAY MONTH DAY ()

what is SCOPE_IDENTITY()

Returns the last identity value inserted into an identity column in the same scope.

INSERT statement syntax

INSERT INTO tblname (col1,col2) VALUES (val1, val2)

What statements are used to modify data?

INSERT, SELECT INTO, UPDATE, DELETE, TRUNCATE TABLE, MERGE

What is SET NOCOUNT ON do?

Improves stored procedure (SP) performance by not printing the number of rows affected. Improvement is small. Shouldn't use unless you know what you're doing...

What is the difference between the ON and WHERE clauses?

In an outer join the ON clause services a matching purpose. It determines which rows from the preserved side get matched with each rows from the non-preserved side. It cannot determine which rows from the preserved side of the join are returned - it's predetermined that all of them are returned. The WHERE clause serves a simpler filtering meaning. It determines which rows from the result of the FROM clause to keep and which to discard. In an inner join, both ON and WHERE serve the same simple filtering meaning.

What is required to insert values into a table when specifying the identity column?

Include SET IDENTITY_INSET tbl1 ON; insert statement with column names SET IDENTITY_INSERT tbl1 OFF;

INSERT Examples

Inserting a single row of data INSERT INTO Production.UnitMeasure VALUES (N'FT', N'Feet', '20080414'); Inserting multiple rows of data INSERT INTO Production.UnitMeasure VALUES (N'FT2', N'Square Feet ', '20080923'), (N'Y', N'Yards', '20080923'), (N'Y3', N'Cubic Yards', '20080923'); Inserting data that is not in the same order as the table columns INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate) VALUES (N'Square Yards', N'Y2', GETDATE());

EXISTS

Is a predicate that accepts a subquery as input and returns true when the subquery returns at least one row and false otherwise. Doesn't return the result set of the query; rather, it returns only true or false, depending on whether the subquery returns any rows.

Generally, when solving tasks with T-SQL, is it more efficient to use joins or subqueries?

It depends. No one tool is better than another. Joins tend to do better when you need to apply multiple calculations, like aggregates, based on the same set of rows. With subqueries you access the data separately for each aggregate and with joins you access the data once for all aggregates. However, when looking for nonmatches, joins currently aren't optimized with the Anti Semi Join optimization, whereas subqueries are. With this optimization, SQL Server short circuits the work as soon as a match is found, resulting typically in less efoort.

What is THROW without parameters?

It re-re-raises the same error that caused control to be given to the CATCH block

LEAD SYNTAX

LEAD (column ,offset row=1, default=NULL ) OVER ( [ partition_by_clause ] order_by_clause ) Default is the value to return when scalar_expression at offset is NULL. If a default value is not specified, NULL is returned.

What could prevent SQL Server from treating a query filter optimally, meaning, from using an index efficiently to support the filter? What other query elements could also be affected in a similar manner and what can you do to get optimal treatment?

Manipulation of the filtered column in most cases prevents the filter's sargability. This means that the optimizer cannot rely on index order, for instance, to perform a seek within the index. In a similar way, manipulation of a column can prevent the optimizer from relying on index order for purposes of joining, grouping, and ordering.

*= (Multiplication Assignment)

Multiplies two numbers and sets a value to the result of the operation. For example, if a variable @x equals 35, then @x *= 2 takes the original value of @x, multiplies by 2 and sets @x to that new value (70).

Your database application needs to determine the age of a piece of equipment. When returned by queries, the age must be determined to the nearest day. You need to enable the calculation of the age of equipment to the nearest day. Your solution must minimize storage. Solution: You store the age as an integer value. Does this meet the goal?

NO! You should store PurchaseDate as a date value and then calculate age when required.

set default syntax

NULL DEFAULT (N'USA') WITH VALUES;

Do views support parameters?

No

Stored procedures are more secure but execute less efficiently than SELECT statements

No

Stored procedures encrypt data in underlying tables using the WITH ENCRYPTION option?

No

THROW must only be used within a CATCH block

No

THROW must only be used within a CATCH block?

No

THROW transfers processing to the TRY block

No

Are temp tables table expressions?

No, temp tables are not table expressions.

Are you required to specify the identity number when adding values to a table?

No, you don't specify the identity column. SQL Server handles it for you.

INNER JOIN syntax

SELECT * FROM <tbl1> INNER JOIN <tbl2> ON (tbl1.cola = tbl2.cola)

ROLLUP syntax

SELECT * FROM tblname GROUP BY ROLLUP (col1, col2, col3);

EXCEPT syntax

SELECT * FROM <tbl1> EXCEPT SELECT * FROM <tbl1>

INTERSECT syntax

SELECT * FROM <tbl1> INTERSECT SELECT * FROM <tbl1>

Select Statement Structure

SELECT - specifies the column name or number to return FROM - specifies the table name WHERE - specifies which rows to retrieve GROUP BY - groups rows sharing a property so that an aggregate function can be applied to each group HAVING - selects among the groups defined by the GROUP BY clause. ORDER BY - specifies an order in which to return the rows;

UNPIVOT syntax

SELECT <column list>, <names column>,<values> FROM <source table> UNPIVOT (<valuesCol> FOR <names col> IN (col1, col2) ) Note: UNPIVOT filters out rows with NULLs. If you want to keep them as zero, then you need a table expression. Add ISNULL in the SELECT

FOR XML syntax

SELECT Cust.CustomerID, OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status FROM Sales.Customer Cust INNER JOIN Sales.SalesOrderHeader OrderHeader ON Cust.CustomerID = OrderHeader.CustomerID FOR XML AUTO In a FOR XML clause, you specify one of these modes: RAW - generates a single <row> element per row in the rowset AUTO - generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified. EXPLICIT-llows more control over the shape of the XML. You can mix attributes and elements at will in deciding the shape of the XML. PATH - mode together with the nested FOR XML query capability provides the flexibility of the EXPLICIT mode in a simpler manner.

Things to remember about XML

SELECT Cust.CustomerID, Cust.CustomerType FROM Sales.Customer ORDER BY Cust.CustomerID FOR XML AUTO, ELEMENTS, ROOT('name'); The order by is required since XML is just a text document, ordering is important for viewing. XML is case sensitive unicode text.

Syntax to determine if a function is deterministic

SELECT OBJECT_PROPERTY(OBJECT_ID('dbo.dbname'), 'IsDeterministic')

Which clauses are window functions allowed?

SELECT and ORDER BY

Syntax for querying temporal tables for all changes

SELECT col1, col2 FROM tbl1 FOR SYSTEM_TIME ALL WHERE col1=9

Syntax for querying temporal tables with date range

SELECT col1, col2 FROM tbl1 FOR SYSTEM_TIME FROM '20161101 14:0000.00 TO '20161101 14:11:38.113' WHERE col1=9

OVER clause syntax

SELECT col1, col2, SUM(col2) OVER(PARTITION BY col1) AS a1 FROM tbl1

PIVOT syntax

SELECT grouping, spreading, aggregation FROM tbl1 PIVOT( <agg function>(<agg colum>) FOR <speading column> IN (<distinct spreading values>) ) AS P; The grouping element is identified by elimination. That is why it is recommended to prepare a table expression for the pivot operator returning only the three elements that should be involved in the pivoting task. Note: Fields with no values for aggregating will show as NULL. Need table expression to change.

Things to remember about JSON

SELECT name, surname FROM emp FOR JSON AUTO SELECT name, surname FROM emp FOR JSON PATH, ROOT('info')

Example of self contained sub query

SELECT productid, productname, unitprice FROM Production.Products WHERE unitprice = (SELECT MIN(unitprice) FROM Production.Products);

explicit insert query of an IDENTITY property syntax

SET IDENTITY_INSERT dbo.name ON INSERT sometableWithIdentity (IdentityColumn, col2, col3, ...) VALUES (AnIdentityValue, col2value, col3value, ...) SET IDENTITY_INSERT sometableWithIdentity OFF

JSON

There is no native JSON data type in SQL Server. However, you can create JSON documents from queries with the FOR JSON clause, shred JSON documents to tabular format with the OPENJSON function, extract scalar values and JSON fragments with the JSON_VALUE and JSON_QUERY functions, modify JSON data with the JSON_MODIFY function, and test validity with the ISJSON function.

DAY(date), MONTH(date), YEAR(date)

These date functions are like DATEPART but a bit easier to work with: SELECT MONTH(0), DAY(0), YEAR(0) They each return an integer representing the supplied date value—in this case, 1,1,1900.

SET TRANSACTION ISOLATION LEVEL options

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE } REPEATABLE READ Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes. SNAPSHOT Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction. SERIALIZABLE Specifies the following: Statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Your stored procedures should always include this statement in the beginning:

SET XACT_ABORT, NOCOUNT ON (Prevents rows change prints)

"not equal to" operator

SQL Server has two "not equal to" operators: <> and != - (<> is best practice).

"not equal to" operator

SQL Server has two "not equal to" operators: <> and =! - (<> is best practice).

DATEDIFF(datepart, startdate, enddate)

This function returns a single integer data type that represents the difference between two dates. It can return values for years, months, days, hours, minutes, seconds, milliseconds, and more: SELECT DATEDIFF(DAY, '01/01/2009', GETDATE()) year, yyyy, yy = Year quarter, qq, q = Quarter month, mm, m = month dayofyear = Day of the year day, dy, y = Day week, ww, wk = Week weekday, dw, w = Weekday hour, hh = hour minute, mi, n = Minute second, ss, s = Second millisecond, ms = Millisecond

ISDATE(expression)

This function tests if the value supplied is a valid date: SELECT ISDATE ('07/44/09') In this case, it returns a value of 0 (false) indicating the date is invalid; if it returns a value of 1 (true), the date is valid.

Date functions

SYSDATETIME() 2007-04-30 13:10:02.0474381 SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00 SYSUTCDATETIME() 2007-04-30 20:10:02.0474381 GETDATE() 2007-04-30 13:10:02.047 GETUTCDATE() 2007-04-30 20:10:02.047

What are the limitations of the UNPIVOT operator in T-SQL?

The UNPIVOT operator removed rows with the NULLs in the value column. It doesn't make this step optional It requires all column that you're unpivoting to have exactly the same data type. It supports only one measure (values column). Like with PIVOT, in a static query you have to hard code the columns that you're unpivoting, or otherwise build and execute the query string with dynamic SQL.

THROW syntax

THROW 51000, 'The record does not exist.', 1; THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ] error_number Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647. message Is an string or variable that describes the exception. message is nvarchar(2048). state Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.

TOP

TOP clause is logically executed before the ORDER BY cannot be combined with OFFSET and FETCH in the same query expression -- Select the first 10 random employees. SELECT TOP(10) WITH TIES JobTitle, HireDate FROM HumanResources.Employee; The WITH TIES option means that instead of stopping immediately after the requested number of rows is returned, the query will also give you all ties with the last row based on the ordering elements (orderdate, in our case). This means that you might get more than the number of rows that you requested, but the selection of rows becomes deterministic.

What are the limitations of the PIVOT operator in T-SQL?

The _________________ operator is limited to only one aggregate. If you use the COUNT aggregate, you're limited to COUNT(*). In a static query, you have to hard code the spreading values. If you don't want to hard code those, you have to construct and execute the query string with dynamic SQL.

How to remove all records from two tables when one of them references a table with a foreign key?

TRUNCATE the table with the foreign key and DELETE the table without it.

TRY_PARSE syntax

TRY_PARSE ( string_value AS data_type [ USING culture ] ) Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.

From a performance perspective, is it better to use table expressions or temp tables?

Table expressions don't persist the result of the inner query physically anywhere, When you're querying a table expression, SQL Server inlines the inner query logic, and the physical processing interacts directly with the underlying table's data. Temp tables and table variables do persist the result set that you store them in. So usually you want to use temporary table when you have a result set that you store them in. So you only want to use a temporary table when you have a result set that is expensive to create, and you need to interact with the result only once, and the use of the temporary table is more about simplifying your solution or circumventing language limitations such as ones related to reuse of column aliases, you typically want to use table expressions in such cases.

Why can't you place a row number calculation in the WHERE clause if you want to filter a range of row numbers?

That is because as a window function, the ROW_NUMBER function is supposed to be applied to the underlying query result. In logical query processing terms, the underlying query result is established only when you get to the SELECT phase (step 5), after FROM, WHERE, GROUP BY, and HAVING. For this reason, you can only use window functions in the SELECT and ORDER BY clauses of a query. If you need to refer to them in clauses that are processed before the SELECT statement, like the WHERE clause, write a query where you invoke the window function in the SELECT and assign it with an alias, and then have the other refer to that alias in the WHERE clause.

What are examples of DML statements

(INSERT, DELETE, UPDATE)

What is the best way in T-SQL to compute running totals, and what are the performance considerations that you need to be aware of when using such computations?

The best way is with the window aggregate function with the frame ROWS UNBOUNDED PRECEDING. This is much more efficient that doing this with joins or subqueries. You can support the calculation with the POC index (partitioning, ordering, covering) to avoid the need for explicit sorting. You want to be careful not to use the RANGE option, which is much more expensive. You want to remember that if you specify a window order clause but don't specify a window order clause but don't specify the window frame unit ROWS or RANGE), you get RANGE by default. So, you need to make sure the explicit use ROWS. If you use columnstore technology, the running total calculation can be optimized with highly efficient batch mode WINDOW Aggregate operator.

What is NOCOUNT?

The effect of NOCOUNT is that it suppresses messages like (1 row(s) affected) that you can see in the Message tab in SQL Server Management Studio. While these row counts can be useful when you work interactively in SSMS, they can degrade performance in an application because of the increased network traffic. The row counts can also confuse poorly written clients that think they are real result sets.

Temporal tables

Not to be confused with temp tables. Allow you to keep track of the history of changes to your data for long periods of time. You enable system versioning on a tabe, and connect it to a corresponding history table. Use a pair of DATETIME2 columns to represent the start and end of the validity period in the row. You modify the current table as usual, and SQL Server keeps track of historical states of rows in the history table automatically. To read data, you query the current table with the FOR SYSTEM_TIME clause. Using the AT subclause you request to return from the state of the data at a specified period of time. Using the ALL subclause, you request to return the the states of the rows, both current and historical.

What are some differences between user defined function and stored procedure?

1) Procedures can have input/output parameters for it whereas functions can have only input parameters. 2) Procedure allows select as well as DML statement in it whereas function allows only select statement in it. 3) Functions can be called from procedure whereas procedures cannot be called from function. 4) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function. 5) We can go for transaction management in procedure whereas we can't go in function. 6) Procedures can not be utilized in a select statement whereas function can be embedded in a select statement. 7) UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

What are the limitations of SQL pivot tables?

1) The aggregation and spreading elements cannot directly be results of expressions; instead, they must be column names from the queried table 2) COUNT(*) instead allowed 3) You are only limited to 1 aggregate function 4) The IN clause of the PIVOT operator accepts a static list of spreading values. It doesn't support a subquery. You need to know ahead of time the distinct values

What are the different types of subqueries?

1) self-contained - Have no dependency on the outer query (you can highlight the statement and run it, troubleshooting is easy) 2) correlated

Difference between VARCHAR and NVARCHAR?

Nvarchar stores UNICODE data. If you have requirements to store UNICODE or multilingual data, nvarchar is the choice. Varchar stores ASCII data and should be your data type of choice for normal use. Regarding memory usage, nvarchar uses 2 bytes per character, whereas varchar uses 1.

Data manipulation language

A data manipulation language is a family of syntax elements similar to a computer programming language used for selecting, inserting, deleting and updating data in a database. BULK INSERT (Transact-SQL) SELECT (Transact-SQL) DELETE (Transact-SQL) UPDATE (Transact-SQL) INSERT (Transact-SQL) UPDATETEXT (Transact-SQL) MERGE (Transact-SQL) WRITETEXT (Transact-SQL) READTEXT (Transact-SQL) Clause Can be used in these statements FROM (Transact-SQL) DELETE, SELECT, UPDATE Hints (Transact-SQL) DELETE, INSERT, SELECT, UPDATE OPTION Clause (Transact-SQL) DELETE, SELECT, UPDATE OUTPUT Clause (Transact-SQL) DELETE, INSERT, MERGE, UPDATE Search Condition (Transact-SQL) DELETE, MERGE, SELECT, UPDATE Table Value Constructor (Transact-SQL) FROM, INSERT, MERGE TOP (Transact-SQL) DELETE, INSERT, MERGE, SELECT, UPDATE WHERE (Transact-SQL) DELETE, SELECT, UPDATE WITH common_table_expression (Transact-SQL) DELETE, INSERT, MERGE, SELECT, UPDATE

What is sysname?

A data type within SQL Server. It is limited to 128 Unicode characters that, IIRC, is used primarily to store object names when creating scripts. Its value cannot be NULL It is basically the same as using nvarchar(128) NOT NULL

Difference between full outer join versus inner join

A full join will return rows from both tables even if there are no matching rows in the other table. A full join is like a right join and a left join at the same time. An inner join will only return rows which have at least 1 partner in the other table. ... Inner join wouldn't bring any NULLs in the join target field.

Explain what function determinism means and what are the implications of using non deterministic functions.

A function is said to be deterministic if given the same set of input values it is guaranteed to return repeatable results, otherwise it is said to be nondeterministic. If you use a nondeterministic function in a computed column, you cannot create an index on that column. Similarly, if you use a nondeterministic function in a view, you cannot create a clustered index on the view.

What are the differences between joins and set operators?

A join can compare a subset of the elements from the input tables while returning elements that it doesn't compare. Also, a join uses equality (or inequality) based on comparison as the join predicate, whereas a comparison between two NULLS or between a NULL and anything yields unknown. A set operator implicitly compares all expressions in corresponding positions in the two input queries. Also, a set operator uses distinctness based comparison, whereas a comparison between two NULLs yields true, and a comparison between a NULL and a non-Null value yields false.

In what way is the APPLY operator different than joins and subqueries? Can you provide an example when it should be used?

A join treats its two inputs as a set, therefore if a join input is a query (table expression), the query cannot refer to elements from the other input; in other words, a join doesn't support correlations. Subqueries can be correlated, but normally subqueries are limited to returning to only one column. The APPY operator combines the advantage of both joins and subqueries. Like in a join, if an input is a table expression, it can return multiple columns and multiple rows. Like a subquery, the right side can have correlations to elements from the left side. An example for case where APPLY is handy when you need to return the three most recent orders for each employee. You use the CROSS APPLY operator when the left input is the HR.Employees table and the right input in a correlated derived table where you use a TOP query against the Sales.Orders table, correlating the order's employee ID with the employee;'s employee ID.

Mathematical Functions

ABS DEGREES RAND ACOS EXP ROUND ASIN FLOOR SIGN ATAN LOG SIN ATN2 LOG10 SQRT CEILING PI SQUARE COS POWER TAN COT RADIANS

What is the difference between ALTER and UPDATE?

ALTER TABLE modifies the table definition of an existing table. UPDATE TABLE modifies data in a table.

modify table default constraint syntax

ALTER TABLE tbl1 ADD CONSTRAINT constraintName1 DEFAULT( ORIGINAL_LOGIN()) FOR CreatedBy

Add column to existing table syntax

ALTER TABLE tbl1 ADD col1 <type>;

modify column syntax

ALTER TABLE tbl1 ALTER COLUMN colName <type>

drop column syntax

ALTER TABLE tbl1 DROP COLUMN col1

How come you can't use an alias you define in the SELECT clause? Where can you use such as Alias

According to logical query processing, which describes the conceptual interpretation of a query: FROM, WHERE GROUP, BY, HAVING, SELECT, ORDER BY. You cannot use such as alias in other expressions in the same SELECT clause, that's because all expressions that appear in the same logical query-processing steps are treated as a set, and a set has no order. The only clause that can refer to aliases that are created in the SELECT clause is the ORDER BY clause, because that's the only clause that is evaluated after the SELECT clause.

What are the three sub query predicates?

All - evaluates criteria against all values in subquery, you get a true in all cases The two below mean the same thing Any - you get a true in any or some cases Some

What are system-versioned temporal tables?

Allow you to keep track of the history of changes to your data for long periods of time. You enable system versioning on a table and connect it to a corresponding history table. Temporal tables use a pair of DATETIME2 columns to represent the start and end of the validity period or the row. You modify the current table as usual and SQL Server keeps track of historical states of rows in the history table automatically.

What is the apply operator?

Applies query logic to each row in a table. It evaluates the left input first, and for each of its rows, applied a derived table query or table function that you provide as the right input.

What are the differences between COALESCE and ISNULL?

Because ISNULL is a function, it is evaluated only once. COALESCE expression can be evaluated multiple times. Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence. The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equivalent, have different nullability values. This makes a difference if you are using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed as shown in the following example:

CASE syntax

CASE col1 WHEN 'R' THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END

inline table valued function syntax

CREATE FUNCTION <name>(@var AS INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT * FROM <tblName>;

User defined function syntax

CREATE OR ALTER FUNCTION name(@var1 AS INT) RETURNS MONEY WITH SCHEMABINDING AS BEGIN query RETURN @totalsalary END;

Create Procedure Syntax

CREATE OR ALTER PROC schema.What_DB_is_that @ID int AS SELECT DB_NAME(@ID) AS ThatDB;

CREATE VIEW syntax

CREATE OR ALTER VIEW name WITH SCHEMABINDING AS SELECT * FROM tbl1

Stored procedure syntax that showcases how you should work with errors and transactions.

CREATE PROCEDURE insert_data @a int, @b int AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY BEGIN TRANSACTION INSERT sometable(a, b) VALUES (@a, @b) INSERT sometable(a, b) VALUES (@b, @a) COMMIT TRANSACTION END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH

Syntax for creating a sequence object

CREATE SEQUENCE dbo.seq1 AS INT START WITH 1000 INCREMENT BY 1; CREATE SEQUENCE dbo.seq AS INT MINVALUE 1000 MAVALUE 1000000;

Create temporal table syntax

CREATE TABLE Department ( DeptID int NOT NULL PRIMARY KEY CLUSTERED , DeptName varchar(50) NOT NULL , ManagerID INT NULL , ParentDeptID int NULL , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) ) WITH ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory) ) ; The history table is created using the same rules as apply to creating an "anonymous" history table, with the following rules that apply specifically to the named history table. The schema name is mandatory for the HISTORY_TABLE parameter. If the specified schema does not exist, the CREATE TABLE statement will fail. If the table specified by the HISTORY_TABLE parameter already exists, it will be validated against the newly created temporal table in terms of schema consistency and temporal data consistency. If you specify an invalid history table, the CREATE TABLE statement will fail.

CREATE VIEW syntax

CREATE VIEW <name> AS SELECT * FROM <tblName>

DATEPART(datepart, date)

To return an integer that represents a portion of a valid date, DATEPART extracts all parts of the datetime data type including years, months, days, hours, minutes, seconds and milliseconds: SELECT DATEPART(MONTH, GETDATE()) returns 7 as the example date's month.

UNION versus UNION ALL

UNION ALL appends all records and does not eliminate duplicate rows. A UNION statement effectively does a SELECT DISTINCT on the results set.

When should you use a temp table over a table expression?

Use temp tables where you would have to use the same query more than once.

When querying a temporal table, are you required to query the temporal table or the temporal table history table?

Use the temporal table because you don't know if the result set will be in the temporal table or the temporal table history. SELECT * FROM tbl1 FOR SYSTEM_TIME AS OF <datetime>

table expression

Use when you don't want to persist the inner query's result in a work table, and temporary tables or table variables when you do.

Pivot and Unpivot

Using the pivot operator to pivot data from a state of rows to columns, and the UNPIVOT operator to unpivot data from a state of columns to rows. With Pivot, make sure to use a table expression that project the elements involved to avoid grouping implicity by undesired columns. With UNPIVOT remember that the operator removes rows with NULLs.

common table expression syntax

WITH <name1> AS ( <inner query1> ), <name2> AS ( <inner query2> ) <outer query>;

OUTPUT Clause

We can use the OUTPUT clause with DML statements (INSERT, DELETE, UPDATE) to return information from modified rows. The results of the INSERT statement are stored in the Inserted table, and the results of the Delete statement are stored in the Deleted table. Also, with an UPDATE statement, the deleted rows are stored in the Deleted table. The new inserted rows in the Inserted table as UPDATE are nothing but delete and insert operations combined together. INSERT INTO dbo.Songs ( Id, Name, Singer) OUTPUT INSERTED.ID, INSERTED.name, INSERTED.Singer VALUES (5, 'AINT no grave', 'Johnny Cash'); DELETE from dbo.Songs OUTPUT DELETED.id, DELETED.name, DELETED.singer WHERE ID=5;

When should you use WITH VALUES clause explicitly as past of adding a column to a table?

When the column is defined as a nullable one, and you want to apply the default expression that is associated with the column in the new rows, you need to specify the WITH VALUES clause explicitly.

What is the difference between a self-contained subquery and correlated one?

When the inner query is completely independent of the outer query, it's self-contained subquery. It can be highlighted and executed independently. A correlated subquery has reference to columns from tables in the outer query. It cannot be run independently, making it harder to troubleshoot.

What to know about XML

XML is: case sensitive ordered documents < intros a tag and processed as markup

A TRY ... CATCH construct must be contained within a single batch

Yes

Stored procedures determine which operations users are allowed to perform on underlying tables?

Yes

Stored procedures simplify permissions on database objects.

Yes

Use THROW when developing new SQL code?

Yes

You can use a table-valued function in a view?

Yes

What are the five limitations of a user defined functions?

You cannot : 1) Use error handling 2) Modify data 3) Use data definition language (DDL) 4) Use temp tables 5) Use dynamic SQL

Creating XML Documents

You create XML documents as output of a SELECT statement with the FOR XML clause. You can read XML and shred it to tabular format with the OPENXML function. You can store XML data in SQL Server in a column of the XML data type. You can use methods of this column to extract scalar values, XML fragments, modify XML data, and more. You use XQuery expression as parameters to the XML data type methods to navigate to the appropriate element or attribute.

What is the difference between the JSON_VALUE and JSON_QUERY functions

You extract a scalar value from JSON text with the JSON_VALUE function and an object or an array with the JSON_QUERY function.

How do you read data from system-versioned temporal tables?

You query the current table with the FOR SYSTEM_TIME clause. Using the AT subclause you request to return the state of the data at a specified point in time.

How would you quickly create, with minimum effort, and element-centric XML document from a T-SQL SELECT result set?

You should use the FOR XML AUTO, ELEMENTS, ROOT('Root Name') clause. You could use also FOR XML PATH, but this would not be with minimal effort. You need the ELEMENTS sub-clause to generate an element-centric XML. Finally, you need the ROOT sub-clause because without root you get an XML fragment and not an XML document.

Aggregate Functions

_____________________ perform a calculation on a set of values and return a single value. Except for COUNT, they ignore null values. Are frequently used with the GROUP BY clause of the SELECT statement. All _________________________________ are deterministic. This means they return the same value any time that they are called by using a specific set of input values. AVG, MIN, CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, STRING_AGG, GROUPING_ID, VAR, MAX, VARP

Derived Table

a named table subquery. A table expression that appears in the FROM clause of a query.

What is a derived table?

a named table subquery. Found in the FROM clause of an outer query.

What are window functions?

allow you to perform data analysis calculations against the underlying query result without hiding the detail. T-SQL support aggregate, ranking, offset, and statistical functions.

Deterministic functions

always return the same result any time they are called with a specific set of input values and given the same state of the database. All built-in string functions are deterministic. SCII CHAR CHARINDEX CONCAT DIFFERENCE FORMAT

Cross Apply

applies a table function over another table. Uses inputs from the left table to generate records on the right. The main purpose is to enable table functions with parameters to be executed once per row and then joined to the results. SELECT * FROM Department D CROSS APPLY ( SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID ) A

table expressions

are named queries that help you simplify and reuse code. T-SQL supports four kind of table expressions: derived tables, CTEs, views and inline table-valued functions

Table Expressions

are named queries that you query from. There are four forms: derived tables, common table expressions, views and inline table-valued functions. The first two forms are visible only to the statement that defines them. As for the last two forms, you preserve the definition of the table expression in the database as an object.

Apply

can be used to apply query logic to each row from a table. The operator evaluates the left input first, and for each of its rows, appies a derived table query or table function that you provide as the right input.

Row Number

computes unique incrementing integers from 1 and is based on indicated ordering, possibly a partition of rows. Is a window function.

What are the two forms of the apply operator?

cross apply and outer apply

datetime versus datetime2

datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision. Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

Which table expressions are only visible to the statement that defines them?

derived tables and common table expressions

Window functions

enables you to perform data analysis computations. You define a set of row per function - and then return one result value per underlying row and function. These functions are only allowed in the SELECT and ORDER BY clauses of the query. If you need to refer to those clauses in the WHERE clause, you need to use a table expression/ CTE. You invoke the window function in the inner query SELECT clause, and assign the expression with a column alias. Then you refer to that column alias in the outer query WHERE clause. That's a common need with ranking calculations. T-SQL supports aggregate, ranking, offset, and statistical functions.

REPEATABLE READ

ensure that the stored procedure cannot read modified data that is not yet committed. It further ensures that other transactions cannot modify data that is being processed by the stored procedure.

What is DATEDIFF_BIG?

function that returns a big integar, which contains the difference between a startdate and enddate to the nearest microsecond

Self-contained subqueries

have no dependence on the outer query. Query can be run independently by highlighting a executing it. There are case when SQL Server handles subqueries more efficiently then joins, and cases where the opposite is true. It's important understand those cases

Correlated Subqueries

having a reference to a column from the table in the outer query. Are complex to work with.

TRY...CATCH Syntax

implements error handling for Transact-SQL BEGIN TRY <regular code> END TRY BEGIN CATCH <error handling> PRINT 'This is the error: ' + error_message() END CATCH

What is XACT_ABORT?

instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs.

Set operators

mainly used to combine the same type of data from two or more tables. UNION - Combine two or more result sets into a single set, without duplicates. UNION ALL - Combine two or more result sets into a single set, including all duplicates. INTERSECT - Takes the data from both result sets which are in common. EXCEPT - returns distinct rows from the left input query that aren't output by the right input query.

How to compare tables when NULL is present

make sure you are explicit and use IS NULL in the join with an OR clause.

Nondeterministic functions

may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. For example, the GETDATE function, which returns the current datetime value, always returns a different result at every run. ALL AGGREGRATE FUNCTION CAST CONVERT CHECKSUM ISDATE RAND

What data type to use if XML element tags must be able to be retrieved exactly as supplied, rather than canonical form, whitespace must be preserved, and currently UTF -16 encoded

nvarchar(max) NOT xml (it modifies the sysntax)

System Functions

perform operations on and return information about values, objects, and settings in SQL Server. $PARTITION ERROR_PROCEDURE @@ERROR ERROR_SEVERITY @@IDENTITY ERROR_STATE @@PACK_RECEIVED FORMATMESSAGE @@ROWCOUNT GET_FILESTREAM_TRANSACTION_CONTEXT @@TRANCOUNT GETANSINULL BINARY_CHECKSUM HOST_ID CHECKSUM HOST_NAME COMPRESS ISNULL CONNECTIONPROPERTY ISNUMERIC CONTEXT_INFO MIN_ACTIVE_ROWVERSION CURRENT_REQUEST_ID NEWID CURRENT_TRANSACTION_ID NEWSEQUENTIALID DECOMPRESS ROWCOUNT_BIG ERROR_LINE SESSION_CONTEXT ERROR_MESSAGE SESSION_ID ERROR_NUMBER XACT_STATE

FULL OUTER JOIN

return all records when there is a match in either left (table1) or right (table2) table records. SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

GETDATE()

returns a datetime data type containing the current system data and time: 2009-07-07 11:52:26.687. SELECT GETDATE()

LEFT JOIN

returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

RIGHT JOIN

returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;

EXCEPT definition

returns all rows which are in the first query but those are not returned in the second query. The number and the order of the columns must be the same in all queries. The data types must be compatible.

CROSS JOIN

returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table. Use when and only when you don't compare columns between tables. That suggests that the lack of comparisons was intentional. (it functions like an INNER JOIN but does not contain the ON) SELECT * FROM table1 CROSS JOIN table2; Use case: If you have a "grid" that you want to populate completely

deterministic funtion

returns the same result each time it is executed

What three elements do you need to identify when pivoting data?

rows, columns, the aggregate intersection

What results can a sub query return?

scaler multi-valued (table with single column) multi-columned (table with multiple columns)

INNER JOIN

selects records that have matching values in both tables

inline table-valued functions

similar to views in concept, however, support input parameters.

DELETE Statement

statement is used to delete existing records in a table. DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste'; DELETE FROM table_name;

Outer Apply

the same as cross apply, however, returns records from the left table where the table expression functions returns nulls.

What are table expressions?

they are named queries. There are four different types of them: derived tables, common table expressions (CTEs), views, and inline table-valued functions)

You need to perform a multi-row insert into a target table that has a column with an identity column with an identity property. You need to capture the newly generated identify values for further processing. How you achieve this?

use the OUTPUT clause and write the newly generated identity values along with any other data that you need from the inserted rows aside, for example into a table variable. You can then use the data from the table variable in the next step where you apply further processing.

Apply operator

use when you can apply a table expression to each row from some table. The CROSS APPLY operator doesn't return the left row if the right side is an empty set, whereas the OUTER APPLY operator does.

Common Table Expressions (CTE) / derived tables

use when you need to use the table expression only in one statement. Use views and inline table valued functions when you need to reuse the table expression. If there are no parameters involved, use views, otherwise use inline table-valued functions.

UPDATE Statement Syntax

used to modify the existing records in a table. UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Which is better for performance user-defined inline function or a user-defined table valued function?

user-defined inline function

Which table expressions preserve the definition in the database as an object and therefore reusable?

views and inline table-valued functions

Correlated subqueries

where the inner query has a reference to a column from the table in the outer query. These queries can't be run independently and are thus more complex.

SARGable expressions and performance

where you can use an index seek, because you're looking for a single value or range of values in the index. The opposite of a SARGable query is when you perform some type of function or calculation on each row in the table, and match the result of this calculation to your search criteria. When you can't use an index to look for a single value or range, you're performing a table scan or index scan, which means you're traversing the entire table or index, which is a costly operation if there are a lot of rows. non-SARGable query: SELECT Name, CreateDate FROM dbo.CoffeeInventory WHERE DAY(CreateDate) = 19 SARGable query: SELECT Name, CreateDate FROM dbo.CoffeeInventory WHERE CreateDate >= '2017-08-19 00:00:00' AND CreateDate < '2017-08-20 00:00:00' In short, keep in mind whether SQL Server will have to modify the data in a column/index in order to compare it — if it does, your query probably isn't SARGable and you are going to end up scanning instead of seeking.

You can use a table valued function in a view

yes