70-761

What are the results of the following statements? SELECT REPLACE('.1.2.3.', '.', '/'); SELECT REPLICATE('0', 10); SELECT STUFF(',x,y,z', 1, 1, ''); SELECT UPPER('huga');

/1/2/3/ 0000000000 x,y,z HUGA

What returns SELECT TIMEFROMPARTS(2, 2, 13, 23, 4);?

02:02:13.0023

SELECT DATEDIFF(YEAR, '20111231','20120101');

1

What's the range of ERROR_STATE?

1 to 255

What's the range of the third parameter of the THROW command?

1 to 255

A searchargument has the following structure: WHERE <column> <operator> <expression>. What are the two main prerequisites that makes such a filter a search argument (sargable)?

1. No manipulation added to the filter column. 2. The operator identifies a consecutive range of qualifying rows in the index.

What are the two main difference between identity and the sequence object?

1. Sequence is an independent object in the database and therefore it is not tied to a certain column 2. You can request a new value from a squence object before using it.

If you want to store logging data in a table from the CATCH block you could have a problem if the error sets the transaction in to a doomed state. How would you proceed to still achive this?

1. Write your data in to a table variable (the only thing that isn't undone when a transaction rolls back) 2. Use a ROLLBACK TRAN loop to make sure you are not in a open but doomed connection. 3. Copy all the data from the variable in to the logger table.

What is the result of the following statement: SELECT DATALENGTH(N' abc '); SELECT DATALENGTH(' abc ');

10 5

What do you get for CAST(10.999 AS NUMERIC(12, 0)) CAST(10.999 AS INT)

11 10

What return values from ERROR_SEVERITY are severe errors and not just informational?

11 to 19

Can you guess the output of the following? SELECT ISNULL('1a2b', 1234) AS ISNULLxy; SELECT COALESCE('1a2b', 1234) AS COALESCExy;

1a2b for the second you get an error because COALESCE takes the one with the higher precedence for the type but you can't convert '1a2b' to an INT

What does SELECT DATEPART(MONTH, '20120212'); return?

2

What do you get for 1 + '1'?

2 (not '11')

Answer the following questions first for ISNULL the for COALESCE Number of supported parameters? Is it standart SQL? Might execute subqueries more than once and hence has weaker performance?

2 : >2 No : Yes No : Yes

What languages are supported with regular character types (CHAR, VARCHAR)?

2. English and an other depending on the collation properties.

From what severety will sql server terminate the connection?

20

What returns SELECT DATETIMEFROMPARTS(2012, 2, 13, 23, 59, 59, 0);?

2012-02-13 23:59:59.000

What returns SELECT DATETIMEOFFSETFROMPARTS(2012, 2, 13, 23, 59, 59, 0, 2, 0, 3);?

2012-02-13 23:59:59.000 +02:00

What returns SELECT DATETIME2FROMPARTS(2012, 2, 13, 23, 59, 59, 3, 5);?

2012-02-13 23:59:59.00003

SELECT DATEADD(YEAR, 1, '20120212');

2013-02-12 00:00:00.000

What returns SELECT EOMONTH('2014-01-23', 1);?

2014-02-28

SELECT TODATETIMEOFFSET('2015-1-05','+05:00');

2015-01-05 00:00:00.0000000 +05:00

What do you get for @s AS CHAR(21) = '20170212 23:59:59.999', @dt2 AS DATETIME2 = '20170212 23:59:59.999999'; SELECT CAST(@s AS DATETIME) CAST(@DT2 AS DATETIME)

2017-02-13 00:00:00.000 2017-02-13 00:00:00.000

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+05:00'); Provide an explanation what this function is used for.

2017-07-30 16:41:25.3580156 +05:00 Use it to select a datetimeoffset value into a time zone offset that is different form the time zone offset that was originally stored.

What is the result of the following statement: SELECT LEN(N' abc ');

4

What returns SELECT PATINDEX('%[0-9]%', 'abcd123efgh');

5

Where starts the first parameter of the THROW command at?

50000

What returns SELECT CHARINDEX(' ', 'Itzik Ben-Gan');?

6

A searchargument has the following structure: WHERE <column> <operator> <expression>. Give a few expamples of operators that make a filter a searchargument.

=, >, <, >=, <=, BETWEEN, LIKE with a known prefix.

What function helps you to find out wheter you are in a open transaction or not? And how is it's return value to be interpreted?

@@TRANCOUNT. If the value is grater than zero you are in an open transaction.

What is the difference between a CROSS APPLY and an OUTER APPLY?

A CROSS APPLY returns nothning for a row on the left when right side returns an empty result set. With the OUTER APPLY the lefts side is preserved just like with an OUTER JOIN.

In short, what are the benefits of a VIEW

- It can act as a security layer to restrict the access for certain users to the underlying tables - Simplify the acces to data

Name three situations where altering a column would fail.

- The column is used in a check or unique constraint unless your only keeping or increasing the length - The column is used in a foreign key or primary key constraint - The column is used in a default constraint unless your changing the length, precicion or scale of a column as long as the data type is not changed

Name three situations where dropping a column would fail.

- The column is used in an index - The column is used in a default, check, foreign key, uniqe or primary key constraint - The column is bound to a default object or a rule

If you want to index a view you need to fullfill some restrictions. What are they?

- The view header needs SCHEMABINDING - If the query is a grouped query it has to include COUNT_BIG aggregate. - It is not allowed to manipulate the result of an aggregate calculation

How would you alter the primary key column in a table.

- create an other table - copy all the data - drop the original table - rename the new table to the name of the original

What are the four forms of table valued expressions?

- derived tables - common table expressions (CTEs) - views - inline table-valued functions

What are the three kinds of user defined functions?

- scalar - inline table valued - multi-statement table valued

With XACT_STATE you can figure the state of a transaction. What is the returnvalue for what state?

-1, transaction is doomed 0, no transaction is open 1, transaction is open and commitable

What kind of JOIN would you have to use to preserve both sides of a join?

A FULL OUTER JOIN (FULL JOIN)

What is a derived table?

A derived table is a named table subquery. It's the inner query of an outer query aliased with a name after the parentheses of the table defining inner query.

What is a composit join?

A join with multiple join columns.

What is the problem with the following query, assuming n is a column in Nums? SELECT n AS theday, n AS shiftno FROM dbo.Nums CROSS JOIN dbo.Nums WHERE n <= 7 AND n <= 3 ORDER BY theday, shiftno;

Aliasing for the Nums table is mandatory.

How is the structure to grant execution right for the dbo.GetOrders object to the user user1?

GRANT EXEC ON dbo.GetOrders TO user1

What is the "identity column" property? How is this property used?

A property that automatically generates keys in an attribute (column) of a numeric type with scale of 0. You can use it to generate surrogate keys. IDENTITY(seed, increment) --> seed is the very first key when a row is inserted, increment is the amount that is incremented on top of the key generated before. If seed and increment is not specified, the default is IDENTITY(1,1)

SELECT custid, orderid, orderdate, val, SUM(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS X FROM Sales.OrderValues; What is X?

A running total. The summ is taken per row from the beginning of the window alwas till the current row if you think of it as cursor going trough.

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

A self-contained query can run independently on it's own. You cold run it in the ssms when you mark it.

What is the doomed state?

A transaction in this state is not commitable anymore.

How is the structure of a scalar user-defined functions?

CREATE OR ALTER FUNCTION <funcName>(params) RETURNS <type> [WITH SCHEMABINDING] AS BEGIN <functionBody> RETURN <scalar> END

What does the structure of inline table-valued user-defined function look like?

CREATE OR ALTER FUNCTION <funcName>(params) RETURNS TABLE [WITH SCHEMABINDING] AS RETURN functionbody

How does the structure of a stored procedure look like?

CREATE OR ALTER PROC <procName> <parametersList> AS [BEGIN] <body> [END]

Issuing an UPDATE when the data doesn't change because there's nothing to update is waist of time and could fire f.e. triggers unnessecairly. How would you prevent an unnessecair update in a MERGE statement?

Add a predicate that makes sure that the values in at least one column is different.

How do use the OUTPUT statement to output inserted clumns and insert that output directly into an other table?

After the INSERT INTO <tablename(columns)> you write OUTPUT <with inserted prefixed columnnames you want to track> INTO<tablename(columns)>

What is the sequence object?

An independent object in the database from which you can obtain new sequence values. You can use it to generate surrogate keys.

transactional statements include DML, DDL and DCL (like GRANT or REVOKE) statements, what is not included in this and therefore are not undone with a rollback?

Assigning values to variables as well as modufying datat in tablevariables.

Where in between or after FROM, WHERE, GROUP, HAVING, SELECT, DISTINCT is TOP and OFFSET-FETCH logically processed?

At last. You can consider these filters as being an extension to the ORDER BY clause.

What taype should you consider for a key when INT becomes to small?

BIGINT

What is the problem with the following part of a CATCH block? IF @@TRANCOUNT > 0 ROLLBACK TRAN THROW;

Because ROLLBACK TRAN isn't terminated THROW isn't recognized as a command. It's rahter recognized as a savepoint or transaction name.

Why is it that all columns of the inner query of a table expression musst have names and are not allowed to have an ORDER BY?

Because it must be relational and remember a set has no order.

Why is it recommendet to always specify a window frame in a function where it is applicable?

Because otherwise the default RANGE BETWEEN UNBOUNDED PRECEEDING will be used and the RANGE frame is not good for performance reasons.

Name one or more fixed types and one or more dynamic types

CHAR, NCHAR, BINARY VARCHAR, NVARCHAR, VARBINARY

What are three alternative writings for COMMIT TRANSACTION?

COMMIT TRAN, COMMIT WORK or COMMIT

Give examples for the following three functions. Out of literal '1/2/2017' make a DATE. use 101 for style and en-US for the culture where adequate. CONVERT CAST PARSE

CONVERT(DATE, '1/2/2017', 101) CAST('1/2/2017' AS DATE) PARSE('1/2/2017' AS DATE USING 'en-US')

Name five standard SQL set functions (aggregate functions)

COUNT SUM AVG MIN MAX

What aggregation function is not allowed as the aggregate function used by the PIVOT operator?

COUNT(*)

How does the structure of a multistatement table-valued user-defined function structure look like?

CREATE FUNCTION <funcName>(params) RETURNS <@tableVariableName> TABLE ( <tabledefinition> ) [WITH SCHEMABINDING] AS BEGIN <body that fills the @tableVariableName> RETURN END

What is the structure of a inline table-valued function? Use <CTE> for the common table expression, <function(param)> and the keywords, RETURN, AS, RETURNS TABLE, FUNCTION, CREATE

CREATE FUNCTION <function(param)> RETURNS TABLE AS RETURN <CTE>

How do you create a login with password?

CREATE LOGIN login1 WITH PASSWORD = 'myPW'

How do you create a INT SEQUENCE called Sales.SeqOrderIDs starting at 1 with a cache value of 10000?

CREATE SEQUENCE Sales.SeqOrderIDs AS INT MINVALUE 1 CACHE 10000;

How do you create a USER user1 for the LOGIN login1

CREATE USER user1 FOR LOGIN login1

What do the COMPRESS and DECOMPRESS functions? Make an example of the usage.

Compresses an input character into a result binary string. With DECOMPRESS you can revoke it. COMPRESS(@notes). DECOMPRESS(notes) as NVARCHAR(MAX)

What is the cost you pay when you index a view?

Cost of extra write cost and space.

What is conceptually returned by a query with an ORDER BY clause, a relation (relational result) or a cursor?

Cursor.

Why is it better to use DATE vs DATETIME when only storing the date without time?

DATETIME uses 8 bytes of storage whereas DATE only uses 3 bytes.

Why is DATETIME2 better than DATETIME?

DATETIME2 requires 6 to 8 bytes depending on precision and a wider range of dates. DATETIME uses 8 bytes and has less control of the precision.

Let's say you want to create a table with a id column "DFT_MyOrders_orderid" based on a SEQUENCE that should be automatically incremented every time a row is inserted. The statement begins as following. How would you continue the statement to achieve that? CREATE SEQUENCE Sales.SeqOrderIDs AS INT MINVALUE 1 CACHE 10000; CREATE TABLE Sales.MyOrders ( orderid INT NOT NULL CONSTRAINT PK_MyOrders_orderid PRIMARY KEY CONSTRAINT DFT_MyOrders_orderid

DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs)

What is the difference between DELETE and TRUNCATE TABLE concerning the speed and why?

DELETE is slower than TRUNCATE TABLE because TRUNCATE TABLE has an optimized logging mode.

How many rows returns "SELECT TOP (3) WITH TIES ... ORDER BY orderdate DESC"? Is this result deterministic? And is the order guarateed?

Depends how many rows are in the database and how man rows are from the same orderdate. So if f.e. there are more than 3 entires most recent entries from the same date, you will get more than 3 rows in the result. Yes, you will get now alwas the most resent orders. The order inbetween the same date isn't guaranteed.

How do you execute a stored procedure?

EXEC <procName> [list of params and values f.e. @orderdate = '20151111', @custid = 85]

How do you set the context to login1

EXECUTE AS LOGIN = 'login1'

What is a drawback of RAISERROR compared to THROW

Errors lower than 20 doesn't terminate the batch, nor does it terminate or doom the transaction, irrespective to the XACT_ABORT option.

The following is a fragment of a query that uses PIVOT. Describe what comes in the gaps. *From PivotData PIVOT( ___ FOR ___ IN ___ ) AS P*

From PivotData PIVOT( <aggragate function> (<aggragation column>) FOR <spreading column> IN <columnheaders> ) AS P

What is GO for?

GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities. GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

What's the short form of ROWS BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW?

ROWS UNBOUND PRECEEDING

Whats the property called and how does it look to automatically increment the value starting at 1 on a orderid not null column that's supposed to be the primary key?

IDENTITY(1,1)

How does a statement look like to get data into a table in pseudocode?

INSERT INTO <tablename>(<column names>) VALUES (<values>)

How is the precedence between INTERSECT, UNION and EXCEPT?

INTERSECT preceds UNION and EXCEPT. UNION ans EXCEPT are evaluated from left to right as they appear in the query.

What's the exception for a table expression to be allowed to have an ORDER BY anyway?

If it's in the use with a TOP or OFFSET-FETCH since it serves an other meaning there and is part of the filter's specification.

When would a predicate not be of the form of a "column operator value" and therefore no "search argument"?

If the column would be appyied to some sort of a function.

With @x AS VARCHAR(3) = NULL, @y AS VARCHAR(10) = '1234567890' SELECT ISNULL(@x, @y) AS ISNULLxy, COALESCE(@x, @y) AS COALESCExy you get the following result: ISNULLxy COALESCExy ---------- --------------- 123 1234567890 Explain.

If the first parameter has a type, ISNULL takes thats the type for the result which is VARCHAR(3) COALESCE however takes the type with the highes precedence what is VARCHAR(10)

Name 4 window ranking functions.

ROW_NUMBER() RANK() DENSE_RANK() NTILE(n)

Are the following functions deterministic? ROW_NUMBER() RANK() DENSE_RANK() NTILE(n)

If the ordering is not unique, it's not. Otherwise yes. Yes. Yes. If the ordering is not unique, it's not. Otherwise yes.

What is to be considered when thinking about update performance when deciding whether choosing fixed or dynamic types?

If you can take fixed types, no data shifting is required. That's good for the update performance.

If we know that fixed datatypes are better for the performance when and why is it recommendet to use variable types for better performance?

If you have strings that vary a lot it is better to use variable types since small strings use less storage and less storage means better performance.

What does the view attribute ENCRYPTION?

If you want the definition of a VIEW you can use OBJECT_DEFINITION(). If the VIEW is created with ENCRYPTION, the definition is hidden and you get back NULL.

What is the $action function in a MERGE statement good for?

In a MERGE statement you can have inserts and deletes in the same run. If you now want to OUTPUT the data that changed you need to know if it was inserted or deleted. You use the $action function to print out that information.

Beside applying a function to a column, what would also be a violation of the "search argument" form?

In general manipulation of the filtered column. Like col1 - 1 <= @n. You could rewrite that to col1 <= @n + 1 and you are on the safe side again.

What does DATENAME?

It is similar to DATEPART, only it returns the names of the part as character string. Note that the funciton is language dependent.

In what clauses is it allowed to use window functions in a query?

In the SELECT or ORDER BY clause.

What could you do if you want your stored procedure always to execute as if the owner of it would execute it?

In the procedure header use the EXECUTE AS OWNER option

What is the LOCK_TIMEOUT and how do you set it to zero? How do you release it?

It is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. SET LOCK_TIMEOUT 0; You release it with a COMMIT TRAN

What's the purpose of the USING clause in a MERGE statement?

It defines the source table where you can alias the table in this clause if you want. It is similar to the FROM in a SELECT statement. You can choose to give back a table result from a join or even create the table on the fly.

There are 3 suppliers in the table. How many rows will be return by the following query? Explain. SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S LEFT OUTER JOIN Production.Products AS P ON S.supplierid = P.supplierid AND S.country = N'Japan';

It depends on how many suppliers in total are in this table and how many matches the supplierid and country criteria. ON is a matching clause and retruns all the rows from the left side plus all the matching rows from the right side.

What dose the function FIRST_VALUE(...) Does it support window frame clauses

It gives you the first value out of a window frame. Yes.

What dose the function LAST_VALUE(...) Does it support window frame clauses

It gives you the last value out of a window frame. Yes.

What dose the function LEAD(...) Does it support window frame clauses

It gives you the the next value in a partition window No.

What dose the function LAG(...) Does it support window frame clauses

It gives you the the previous value in a partition window No.

If you index a view, how has the first index to be defined?

It has to be clustered and unique

What does PARSE? give an explanation! give an example!

It is a type casting function. You can indicate the culture by using any culture supported by the Microsoft .NET Framework. select PARSE('14/2/2012' AS DATE USING 'de-CH') returns 2012-02-14

What does TRY_PARSE? give an explanation! give an example!

It is a type casting function. You can indicate the culture by using any culture supported by the Microsoft .NET Framework. When the statement fails, then it returns null. select PARSE('14/2/2012' AS DATE USING 'de-CH') returns 2012-02-14

What happens if a scalar subquery returns an empty set?

It is converted to a null.

What is a savepoint?

It is possible to only rollback all the statements untill the savepoint.

What does CAST? give an explanation! give an example!

It is used cast values of one type into an other. If the cast fails, the query fails too. It is standard SQL. SELECT CAST(1 AS BIT)

What does TRY_CAST? give an explanation! give an example!

It is used to cast values of one type into an other. If the query fails, it returns null SELECT TRY_CAST('abc' AS INT)

What is autocommit?

It means if you don't have a BEGIN TRANS and END TRANS you have autocommit. With autocommit each individual statement is considered a separate transaction.

What is understood under the *physical data independence principle*?

It means that the type is a property of the value stored in the database. The storage format shouldn't be of your concerne.

Briefly explain what an INNER JOIN (JOIN) does?

It only returns matching rows for which the predicate evaluates to true.

From the logical processing point of view where operates a join?

It operates in the FROM clause.

Briefly explain what an OUTER JOIN does?

It preserves all rows from one or both sides of the join, regardless if there are matching rows in the other side based on the ON predicate.

What is the main benefit in altering a view, as opposed to dropping and recreating it?

It preserves the permissions

What does the option CHECK OPTION?

It prevents inserting and updating rows through a view where it contradicts the inner query's filter.

What is the SCHEMABINDING option for?

It prevents structural changes to dependent tables and columns while the view exists.

With GROUPING(<column>) you can figuer out if a column is part of a grouping set and the value is really null or if it's just a placeholder because it's not part of the grouping set. What are the returnvalues and what's their meaning?

It returns 0 if the value of the column is really null and 1 if it's not part of the groupingset and just a placeholder.

Explain short what EXCEPT does.

It returns distinct rows that appear in the result of the first query but not the second. In other words if a row appears at least once in the first query but not at all in the second query, then it will be returned.

Explain short what INTERSECT does.

It returns only the distincted rows that are common in both sets (query results).

What does GETDATE()? Is it standard SQL?

It returns the current DATETIME. No it's T-SQL

What does CURRENT_TIMESTAMP? Is it standard SQL?

It returns the current DATETIME. Yes.

What does GETUTCDATE()?

It returns the current UTC DATETIME.

What does SYSUTCDATETIME()?

It returns the current UTC DATETIME2.

What does SYSDATETIME()?

It returns the current more precise DATETIME2.

What does SYSDATETIMEOFFSET()?

It returns the current more precise DATETIME2. The timezone is included.

What does the @@ROWCOUNT function?

It returns the number of rows affected by the last statement that you executed.

What does NOCOUNT option and why is it good to turn it on in stored procedures?

It supresses the message indicating how many rows were affected by a data manipulation. It's not good for the query performance due to network traffic generated by the messages.

How does a multistatement table-valued user-defined function work. Say it in prosa.

It's a function just like a inline table-valued function. The function declares a table variable in it's header that then is filled with rows in the body and then returned.

What is the use of PIVOT?

It's a specialized case of grouping and aggregating data. You can kind of swap a result set and give out the rows as columns.

What does CONVERT? give an explanation! give an example!

It's a type casting function. On contrast to CAST it is non standard SQL. It also accepts a third argument representing the style by that the result will be formatted. SELECT CONVERT(DATE, '1/2/2012', 101) returns '2012-01-02'

What does TRY_CONVERT? give an explanation! give an example!

It's a type casting function. On contrast to CAST it is non standard SQL. It also accepts a third argument representing the style by that the result will be formatted. If the conversion fails, it returns null SELECT TRY_CONVERT(DATE, '1/2/2012', 101) returns '2012-01-02'

What's the point of CUBE and where in the query comes the keyword?

It's an abbreviation of GROUPING SETS. It creates all possibel combinations of groupings of the given columns to group against. It comes right after the GROUP BY clause.

What's the point of ROLLUP and where in the query comes the keyword?

It's an abbreviation of GROUPING SETS. It creates groupings in the hirarchical ordering of the given columns to group against. It comes right after the GROUP BY clause.

What is a good reason to be consistent with the types when defining attributes that represent the same thing like Primarykey on one table and Foreignkey on the oder?

It's good for the performance. If you fail that SQL server has to do implicit conversions and that costs performance.

Explain what a *window aggregate function* is.

It's like a group aggregate function except it's applied to a window of rows defined by the OVER clause.

What's the difference between ROWS and RANGE in a window aggregate function?

It's similar only RANGE is limited. It knows only the delimiters UNBOUND PRECEEDING, FOLLOWING AND CURRENT ROW and it's taking in the peers as well.

What is the implicit transactions mode and how do you switch it on??

It's similar to the autocommit mode. You don't have to open a transaction, this is done implicitly but you have to close it explicitly.

Whats the advantage and what's the drawback of Nonsequential GUID?

It's unique across space an time but it uses a lot of storage.

What is also a valid key word for LEFT OUTER JOIN?

LEFT JOIN

How do you set a LOCK_TIMEOUT to infinity?

LOCK_TIMEOUT -1

How does the window ranking function ROW_NUMBER() part of a query look like. The ordering is over the column "val"?

ROW_NUMBER() OVER(ORDER BY val)

When compression is on, how does SQL server store tixed taypes?

Like variable ones but with less overhead.

Are most nondeterministic functions called once per row or per query? Are there exceptions? If yes, make an example.

Most nondeterministic functions are called once per query. An exception is NEWID(). It is called once per row.

What function can you use to generate a new GUID?

NEWID

What will the following statements return? SELECT NULLIF(1, 1); SELECT NULLIF(2, 5); Is NULLIF standard SQL?

NULL. 2. YES.

Can you use PERCENT and WITH TIES together witch OFFSET-FETCH?

No, only with TOP.

Can you use a subquery to define the list of the spreading columns?

No.

Does it matter if I specify my predicate in the ON or WHERE clause in an INNER JOIN?

No.

Assume you have the following query: WHERE pType = 'INT' AND CAST(pVal AS INT) > 10. pType has stored the type of the value so when pType is INT, then an INT is stored. Can you tell if this query is safe and will never fail or not? Explain your Answer. How would you rewrite the query?

No. Expressions in the same logical processing phase are evaluated at the same point in time. That means, the CAST part could be evaluated first and fail if the value is no an INT. Use TRY_CAST instead of CAST.

Can a filter be considered a search argument if you apply operators like <> or LIKE with a wildcard? Explain why.

No. Because that doesen't quarantiee a consecutive range of rows in the index.

Is ownership chaining applicable to using dynamic sql?

No. Only static objects and SELECT, INSERT, UPDATE, DELETE

Suppose you have a stored precedure that opens a transaction and within this transaction you alter data of a table. The transaction in the procedure is commited. The outer query though rollbacks the transaction. Is the altered data now permanent or not?

No. SQL Server doesn't know real nesting of transaction. The last commit or rollback statement takes it all.

Will the following query perform properly? Explain. SELECT country, region, city FROM HR.Employees ORDER BY country UNION SELECT country, region, city FROM Sales.Customers;

No. Since UNION is a set operator you cannot have an ORDER BY in the individual queries.

In a *window ranking function* the *window order* clause (OVER()) is mandatory. In the window order clause an ordering is specified. Does that mean that the ordering of the result set is guaranteed? Explain why.

No. The reason for this ORDER BY is for computation, not presentation ordering. Therfore ordering is not guaranteed.

When using the INSERT statement do you have to insert a value for the IDENTITY column if you have one?

No. This happens automatically

Is it a good idea to use TRY CATCH and RAISERROR in a user defined function?

No. none of them are premitted there.

If XACT_ABORT is off, what happens to if you use THROW while an open transaction and you're not using a TRY CATCH. Is the transaction gonna be aborted or not?

Not aborted and still commitable

Let's say you have a table with a column region. This columns has only 4 null entries and 5 'WA' entries. If you have a predicate like region <> N'WA' what will be returned? How would a predicate look like, that brings back what really was intended?

Nothing because the predicate against null will evaluate to unknown and the rows of that result will be discarded. region <> N'WA' OR region IS NULL

With what function can you get the definition of an existing view?

OBJECT_DEFINITION(...)

If TOP and OFFSET-FETCH are logically equivalent what is recommended to stick to and why?

OFFSET-FETCH. It is standard SQL, TOP is not.

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

ON WHERE HAVING

What clause do you additionally need if you want to limit the rows in the window aggregate function with a window frame?

ORDER BY. Limiting rows with no order is senceless.

Is a nondeterministic function when indirectly invoked over a UDF invoked once per query or once per row?

Once per row.

Is a nondeterministic function invoked once per query or once per row?

Only once for the entire query. The NEWID function is an exception.

How many aggregate functions are allowed when using PIVOT?

Only one.

Why should you choos CONVERT or CAST over PARSE?

PARSE is significantly slower than the others.

When a window frame is applicable to a function, what is the default frame if you don't explicitly specify a frame.

RANGE BETWEEN UNBOUNDED PRECEEDING.

How do you get back to the original execution context?

REVERT

What does SCOPE_IDENTITY()?

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch

How do you set a savepoint you can rollback to? An how do you rollback to it?

SAVE TRANSACTION <savepoint name> ROLLBACK TRANSACTION <savepoint name>

Give an alternative to the following statement. Use a CASE Expression. SELECT IIF(region is null, country + ', ' + city, region) AS location FROM HR.Employees; Is IIF standard SQL?

SELECT CASE WHEN region is null THEN country + ', ' + city ELSE region END AS location FROM HR.Employees; No.

Can you give a similar statement, that returns the same result? Explain what happens. SELECT COALESCE(region, '') AS location FROM HR.Employees; Is COALESCE standard SQL?

SELECT ISNULL(region, '') AS location FROM HR.Employees; region will be selected as long it's not null. In the null case it is replaced with an empty string. Yes. ISNULL on the other hand only exists in T-SQL

Name the "select part" of a select statement that selects the one percent of data using the TOP filter.

SELECT TOP (1) PERCENT

Say you have the query *SELECT TOP(0) * FROM dbo.ErrorLog* How do you use this query with an exclusive table lock on dbo.ErrorLog?

SELECT TOP(0) * FROM dbo.ErrorLog WITH (TABLOCKX)

In what query clauses are window ranking functions allowed?

SELECT and ORDER BY.

What are the 2 possibilities to concatenate the attributes country and region, comma separated? What is the difference, regarding nulls?

SELECT country + ', ' + region AS location FROM HR.Employees SELECT CONCAT(country, ', ', region) FROM HR.Employees Null values are implicitly converted to an empty string.

Say you have the following statement: "SELECT country + ', ' + region AS location FROM HR.Employees". How can you treat nulls in the attribute region to turn into an empty string.

SELECT country + COALESCE(', ' + region, '') AS location FROM HR.Employees

Assume you have the numeric variable @newdiscount declared and you want to update the "discount" column to +0.05 and also set the variable in the same statement to +0.05. How would the "SET" part of the query look like?

SET @newdiscount = discount += 0.05

What happens if you don't specify a value for a column? What if the column is a not null?

SQL Server first checks if the column gets it's value automatically (f.e. IDENTITY or default constraint) then it checks if nulls are allowed and summons one if yes. If no, SQL Server generates an error.

What happens if you don't deliver a value for a column?

SQL Server uses the default expression.

What's the difference between temporary tables and table variables concerning statistics?

SQL server maintains mimal statistics on table variables, on temporary tables however it runs full blown statistics.

If you use a *windows aggregate function* what does the aggregate part of the query look like if f.e. you want to have the summ of the column "val" grouped by the "custid" column?

SUM(val) OVER(PARTITION BY custid)

In what form has a predicate to be to maximazie the efficiency of your queries?

Search argument

What are sequential GUIDs?

Sequential GUIDs within the machine. You can generate surrogate keys by using NEWSEQUENTIALID.

Whats the difference between set operators and "normal" operators like =, <, LIKE and so forth, with regard to comparison?

Set operators don't use equality-based comparison (when u compare to null, the query yields unknown), they use distinctness-based comparison (null = null yields true)

THROW doesn't support specifying severity. What is the default then?

Severity 16

Inputparameters of a UDF can be assigned with a default as in @p AS INT = 0. However what's the difference when invoking the function compared to a stored procedure?

You must specify the keyword DEFAULT.

What is a disadvantage of the THROW to the RAISERROR command?

THROW doesn't support WITH LOG or WITH NOWAIT

Give the correct order of the parameters when using THROW: message state errornumber

THROW errornumber, message, state;

What is the problem with the following part of a CATCH block? SELECT 'This is an error message' THROW;

THROW will not behave as intended. It is an alias here since the select is not terminated.

Which form of the THROW command can you also use outside a CATCH block?

THROW with parameters.

What are the two members of the recursive form of a CTE called?

The anchor member and the recursive member

In an UPDATE with OUTPUT statement how do you get the column with the state before and after the update?

The columns you want to know with the state before the update must be prefixed with deleted and the ones after the update with inserted.

What happens if you use the THROW command in a CATCH block?

The exception bubbles up the stack and activates the next CATCH block or treminates the execution if none is found.

What is the difference between inline table-valued functions and views?

The former accepts parameters.

What is the difference between the WHEN NOT MATCHED and the WHEN NOT MATCHED BY SOURCE clause?

The former defines what happens when a source row is not matched by a target row because a target row doesn't exist. Because it doesn't exist only a INSERT action is allowed. The latter defins what happens when a target row exists but isn't matched by the source row. Because the target row exists only UPDATE or DELETE is allowed.

What are the restrictions to use a UDF in a persisted computed column or in a indexed view?

The function needs to be deterministic and must be defined with the SCHEMABINDING attribute.

What is the prerequisite that a MERGE join reqires?

The input on both sides must be sorted on the merge columns.

Why does a manipulation to the filtered column like WHERE YEAR(orderdate) = 2015 make the a filter not sargable?

The optimizer can't relay on the index order anymore and has to do a full scan of the index instead of a scan.

What is crucial that it's possible to use a query on one side in the where clause?

The query mus return a single value.

What returns the follogwing statement: SELECT orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC OFFSET 50 ROWS FETCH NEXT 25 ROWS; Explain!

The query will fail because the ONLY keyword is missing.

What returns the follogwing statement: SELECT TOP 100 orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY; Explain!

The query will fail because you can't have TOP and OFFSET in the same query. Both have similar purposes, so choos one of them!

What returns the follogwing statement: SELECT TOP 100 orderid, orderdate, custid, empid FROM Sales.Orders OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY; Explain!

The query will fail. In T-SQL an ORDER BY is required when using OFFSET.

What can you basically say concerning read performance when choosing a certain datatype?

The smaller the better.

What does SELECT INTO? What exactly does it copy and what not? Make a pseudo code example.

The statement creates a table based on the definition of the source table of the query. It copies all the data definition like the column names, identity property and so on. However it doesn't copy constrains (like the PRIMARY KEY) from the source table. SELECT <column names> INTO <new table name> FROM <table> WHERE <predicate>;

Joins treats its input as a set. What does that mean for the ordering?

There is no ordering.

What happens with the remainder rows of the NTILE(n) function?

They are added to the first couple of rows till the reminder is exhausted.

How do set functions generally treat NULLs?

They are ignored.

If you sort by a column that contins NULLs, how are these treated by the SQL Server? And is this behavior standard SQL?

They are sorted before non-NULLs. SQL defines that NULLs are sorted together it doesn't define where to put them though.

What problem can you run into when updating data based on joins?

This kind of update can be non deterministic when multiple source rows match one target row. SQL Server doesn't generate an error or even a warning. It just arbitrarly takes one row.

What is the ON clause used for in a MERGE statement?

To match rows between the source and the target table. Note that it's not a filter like in a JOIN Statement.

If UNION and UNION ALL return the same results and there's no potential for duplicates, which clause should you choose and why.

UNION ALL. With UNION SQL Server might try to distinct the result, what causes a distinct sort, which is very expensive from the performance standpoint.

What type of attribute is nessecair to save a Nonsequential GUID in it? And how many space of storage does it need?

UNIQUEIDENTIFIER 16 Bytes

You have a table "MyTable" with a column "discount" and a cursor "MyCursor". How do you update the discount to plus 5 percent with the help of the curser? A query is expected.

UPDATE MyTable SET disount += 0.05 WHERE CURRENT OF MyCursor;

Why don't you have the same problem with RAISERROR as with THROW when not terminating the previous statement with a ;

Unlike THROW is RAISERROR not a reserved keyword

How do you prevent two processes to insert a row in a table at the same time.

Use WITH (SERIALIZABLE)

How can you make the ordering of a query result unique when querying f.e. TOP (3), ordered by orderdate and you have multiple orders from the same date and you use "WITH TIES" to make the result deterministic? Explain.

Use a tie breaker. This is you order also by an other column.

If you want a stored procedure that gives back values, how do you do that?

Use the OUTPUT keyword in the parameterdefinition.

In a MERGE statement when you want to prevent (in the WHEN MATCHED AND clause) unnessecair updates what would be a good method to check inequality and handle null values in the mean time using SET OPERATORS? Use pseudocode.

WHEN MATCHED AND EXISTS(SELECT <sourcetable.*> EXCEPT SELECT <targettable.*>) THEN UPDATE.

How does a CTE's (common table expression) structure look like? use the following parts: <inner_query>, <outer_query>, <CTE_name> and the necessairy keywords.

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

How is it indicated that a cursor reached it's end?

When @@FETCH_STATUS = 0 isn't true anymore

What is ownership chaining?

When a user is granted a right f.e. EXECUTE on a stored procedure and this procedure works with objects that the user has no execution right, he still can execute the procedure.

What is the XACT_ABORT option for?

When it's on, every error causes an open transaction to roll back and abort the execution of the code. Default is, that it's off and some errors get trough anyway and others cause a rollback.

How can an predicate evaluate to unknown?

When the predicate evaluates columns or values that can have null entries.

What would you have to change that this query will succeed and still bring back what it intended? SELECT TOP 100 orderid, orderdate, custid, empid FROM Sales.Orders ORDER BY orderdate DESC, orderid DESC FETCH NEXT 25 ROWS ONLY;

When using FETCH in T-SQL you need OFFSET as well. If you don't want to skip any rows you have to use OFFSET 0.

When is it recommendet to use temporary tables and when rather table expressions?

When you only have to reat a result out of a temporary table once you don't want to pay the penalty of creating that table. You use an expression. But if you use it several times, say in multiple joins, you better pre save the results.

What's wrong about prefixing columns with "inserted" in an DELETE with OUTPUT statement?

You must prefix them with "deleted" since in a DELETE statement you cannot have inserted rows.

How do you insert the result set from a stored procedure into a table? Make a pseudo code example.

With INSERT EXECUTE. INSERT INTO <table>(column names) EXEC <stored procedure> <procedure parameters>

How do you insert values gathered from a select statement? Make a pseudo code example.

With INSERT SELECT. INSERT INTO <table>(<columns>)SELECT<columns>FROM<table>WHERE <predicate>

What are nonsequential GUIDs? Give an example how to generate one.

With NEWID you can generate nonsequential global unique identifiers that are stored in an UNIQUEIDENTIFIER type. Unique across space and time. You can use it to generate surrogate keys. DECLARE @newid as UNIQUEIDENTIFIER = NEWID() SELECT @newid

How are the two members of a recursive CTE usually combined?

With a UNION.

What is a big advantage of fixed types vs. dynamic ones?

With fixed types there is no data shifting involved and therefore it's faster.

What's generally the limitation of corelated subqueries compared to queries using the APPLY operator?

With subqueries your limited to returning only one column, whereas with an applied table expression you can return a whole result set with multiple columns and rows.

What is CONTEXT_INFO?

With the SET CONTEXT_INFO command you can store one global variable. You can read it out be using the CONTEXT_INFO() command.

In what range is a sequential GUID unique and how do you generate it?

Within the machine and you generate it with the function NEWSEQUENTIALID

Name three abbreviations of DATEPART!

YEAR MONTH DAY

Give a yes or no to following questions. Can a stored procedure.. ...have input and output parameters? ...do errorhandling? ...be embedded in queries? ...modify data? ...apply changes to database objects?

Yes Yes No Yes Yes

Is it possible to modify data through a inline table-valued function?

Yes.

Does it matter if I specify my predicate in the ON or WHERE clause in an OUTER JOIN? What are the roles of these clauses?

Yes. WHERE is still a filter. ON only determines which rows from the nonpreserved side get mached to rows from the preserved side. From the preserved side it takes all rows In other words, ON is a matching predicate and WHERE is a filter predicate.

Would the following query succeed? If not, explain. What is an other name for this join? SELECT S.companyname AS supplier, S.country, P.productid, P.productname, P.unitprice FROM Production.Suppliers AS S JOIN Production.Products AS P ON S.supplierid = P.supplierid WHERE S.country = N'Japan';

Yes. INNER JOIN.

Will the following query perform properly? Explain. SELECT country, region, city FROM HR.Employees UNION SELECT country, region, city FROM Sales.Customers ORDER BY country;

Yes. Since UNION is a set operator you cannot have an ORDER BY in the individual queries. You can have one at the end though, for presentation reasons of the reult.

Is there a way around the problem, that you cannot invoke a nondeterministic built-in function with side effects in a UDF?

Yes. You could place that function in a VIEW and query from that VIEW in your function.

What do you do if you have a view with an expensive underlying query and you want to persist it's results?

You can create a clustered index on the view and this way you persist the view's result within the clustered index B-tree.

What's the point of GROUPING SETS and where in the query comes the keyword?

You can instead of just GROUP BY you can group by different set's of "columns"/criterias. The keyword comes right after the GROUP BY clause.

What's the MERGE statement meant for?

You can merge data from a source table into a target table

What could you do to make sure that if you commit a transaction in your stored procedure, that no outer query can rollback it?

You could use a loop where you commit while @@ROWCOUNT > 0. It's considered bad practice though.

What is a big advantage from CTE's compared to derived tables?

You don't have to nest tables, you can define them one after another and relay on the previous.

What happens if you have a missmatching number of BEGIN and COMMIT statements?

You get a Msg 266 Error. The error doesn't stop the execution though even if you set XACT_ABORT

What result do you get, when you use a error function outside a CATCH block?

You get back null.

What is important if you use RAISERROR with severity level 19 and up?

You have to add the WITH LOG option.

When you have a correlated subquery that queries the same table as the outer query, what do you have to do that this won't get you an error?

You have to alias the two tables.

To use the WITH LOG command what do you have to know about the permission

You have to be a member of the sysadmin role or have an ALTER TRACE permission.

What is nessecairy to do if you want to change the structure of a table but it is bounded to a view via SCHEMABINDING?

You have to drop and recreate the bindet object first.

If you want to provide your own value for the primary key instead of letting the identity property do it for you when you have specified one. What can you do?

You need to turn on a session option called IDENTITY_INSERT. SET IDENTITY_INSERT <table> ON; (remember to turn it off)

How do you get the last identity value that was generated in the same session and scope?

You query the SCOPE_IDENTITY function.

How do you get the last identity value that was generated irrespective of scope?

You query the system function @@IDENTITY.

What happens if you try to invoke a nondeterministic built-in function with side effects, in a UDF (user defined function)?

You would get an error at creation time of the function that this is not allowed.

What is the problem with the invocation of the following scalar UDF? SELECT SubtrrTotalSalaries(8) as subtreetotal;

You'll get an error since this is not a systemfunction and no scema is specified. Correct would be SELECT dbo.SubtrrTotalSalaries(8) as subtreetotal

What returns SELECT LEFT('abcdefg', 3);?

abc

What returns "SELECT SUBSTRING('abcdefg', 2, 8);"? Does the function fail?

bcdefg. No. Even if the third parameter is to big, it just extracts the string till the end of the given string.

Give an example for a predicate in the form of a "column operator value". Can that be a "search argument"?

col1 = 10 or col1 > 10 Yes.

What's the value of col2 after the following UPDATE? col1 is 100. col2 is 0. DECLARE @add AS INT = 10; UPDATE dbo.T1 SET col1 += @add, col2 = col1 WHERE keycol = 1; SELECT * FROM dbo.T1;

col2 = 100, col1 = 110

What returns SELECT RIGHT('abcdefg', 3);?

efg

If the WHERE clause is used for filtering at the row level at what level is the HAVING clause used?

group level

What are the results of the following statements? SELECT LOWER('HUGA'); SELECT LTRIM(' HUGA '); SELECT RTRIM(' HUGA '); SELECT FORMAT(1759, '000000000')

huga HUGA (with trailing spaces) HUGA (with leading spaces) 000001759

Fill in the gap: The ON clause ordering is what defines the _______ ordering.

logical join

What's the following grouped query called: A query that invokes a group aggregate function without a GROUP BY?

scalar aggregate. F.e. Select COUNT(*) ...

How is the system procedure called with that you execute dynamic sql?

sys.sp_executesql

To what kind of sql objects belongs a VIEW

table expression

How is that logic called when the predicate evaluates to true, false or unknown?

three-valued logic

How is that logic called when the predicate evaluates only to true or false?

two-valued logic

What returns the following: SELECT CHOOSE(2, 'x', 'y', 'z'); ? Is it standard SQL?

y. No.