70-461 Querying

If pages are physically out of order; can slow down full or partial scans in logical order.

external fragmentation

Other indexes, such as indexes that do not contain all of the data and serve as pointers to table rows for quick seeks

nonclustered

What is the negation of true, false and unknown?

not true is false not false is true not unknown is still unknown.

What is the System procedure for creating plan guides

sys.sp_create_plan_guide

What is the Sys proc for creating a Plan Guide from a cached query plan

sys.sp_create_plan_guide_from_handle

System procedure to manually update statistics for all tables in a database.

sys.sp_updatestats

What are two variations of syntax to create a CROSS JOIN?

FROM T1 CROSS JOIN T2 or FROM T1, T2.

What T-SQL functions can be used to apply ALTERATIONS to an input string?

REPLACE, REPLICATE, and STUFF.

SQL Server error messages are listed in sys.________.

SQL Server error messages are listed in sys.messages.

SQL Server error messages are numbered from ______ through _____.

SQL Server error messages are numbered from 1 through 49999.

When SQL Server is executing a query, what happens in the Optimization stage?

SQL Server generates candidate execution plans and evaluates them. It selects a good enough plan for the next phase.

How does SQL Server maintain transactional durability?

SQL Server maintains transactional durability by using the database transaction log. Every database change (data modification statement or DDL statement) is first written to the transaction log, with the original version of the data (in the case of updates and deletes). When a transaction is committed and all consistency checks pass, the fact that the transaction has been successfully committed is written to the transaction log.

Hint that specifies that the acquired lock is applied at the table level, as opposed to row or page level.

TABLOCK

Plan guides are used by the Query Optimizer to match stand-alone queries that can be parameterized (force parameterization)

TEMPLATE plan guides

What type of data does an inline function return?

Tables. Inline functions return tables and are often referred to as inline table-valued functions.

What's the difference between an xml document and an xml fragment?

a document has a single root node, like <CustomersOrders>

When a query doesn't have an ORDER BY clause, what is the order in which the rows are returned? a. Arbitrary order b. Primary key order c. Clustered index order d. Insertion order

a. Without an ORDER BY clause, ordering isn't guaranteed and is said to be arbitraryit's optimization-dependent.

Means that there's only one sensible plan and the optimizer doesn't need histograms to come up with this conclusion

trivial plan

Command to get detailed information about statistics

DBCC SHOW_ STATISTICS

What is DML?

Data Manipulation Language.

Why is it important for SQL Server to maintain the ACID quality of transactions?

To ensure that the integrity of database data will not be compromised.

Prefix used for Local Temporary Tables

# (hashtag)

Prefix used for Global Temporary Tables

## (double hash)

Which of the following literals are language-dependent for the DATETIME data type? '2012-02-12' '02/12/2012' '12/02/2012' '20120212'

'2012-02-12' is language-neutral for the data types DATE, DATETIME2, and DATETIMEOFFSET, but language-dependent for DATETIME and SMALLDATETIME. '02/12/2012' is language-dependent. '12/02/2012' is language-dependent. '20120212' is language-neutral.

The wildcard '[0-9]%' means...

'[0-9]%': string where first character is a digit. '[^0-9]%': string where first character is not a digit

How can you disable the ability to return a result set from a trigger?

**EXAM TIP You can disable it with the sp_configure option called Disallow Results From Triggers. It is not a good practice to return result sets from triggers. In SQL Server 2012 and earlier versions, returning a rowset from a trigger is allowed, but it cannot be relied on. The ability to return result sets from a trigger is deprecated and will be dropped in the next version of SQL Server after SQL Server 2012.

How can you use @@ROWCOUNT to improve the performance of a trigger?

**EXAM TIP You can improve the performance of the trigger by testing whether @@ROWCOUNT is 0 in the very first line of the trigger. When no rows are affected, there is no point in proceeding with the trigger. It must be the first line because @@ROWCOUNT will be set back to 0 by any additional statement. When the AFTER trigger begins, @@ROWCOUNT will contain the number of rows affected by the outer INSERT, UPDATE, or DELETE statement.

What are characteristics of "Iterative" logic?

- use cursors or other iterative constructs to handle one row at a time -Your focus is on the individual rows, not the whole -you process one row at a time and you can do so in a specifc order

What are the drawback of iterative solutions in SQL?

-Bad performance, loops in T-SQL are much slower than those in other programming languages such as in Microsoft .NET code, each record fetch from a cursor by using the FETCH NEXT command has quite a high overhead associated with it

What are characteristics of "Set-Based" logic?

-based on principles from the relational model -a set should be considered as a whole. This means that your attention should be focused on the set and not on its individual elements - a set has no particular order to its elements. So when you use set-based solutions, you cannot make any assumptions about the order of the data, unless you add an ORDER BY clause to the query, youre not guaranteed that the data will be returned in any particular order

What are the possible values of @@FETCH_STATUS in a cursor?

0 when the previous fetch was successful, -1 when the row is beyond the result set, -2 when the row fetched is missing.

What are the three XACT_STATE() values?

1 An open transaction exists that can be either committed or rolled back. 0 There is no open transaction; it is equivalent to @@TRANCOUNT = 0. -1 An open transaction exists, but it is not in a committable state. The transaction can only be rolled back.

When defining the IDENTITY property, what are the default seed and increment values?

1 and 1 **You can define it for columns with any numeric type that has a scale of 0. This means all integer types, but also NUMERIC/DECIMAL with a scale of 0.

How many bytes of storage does TINYINT use?

1 byte

What are the two levels of table compression?

1) Row - SQL Server applies a more compact storage format to each row of a table. 2) Page - includes row-level plus additional compression algorithms that can be performed at the page level.

When using the ROWS window frame unit, what are the three options for indicating delimiters?

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

What are two methods of allowing a default constraint to populate a column? (upon inserting data)

1) omit the column name/value from the INSERT VALUES statement 2) set the value to DEFAULT in the INSERT VALUES statement

Which five steps must you perform to pass a table-valued variable to a user-defined stored procedure?

1. Create a user-defined data type. 2. Create the stored procedure that accepts a table parameter. 3. Declare a variable of the user-defined data type. 4. Initialize the variable. 5. Pass the variable as an input parameter to the stored procedure.

After you deploy a line-of-business (LOB) application to your customer, you realize it is not user friendly enough. End users have to perform many searches; however, they always have to know the exact phrase they are searching for. 1. How could you enhance the end users' experience? 2. How should you change your queries to support the enhanced user interface?

1. Use the Full-Text Search feature of SQL Server. 2. Revise the queries to include the full-text predicates, or use the full-text and semantic search table-valued functions.

You can have up to X columns in a key

16

What are the size limitations of primary key and unique constraints?

16 columns 900 bytes Both primary key and unique constraints have the same size limitations as an index: you can combine no more than 16 columns as the key columns of the index, and there is a maximum combined width of 900 bytes of data across those columns.

What's the easiest way to create XML schemas?

1st Create a realtional table 2nd Use the XML schema option 3rd Store the XML schema in a variable 4th Provide variable as input to the CREATE XML SCHEMA COLLECTION 5th Drop temp tables

A statistic can have maximally X steps.

200

What is the maximum amount of steps that can occur in a statistics histogram?

200

The error message can be up to ___ Unicode characters long.

255

How many bytes of storage does DATE use?

3 bytes

Which of the following is not a part of an Extended Events package? a. Predicates B. Targets c. Sources D. Actions

3. correct answer: c a. incorrect: Predicates are objects in an Extended Events package. B. incorrect: Targets are objects in an Extended Events package. c. correct: Sources are not a part of an Extended Events package. D. incorrect: Actions are objects in an Extended Events package.

What is the maximum depth of trigger executions?

32

Amount of space a single IAM page can point to

4 GB

How many bytes of storage does INT use?

4 bytes

How many bytes of storage does REAL use?

4 bytes

How many bytes of storage does DATETIME use?

8 bytes

How many bytes of storage does FLOAT use?

8 bytes

SQL Server stores data on pages. How big is a page?

8,192 bytes, or 8 KB. A page belongs to a single object only, such as a single table, index, or indexed view.

The size of all columns in a composite key can't exceed this

900 bytes

You can have up to ___ nonclustered indexes on a table

999

How do you write a comment in XML?

<!-- What you want to say -->

What are the three supported forms of aliasing in a SELECT clause?

<expression> AS <alias>, as in empid AS employeeid, <expression> <alias>, as in empid employeeid, <alias> = <expression>, as in employeeid= empid

What are the two supported forms of aliasing in a FROM clause?

<table> <alias>, as in HR.Employees E, or <table> AS <alias>, as in HR.Employees AS E

All parameters and local variables in a procedure, function, or trigger must begin with which character?

@

Prefix used to declare table variables

@

Which function can be used to rank documents based on proximity of words? A. CONTAINSTABLE() B. FREETEXTTABLE() C. SEMANTICKEYPHRASETABLE() D. SEMANTICSIMILARITYTABLE() E. SEMANTICSIMILARITYDETAILSTABLE()

A A. Correct: Use the CONTAINSTABLE function to rank documents based on proximity of words. B. Incorrect: Use the FREETEXTTABLE function to rank documents based on containment of words. C. Incorrect: Use the SEMANTICKEYPHRASETABLE function to return key phrases associated with the full-text indexed column. D. Incorrect: Use the SEMANTICSIMILARITYTABLE function to retrieve documents scored by similarity to a specified document. E. Incorrect: Use the SEMANTICSIMILARITYDETAILSTABLE function to return key phrases that are common across two documents.

What is the simplest type of join?

A CROSS JOIN.

When using a check constraint, is a NULL value considered > or < or = to 0 (zero) ?

A NULL is not negative, but it is also not positive. An insert of a NULL passes the constraint unitprice >= 0 and it also passes the constraint unitprice < 0.

What statement involves a query and a target table?

A SELECT INTO statement. The statement creates the target table based on the definition of the source and inserts the result rows from the query into that table. The statement copies from the source some aspects of the data definition like the column names, types, nullability, and IDENTITY property, in addition to the data itself. Other aspects of the data definition like indexes, constraints, triggers, permissions, and others aren't copied.

Which SET session options are useful for query optimization? (Choose all that apply.) A. SET STATISTICS IO B. SET STATISTICS EXECUTION_DETAILS C. SET IDENTITY_INSERT D. SET STATISTICS TIME

A and D A. Correct: The SET STATISTICS IO session option is useful for analyzing query performance. B. Incorrect: There is no SET STATISTICS EXECUTION_DETAILS option. C. Incorrect: You use the SET IDENTITY_INSERT option to provide a value for the column that has an identity property. D. Correct: The SET STATISTICS TIME session option is useful for analyzing query performance.

Which of the following are true about synonyms? (Choose all that apply.) A. Synonyms do not store T-SQL code or data. B. Synonyms do not require schema names. C. Synonym names can match those of the objects they refer to. D. Synonyms can reference objects in other databases or through linked servers.

A and D A. Correct: Synonyms are just names, and do not store T-SQL code or any data. B. Incorrect: Synonyms are database objects that are scoped to database schemas, just like tables, views, functions, and stored procedures, so they require schema names. C. Incorrect: A synonym name (schema name plus object name) cannot be the same as any other schema-scoped database object, including other synonyms. D. Correct: Synonyms can reference other database objects using three-part names, and objects through linked servers using four-part names.

What can you use to constrain the values of a column?

A check constraint. The values are already constrained by the data type, so a check constraint adds some additional constraints on the ranges, or set of allowable values.

What is the natural key or business key of the table?

A column (or combination of columns) within the data of a table that uniquely identifies every row (such as the category name in the TSQL2012 Production.Categories table).

What is the difference between a database schema and a table schema?

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

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

A database schema.

What results from mutual blocking between TWO or more sessions?

A deadlock.

What is a tuple?

A finite ordered list of elements. ... a row.

Define a data analysis function.

A function applied to a set of rows that returns a single value. An example of such a function is data analysis operations. A data analysis function is a function the SUM aggregate function.

What type of programming object cannot call a stored procedure?

A function or view.

What is a predicate?

A logical expression that when attributed to some object, makes a proposition either true or false. For example, "salary greater than $50,000" is a predicate.

What is the purpose of a node test?

A node test follows the axis you specify. There are different kinds like name and type test. The purpose is to validate.

What type of view can an inline function simulate?

A parameterized view that is, a view that takes parameters.

What is a pro-log?

A prolog is at the beginning of an XML document and it denotes the XML version and encoding of the document. Example: <?xml version="2.0" encoding=" ISO-1111-11">

What type of UDF returns only a single value?

A scalar UDF returns only a single value.

What is the form of a filter predicate that can rely on index ordering called?

A search argument, or SARG, for short.

What is a SARG?

A search argument.

What is used to define searchable properties for documents?

A search property list.

What is a grouping set?

A set of attributes used to group data.

The wildcard [<character range>] means...

A single character from the specified range. '[0-9]%': string where first character is a digit

The wildcard _ (underscore) means...

A single character. '_D%': string where second character is D

What is a surrogate key?

A special column with a numeric data type (such as integer), which will have a unique but otherwise meaningless value, created by database designers.

What two object types can be used to return certain columns from a table based on the primary key column of that table using parameters?

A stored procedure or table-valued user defined function.

Which database object would you create to accept input parameters and execute Transact-SQL code without returning a value?

A stored procedure.

Define transaction.

A transaction is a logical unit of work. Either all the work completes as a whole unit, or none of it does.

When working with transactions, what are Cross-database transactions?

A transaction may span two or more databases on a single SQL Server instance without any additional work on the user's part. SQL Server preserves the ACID properties of cross-database transactions without any additional considerations. However, there are limitations on database mirroring when using cross-database transactions. A cross-database transaction may not be preserved after a failover of one of the databases.

Views should be self-documenting. What best practice helps with this?

A view will be more self-documenting if the column names of the view are specified in the SELECT statement and not listed separately in the view. You can specify the set of output columns following the view name. BUT it makes it more difficult to see what the column names of the view are when reading the SELECT statement.

Which of the following functions can be used in a CATCH block to return information about the error? (Choose all that apply.) A. @@ERROR B. ERROR_NUMBER() C. ERROR_MESSAGE() D. XACT_STATE()

A, B, C, and D A. Correct: The value of @@ERROR changes with each successful command, so if it is accessed in the very first statement of the CATCH block, you can get the original error message. B. Correct: ERROR_NUMBER() returns the error number of the original error that led to control being passed to the CATCH block. C. Correct: ERROR_MESSAGE() returns the text of the original error. D. Correct: XACT_STATE() tells you the state of the transaction in a CATCH block, in particular whether the transaction is committable.

Which of the following are T-SQL regular identifiers? (Choose all that apply.) A. categoryname B. category name C. category$name D. category_name

A, C, and D A regular identifier can consist of all alphabetic characters. A regular identifier cannot contain a space. A regular identifier may contain a dollar sign ($). A regular identifier may contain an underscore (_).

What metadata tables give you a list of constraints in a database? (Choose all that apply.) A. sys.key_constraints B. sys.indexes C. sys.default_constraints D. sys.foreign_keys

A, C, and D sys.key_constraints lists all primary key and unique constraints in a database. sys.default_constraints lists the default constraints in a database. sys.foreign_keys lists all the foreign keys in a database.

Which of the following is only possible when using the MERGE statement in regard to the OUTPUT clause? A. Referring to columns from the source table B. Referring to both the keywords deleted and inserted C. Assigning aliases to output columns D. Using composable DML

A. Only in a MERGE statement's OUTPUT clause can you refer to elements from the source table.

Which T-SQL statement should you use if you need to change the source code of your stored procedure without first dropping it?

ALTER PROCEDURE.

Write code to add a foreign key constraint to enforce that every entry into the categoryid column of the Production.Products table is a valid categoryid from the Production.Categories table.

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

What command is used to set a table's compression?

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

When this option is set to on, SQL Server creates statistics automatically.

AUTO_CREATE_STATISTICS

What are the mandatory clauses in a SELECT query, according to T-SQL?

According to T-SQL, the only mandatory clause is the SELECT clause. e.g. SELECT @@VERSION

Is there any harm in specifying ORDER BY even when the data doesn't need to be returned ordered?

Adding an ORDER BY clause means that SQL Server needs to guarantee returning the rows in the requested order. If there are no existing indexes to support the ordering requirements, SQL Server will have to sort the data. Sorting is expensive with large sets. The general recommendation is to avoid adding ORDER BY clauses to queries when there are no ordering requirements. When you do need to return the rows in a particular order, consider arranging supporting indexes that can prevent SQL Server from needing to perform expensive sort operations.

List some advantages/disadvantages of using a SELECT INTO statement.

Advantages: When the database's recovery model is set to either simple or bulk logged (not full), the statement uses minimal logging which can result in a faster insert compared to when full logging is used. Disadvantages: You have limited control over the definition of the target table. Because SELECT INTO involves both creating a table and populating it with data, both the metadata related to the target table and the data are exclusively locked until the SELECT INTO transaction finishes. As a result, you can run into blocking situations due to conflicts related to both data and metadata access.

Name three types of window functions.

Aggregate Ranking Offset.

Which of the following T-SQL statements allow the use of synonyms? EXECUTE for stored procedures ALTER DELETE INSERT SELECT UPDATE

All except ALTER. You can modify data through a synonym, but you cannot alter the underlying object.

Which table rows are updated in an UPDATE statement without a WHERE clause?

All table rows.

If the sets you're unifying are disjoint and there's no potential for duplicates, is there a difference between using UNION or UNION ALL?

Although UNION and UNION ALL will return the same result, use UNION ALL because with UNION, SQL Server may try to eliminate duplicates, incurring unnecessary cost.

If an AFTER trigger discovers an error, how does it prevent the DML command from completing?

An AFTER trigger can issue a THROW or RAISERROR command to cause the transaction of the DML command to roll back. Also a trigger can issue ROLLBACK TRAN but this can have some unwanted side effects.

This trigger fires after the event it is associated with finishes and can only be defined on permanent tables.

An AFTER trigger.

This trigger fires instead of the event it is associated with and can be defined on permanent tables and views.

An INSTEAD OF trigger.

In a query what must be done in order to refer to columns that don't appear in the GROUP BY list?

An aggregate function must be applied to them. This guarantees the expression will return only one value per group.

What is cardnality?

An estimate of the number of rows that exists for a given column in the predicate of the WHERE and JOIN clause

What occurs when you explicitly issue the BEGIN TRANSACTION or BEGIN TRAN command to start a transaction?

An explicit transaction.

Is CASE a statement or an expression?

An expression. A statement performs some kind of an action or controls the flow of the code. CASE returns a value, and hence is an expression.

What is an irregular identifier?

An identifier that does not follow the rules for formatting identifiers; for example, it starts with a digit, has an embedded space, or is a reserved T-SQL keyword.

What can you use to simulate passing a parameter to a view?

An inline table-valued function.

What is the only type of UDF that can be written without a BEGIN/END block?

An inline table-valued function.

When working with nested transactions, what does an inner COMMIT do?

An inner COMMIT statement has no real effect on the transaction, only decrementing @@TRANCOUNT by 1. Just the outermost COMMIT statement, the one executed when @@TRANCOUNT = 1, actually commits the transaction.

List a couple of disadvantages of using implicit transactions.

Any locks taken out by your command are held until you complete the transaction. Therefore, you could end up blocking other users from doing their work. Because this is not the standard method of using SQL Server, you must constantly remember to set it for your session. The implicit transaction mode does not work well with explicit transactions because it causes the @@TRANCOUNT value to increment to 2 unexpectedly. If you forget to commit an implicit transaction, you may leave locks open.

The wildcard % (percent sign) means ...

Any string including an empty one. 'D%': string starting with D

Can anything be done to improve the way filtering is handled?

As much filtering as possible should be done in the database. Doing filtering in the client results in scanning more data, which increases the stress on the storage subsystem, and unnecessary network traffic. When you filter in the databases, for example by using the WHERE clause, use search arguments that increase the likelihood for efficient use of indexes. Try to avoid manipulating the filtered columns.

When analyzing queries, you can get both the estimated and the actual plan in what three forms?

As text, as XML, or graphically.

What are three modes for user transactions in SQL Server that is, three ways of working with transactions?

Autocommit, Implicit transaction, Explicit transaction.

What are three modes for user transactions in SQL Serverthat is, three ways of working with transactions?

Autocommit, Implicit transaction, Explicit transaction.

Which data type should be used in place of TIMESTAMP?

B. ROWVERSION is the replacement for the deprecated TIMESTAMP.

What is the implication of the 'all at once' on UPDATEs?

Based on this property, all assignments use the original values of the row as the source values, irrespective of their order of appearance.

Why does querying the @@ERROR function, even in an IF clause, cause it to be reset to a new number?

Because @@ERROR always reports the error status of the command last executed.

Which of these two can have gaps between result ranking values? RANK and DENSE_RANK

Because RANK considers rows, it can have gaps between result ranking values. Because DENSE_RANK considers distinct values, it cannot have gaps.

Why must constraints have unique names across the database?

Because all table constraints are database objects, just like tables, views, stored procedures, functions, and so on.

What is the main way to help queries with TOP and OFFSET-FETCH perform well?

By arranging indexes to support the ordering elements. This can prevent scanning all data, in addition to sorting.

How can you prevent indexing words/phrases that occur frequently but don't help with searches?

By creating stoplists of stopwords.

How do you guarantee deterministic results with TOP?

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

How does SQL Server implement transaction durability?

By first writing all changes to the database transaction log before making changes to the database data.

How can a hacker detect that SQL injection may be possible?

By inserting a single quotation mark and observing an error message. The tell-tale message is "Unclosed quotation mark after the character string."

How can a hacker detect that SQL injection may be possible?

By inserting a single quotation mark and observing an error message. The telltale message is Unclosed quotation mark after the character string.

How can you view the names of transactions?

By inspecting the name column of the dynamic management view (DMV) sys.dm_tran_active_transactions. The default name for user transactions is user_transaction but you can assign your own name to a transaction by using explicit transactions.

How do you modify a column value in a target row and collect the result of the modification in one visit to the row? A .By using an UPDATE based on a join B. By using an UPDATE based on a table expression C. By using an UPDATE with a variable D .The task cannot be achieved with only one visit to the row.

C

A stored procedure calls another stored procedure. The calling stored procedure has created temporary tables, declared variables, and passes parameters to the called stored procedure. What data can the called stored procedure see from the caller? A. The called procedure can see the variables, temporary tables, and passed parameters of the caller. B. The called procedure can see the temporary tables but not the variables and passed parameters of the caller. C. The called procedure can see the passed parameters and temporary tables but not the variables of the caller. D. The called procedure cannot see any objects created by the calling procedure.

C. The called procedure can see temporary tables and parameters passed to it from the calling procedure.

Which of the following is not a part of an Extended Events package? A. Predicates B. Targets C. Sources D. Actions

C. Sources

finish using a cursor

CLOSE cursor

What does SET STATISTICS TIME ON show?

CPU time and elapsed time.

What is the syntax for creating a full-text catalog?

CREATE FULLTEXT CATALOG catalog_name [ON FILEGROUP filegroup ] [IN PATH 'rootpath'] [WITH <catalog_option>] [AS DEFAULT] [AUTHORIZATION owner_name ] <catalog_option>::= ACCENT_SENSITIVITY = {ON|OFF} ON FILEGROUP and IN PATH are for backward-compatibility and should be avoided ACCENT_SENSITIVITY indicates accent sensitive indexes

What is the basic syntax for creating a synonym?

CREATE SYNONYM schema_name.synonym_name FOR object_name Example: CREATE SYNONYM dbo.Categories FOR Production.Categories;

Create a Local Temporary Table, T1

CREATE TABLE #T1 ();

As a database developer on a key project for your company, you have been asked to refactor a set of stored procedures in your production database server. You have observed that the stored procedures have practically no error handling, and when they do have it, it is ad hoc and unstructured. None of the stored procedures are using transactions. What plans should you include for refactoring dynamic SQL?

Check the stored procedures for the use of dynamic SQL, and where possible, replace calls to the EXECUTE command with the sp_executesql stored procedure.

In order to perform full-text queries on them, what three data type columns require an additional type column in which you store the file extension (such as .docx, .pdf, or .xlsx) of the document in each row?

Columns of data type VARBINARY(MAX), IMAGE, or XML.

What are computed columns?

Columns whose values are computed based on expressions. These expressions could be based on the value of other columns in the row or based on T-SQL functions. You can make the computed column persisted, meaning that SQL Server will store the computed values with the table's data, and not compute the values on the fly, so long as the column does not make use of any non-deterministic functions (ie GETDATE() or CURRENT_TIMESTAMP).

How are multi-join tables evaluated by T-SQL?

Conceptually from left to right. So the result of one join is used as the left input to the next join.

A database has a table holding about 100 million rows. About a third of the existing rows need to be updated. Can you provide recommendations as to how to handle the update in order not to cause unnecessary performance problems in the system?

Consider developing a process that handles the large update in chunks. If done in one big transaction, the process will very likely result in a significant increase in the transaction log size. The process will also likely result in lock escalation leading to blocking problems.

What is shredding XML to a table?

Converting an XML document into a relational table To shred means to strip the actual data away from the markup tags, and organize it into a relational format.

What command would you use to clear the plan cache?

DBCC FREEPROCCACHE;

What is the purpose of the GROUPING and GROUPING_ID functions? (Choose all that apply.) A. You can use these functions in the GROUP BY clause to group data. B. You can use these functions to tell whether a NULL in the result represents a placeholder for an element that is not part of the grouping set or an original NULL from the table. C. You can use these functions to uniquely identify the grouping set that the result row is associated with. D. These functions can be used to sort data based on grouping set associationthat is, first detail, and then aggregates.

Correct Answers: B, C, and D A. Incorrect: These functions cannot be used in the GROUP BY clause. B. Correct: When the functions return a 1 bit, a NULL is a placeholder; when they return a 0 bit, the NULL originates from the table. C. Correct: Each grouping set can be identified with a unique combination of 1s and 0s returned by these functions. D. Correct: These functions can be used for sorting because they return a 0 bit for a detail element and a 1 bit for an aggregated element. So if you want to see detail first, sort by the result of the function in ascending order.

Because joins often occur on foreign keys, what is something that can help query performance?

Create a nonclustered index on the foreign key in the referencing table. There is already a unique index on the corresponding column in the referenced table, but if the referencing table, like Production.Products, has a lot of rows, it may help SQL Server resolve the join faster if it can use an index on the big table.

Custom error messages are numbered _____ and higher.

Custom error messages are numbered 50001 and higher.

How can you express that the column categoryname allow NULLs? A. categoryname PERMIT NULL NVARCHAR(15) B. categoryname NVARCHAR(15) ALLOW NULL C. categoryname NVARCHAR(15) PERMIT NULL D. categoryname NVARCHAR(15) NULL

D. Specify NULL right after the data type.

Which of the following are preferred data types? DECIMAL FLOAT NUMERIC REAL

DECIMAL and NUMERIC are the same data type, but generally people prefer DECIMAL because the name is a bit more descriptive. Use DECIMAL and NUMERIC instead of FLOAT or REAL data types unless you really need floating-point precision and are familiar with possible rounding issues.

Create a Table Variable, T1

DECLARE @T1 AS TABLE ();

What syntax is used to define a derived table?

Define the derived table's inner query in parentheses in the FROM clause of the outer query, and specify the name of the derived table after the parentheses. Example: SELECT categoryid, productid, productname, unitprice FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY categoryid ORDER BY unitprice, productid) AS rownum, categoryid, productid, productname, unitprice FROM Production.Products ) AS D WHERE rownum <= 2;

When working with VIEWs, what does WITH CHECK option do?

During an UPDATE, it looks at the WHERE clause filter, and it restricts modifications to only rows that match the filter condition AND prevents you from changing a value such that an affected row will no longer match the WHERE clause filter.

Are the following data types fixed or dynamic? VARCHAR, NVARCHAR, VARBINARY

Dynamic. Variable types use the storage for what you enter, plus a couple of bytes for offset information. The less storage used, the less there is for a query to read, and the faster the query can perform. Variable length types are usually preferable in cases when read performance is a priority.

Which of the following is not a part of the CONTAINS predicate? A. FORMSOF B. THESAURUS C. NEAR D. PROPERTY E. TEMPORARY

E. TEMPORARY is not a valid keyword of the CONTAINS predicate.

What functions can be used within the CATCH block to report on errors?

ERROR_NUMBER Returns the error number. ERROR_MESSAGE Returns the error message. ERROR_SEVERITY Returns the severity of the error. ERROR_LINE Returns the line number of the batch where the error occurred. ERROR_PROCEDURE The function, trigger, or procedure name that was executing when the error occurred. ERROR_STATE The state of the error.

Query to delete a Plan Guide called Cust71

EXEC sys.sp_control_plan_guide N'DROP', N'Cust71';

What is attribute centric?

Elements can have attributes. So, attribute centric is when attributes have their own names, and their values are enclosed in quotation marks

When comparing NULLs, the INTERSECT operator considers them as equal or inequal?

Equal.

As a general rule, errors with a severity level of __ or higher are logged automatically to the SQL Server log and the Windows Application log.

Errors with a security level of 16 or higher.

Errors with a severity level from __ through __ can be specified only by members of the sysadmin fixed server role.

Errors with a severity level from 19 through 25.

Errors with a severity level from __ through __ are considered fatal and cause the connection to be terminated and any open transactions to be rolled back.

Errors with a severity level from 20 through 25.

Errors with severity level __ through __ are informational only.

Errors with severity level 0 through 10.

During a review of system code, you identify a number of cases where cursors are used to access the instances of a certain entity (like customer, employee, shipper) one at a time; next the code invokes a query per each of those instances, storing the result in a temporary table; then the code just returns all the rows from the temporary tables. The existing code has both code maintainability and performance problems. What can you recommend?

Evaluate the use of the APPLY operator instead of the cursor plus the query per row. The APPLY operator involves less code and therefore improves the maintainability, and it does not incur the performance hit that cursors usually do.

You identify performance issues with joins. You realize that there are no indexes created explicitly in the system; there are only the ones created by default through primary key and unique constraints. What can you recommend?

Examine foreign key relationships and evaluate creating indexes on the foreign key columns.

What are logical groups of eight pages called?

Extents. An extent can be mixed, if pages on this extent belong to multiple objects, or uniform, when all pages from this extent belong to a single object only.

True or False: Allocation order scans are affected by logical fragmentation

FALSE

Option to make a cursor a read-only, forward-only cursor

FAST_FORWARD

Explain XQueries FLOWR expression

FLOWR expression is where the real power lies in Xquery. FLOWR is an acronym for For- determines input sequence.Nodes or atomic values Let - optional Where - optional Order by- a name of a iterator must star with a $ Return- This formats the resulting XML of the query

What are the three abbreviations for functions? (when looking up an object type in a system table)

FN = SQL scalar function IF = SQL inline table-valued function TF = SQL table-valued-function

What are the four basic ways to generate an XML document with a query?

FOR XML RAW FOR XML AUTO FOR XML PATH FOR XML EXPLICIT

True or False? With OUTER JOINs, the ON and WHERE clauses are interchangeable.

False. The WHERE clause plays a simple filtering role, it keeps true cases and discards false and unknown cases. The ON predicate determines which rows from the nonpreserved side get matched to rows from the preserved side.

True or False? CTEs are nested like derived tables?

False. To define multiple CTEs, separate them by commas. Each can refer to the previously defined CTEs, and the outer query can refer to all of them. After the outer query terminates, all CTEs defined in that WITH statement are gone. To refer to one CTE from another, use the following general form. WITH C1 AS ( SELECT ... FROM T1 WHERE ... ), C2 AS ( SELECT FROM C1 WHERE ... ) SELECT ... FROM C2 WHERE ...; To refer to multiple instances of the same CTE, use the following general form: WITH C AS ( SELECT ... FROM T1 ) SELECT ... FROM C AS C1 INNER JOIN C AS C2 ON ...;

True or False? The attribute type prevents NULLs.

False. A type doesn't prevent NULLs. For this, you use a NOT NULL constraint.

True or False? It is a bad practice to alias COLUMNS by using the AS clause.

False. Aliasing columns with the AS clause is standard and considered a best practice.

True or False? It is a bad practice to alias TABLES by using the AS clause.

False. Aliasing tables with the AS clause is standard and considered a best practice.

True or False? The UNION set operator can return duplicates.

False. As a set operator, UNION has an implied DISTINCT property, meaning that it does not return duplicate rows.

True or False? The ability to assign attribute aliases is just an aesthetic feature.

False. Attribute aliasing allows you to meet relational requirements.

True or False? Set operators allow the use of ORDER BY clauses inside the individual queries.

False. Because the operators are set operators and not cursor operators, the individual queries are not allowed to have ORDER BY clauses.

True or False? The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in the WHERE clause.

False. Both CASE expressions are allowed wherever a scalar expression is allowed anywhere in the query.

True or False? The simple CASE expression is used when the database recovery model is simple, and the searched CASE expression is used when it's full or bulk logged.

False. CASE expressions have nothing to do with the database recovery model.

True or False? The type of your attribute enables you to control the formatting of the values.

False. Formatting isn't a responsibility of the type or the data layer in general; rather, it is the responsibility of the presentation layer. Sometimes, people use character strings to store dates because they think of storing a date in a certain format. The formatting of a value is supposed to be the responsibility of the application when data is presented

True or False? If you invoke a window function in your query but don't specify a presentation ORDER BY clause, the rows will be presented in the same order as the window function's ordering.

False. If you need such a guarantee, you need to add a presentation ORDER BY clause.

Operators that support batch mode processing in SQL Server 2012 (6)

Filter, Project, Scan, Local hash (partial) aggregation, Hash inner join, Batch hash table build

In SQL Server 2012, what extracts the textual information and removes formatting from the documents?

Filters, called ifilters in full-text terminology,

Are the following data types fixed or dynamic? CHAR, NCHAR, BINARY

Fixed. Fixed types use the storage for the indicated size; for example, CHAR(30) uses storage for 30 characters. This means that updates will not require the row to physically expand, and no data shifting is required. For attributes that get updated frequently, where update performance is a priority, consider fixed types.

How can you enforce that values in certain tables are restricted to specified ranges?

For simple restrictions of ranges in a table, you can use a check constraint. You can then specify the restriction in the expression value of the constraint.

You notice a system uses a lot of triggers to enforce foreign key constraints, and the triggers are error-prone and difficult to debug. What changes can you recommend to reduce the use of triggers?

Foreign key constraints can be implemented by using triggers, but the code can become complex and error prone. You can recommend instead that the database developers implement true referential integrity by using T-SQL declared foreign key constraints rather than triggers.

What functions are useful for ranking results?

Full-text functions.

T-SQL supports what three clauses that allow defined multiple grouping sets?

GROUPING SETS, CUBE, and ROLLUP.

What are the clauses that you can use to define multiple grouping sets in the same query?

GROUPING SETS, CUBE, and ROLLUP.

Value comparisons wok in singletons

General Comparisons work in sequence

How do you ensure that users in a database can see the metadata for views?

Grant them VIEW DEFINITION on the views.

You will use T-SQL scripts to deploy new objects such as tables, views, or T-SQL code to a database. If any kind of T-SQL error occurs, you want the entire deployment script to quit.

How can you accomplish this without adding complex error handling? When you deploy new database objects by using T-SQL scripts, you can wrap the batches in a single transaction and use SET XACT_ABORT ON right after the BEGIN TRANSACTION statement. Then if any T-SQL error occurs, the entire transaction will abort and you will not have to add complex error handling.

Which function do you use to return the last identity value generated in a specific table? MAX SCOPE_IDENTITY @@IDENTITY IDENT_CURRENT

IDENT_CURRENT The IDENT_CURRENT function accepts a table name as input and returns the last identity value generated in that table. **NOTE The maximum value in the table is not necessarily the last identity value generated. The SCOPE_IDENTITY function is not table specific; it's session specific and scope specific. The @@IDENTITY function is not table specific; it's session specific.

Give an example for creating a view.

IF OBJECT_ID('Sales.RankedProducts', 'V') IS NOT NULL DROP VIEW Sales.RankedProducts; GO CREATE VIEW Sales.RankedProducts AS SELECT ROW_NUMBER() OVER(PARTITION BY categoryid ORDER BY unitprice, productid) AS rownum, categoryid, productid, productname, unitprice FROM Production.Products; GO

Two operators that are internally considered equal

IN/OR

Is it required to include the EXEC command when calling a stored procedure?

If the execution of a stored procedure is the first statement in a batch of T-SQL code or the only statement selected in a query window, you do not need the EXEC statement. However, if the stored procedure is the second or later statement, you must precede it with EXEC or EXECUTE. **Exam Tip: Always include the EXEC command when calling a stored procedure. That will avoid getting unexpected and confusing errors. If the statement is no longer the first statement in the batch, it will still run.

What is the difference between a (local) partitioned view and a distributed partitioned view?

If the tables are in one database or at least on one instance of SQL Server, it is called a partitioned view or a local partitioned view. If the tables are spread across multiple SQL Server instances, the view is called a distributed partitioned view.

When should you define a column as NULL?

If you know that a value for a column must be optional because sometimes no value is known at the time the row will be inserted.

When using FETCH, is OFFSET required?

In T-SQL OFFSET is required. In standard SQL, it is not. OFFSET does not require a FETCH.

When using an OUTPUT clause, in what situation can you reference columns from the source?

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

What are the things that the SELECT INTO statement doesn't copy from the source? (Choose all that apply.) Indexes Constraints The IDENTITY property Triggers

Indexes Constraints Triggers

What two column aliasing options are available when working with derived tables?

Inline and external. With the inline form, you specify the column alias as part of the expression, as in <expression> AS alias. With the external aliasing form, don't specify result column aliases as part of the column expressions; instead, name all target columns right after the derived table's name, as in FROM (...) AS D(rownum, categoryid, productid, productname, unitprice). With the external form, ALL target column names must be specified, not just those that are results of computations.

What are the three DML keywords that Xquery supports?

Insert, delete, and replace

What is the default isolation level setting?

Isolation levels are set per session. If you do not set a different isolation level in your session, all your transactions will execute using the default isolation level, READ COMMITTED. For on-premise SQL Server instances, this is READ COMMITTED. In Windows Azure SQL Database, the default isolation level is READ COMMITTED SNAPSHOT.

How does the caching option of the SEQUENCE object work?

It controls how often the current sequence value is written to disk versus written to memory. For example, a sequence with a CACHE 100 defined for it will write to disk once every 100 changes. SQL Server keeps two members in memory, holding the current sequence value and how many values are left. So it will write only to memory 100 times, and only when it runs out of those 100 values will it write the current value plus 100 to disk. The benefit is better performance for allocation of sequence values. The risk is losing a range up to the size of the cache value in case there's an unordered shutdown of the service.

When SQL Server is executing a query, what is the output the Parsing stage?

It creates a tree of logical operators known as a parse tree.

When SQL Server is executing a query, what is the output of the Binding stage?

It creates the algebrized tree, which is a tree of logical operators bound to actual objects.

When working with table expressions, does SQL Server persist the table expressions' result to an internal work table and then interact with that table? or does it interact with the underlying tables directly?

It interacts with the underlying tables directly which means that table expressions don't have a performance side to them.

Is it necessary to reference tables by using two-part names (with both the schema and table name)?

It is not always necessary; however, SQL Server always assigns the table exactly one database schema. Therefore, you SHOULD always reference tables by using two-part names to 1) avoid errors and 2) make your code more robust.

When working with transactions, what are Distributed transactions?

It is possible to make a transaction span more than one server, by using a linked server. In that case, the transaction is known as a distributed (as opposed to local) transaction. After a transaction spans multiple servers by using a linked server, the transaction is considered a distributed transaction and SQL Server invokes the Distributed Transaction Coordinator (MSDTC). A transaction restricted to one database, or to a cross-database transaction, is considered a local transaction, as opposed to a distributed transaction, which crosses SQL Server instance boundaries.

When using the TOP (9) PERCENT option, what does SQL do when the returned number of rows is not whole?

It rounds up. For example, the TOP (1) percent of 830 rows results in 8.3. The query returns 9 rows.

If you do not provide an explicit name when creating a default constraint, will the statement fail or succeed?

It will succeed. If you do not provide an explicit name, SQL Server will supply a machine-generated name.

T-SQL supports specifying the number of rows to filter using the TOP option in SELECT queries without parentheses. Why?

It's only for backward-compatibility reasons. The correct syntax is with parentheses.

What two columns are returned by the CONTAINSTABLE and FREETEXTTABLE functions?

KEY and RANK. The KEY column is the unique key from the index used in the KEY INDEX clause of the CREATE FULLTEXT INDEX statement. RANK returns an ordinal value between 0 and 1000. This is the rank value. It tells you how well a row matches your search criteria.

What window offset functions are supported by T-SQL?

LAG, LEAD, FIRST_VALUE, and LAST_VALUE.

Name three types of OUTER JOINs.

LEFT OUTER JOIN (or LEFT JOIN) preserves the left table. RIGHT OUTER JOIN (or RIGHT JOIN) preserves the right table. FULL OUTER JOIN (or FULL JOIN) preserves both tables.

With T-SQL what two functions can be used to measure the length of an input value?

LEN and DATALENGTH. The LEN function returns the length of an input string in terms of the number of characters. The DATALENGTH function returns the length of the input in terms of number of bytes (which will be different than LEN if unicode characters are used)

Use the keyword ______________ to filter character string data (regular and Unicode) based on pattern matching.

LIKE predicate.

What are the different types of outer joins?

Left, right, and full.

Explain the main difference between the WHERE and HAVING clauses.

Like the WHERE clause, the HAVING clause uses a predicate but evaluates the predicate per group as opposed to per row. This means that (in the HAVING clause) you can refer to aggregate computations because the data has already been grouped.

How can multiple grouping sets be defined within the same query?

List the grouping sets separated by commas within the outer pair of parentheses belonging to the GROUPING SETS clause. You use an inner pair of parentheses to enclose each grouping set. If you don't indicate an inner pair of parentheses, each individual element is considered a separate grouping set. Example: SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders FROM Sales.Orders WHERE shippeddate IS NOT NULL -- exclude unshipped orders GROUP BY GROUPING SETS ( ( shipperid, YEAR(shippeddate) ), ( shipperid ), ( YEAR(shippeddate) ), ( ) );

How are literals of Unicode character strings delimited?

Literals of Unicode character strings are delimited with a capital N and then single quotation marks, as in N'abc'.

What is the general form for a MERGE statement?

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

When filtering by a DATETIME, if no time is specified, what time does SQL use by default?

Midnight.

Name two Unicode character data types.

NCHAR and NVARCHAR (use 2 bytes of storage per character). Support multiple languages.

Do element names have to be unique in an XML document?

No but you can use namespaces or aliases if you think it would help

Can a stored procedure span multiple batches of T-SQL code?

No, a stored procedure can only contain one batch of T-SQL code.

Does a synonym store T-SQL or any data?

No, a synonym is just a name. All that is stored with a synonym is the object it refers to.

Can you change the data type of an existing sequence?

No, but you can change all of its properties by using the ALTER SEQUENCE command. Example: ALTER SEQUENCE Sales.SeqOrderIDs RESTART WITH 1;

A disection of a complete path in XQuery

Node-name/child: : element-name[attribute-name=value] 3 parts: Axis- specifies the direction of travel Node-specifies the criterion for selecting nodes Predicate- Further narrows down the search

What problems occur if you fail to drop your temp table? (#TempOrders)

None really, If you dont drop the temporary table explicitly, it is destroyed when the level that created it terminates

When working with nested transactions, what does an inner ROLLBACK command do?

Note that it doesn't matter at what level you issue the ROLLBACK command. A transaction can contain only one ROLLBACK command, and it will roll back the entire transaction and reset the @@TRANCOUNT counter to 0.

What are some of the most useful XQuery Functions?

Numeric Functions- ceiling(), floor(), round() String Functions- concat(), conatins(), substring(), string-length(), lower-case(), upp-case() Boolean and Boolean Constructor Functions - not(), true(), false() Nodes Functions - local-name(), namespace-uri() Aggregate Functions - Count(), Max(), Min(), Avg(), Sum() Data Accesor Functions - data(), string() SQL SERVER EXTENSION Functions - sql:column(), sql:variable()

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

ON, WHERE, and HAVING.

start using a cursor

OPEN cursor

Cursor use syntax

OPEN cursor; FETCH NEXT FROM cursor INTO var; WHILE @@FETCH_STATUS = 0 BEGIN logic END; CLOSE cursor; DEALLOCATE cursor;

How do you 'Shred XML'?

OPENXML function

Please describe OPENXML?

OPENXML is a function that provides a rowset over in-memory XML documents by using DOM. In other words it allows you open and walk thru an xml document and "shred" it.. extract the data from the markup into a result set / table.

Specify query hints as part of the ___ clause of the SELECT, INSERT, UPDATE, DELETE, MERGE stmts

OPTION

Explain the importance of the ORDER BY clause with the XML Auto option

ORDER BY is required to group all children under one parent. The ORDER BY clause determines the order of rows returned . You could recieve a strange document with an element repeated several times with just parts of nested elements every time.

How does a stored procedure use memory to improve performance?

Once a stored procedure is compiled, the compilation remains in memory.

In a table, how many columns can be assigned the Identity property?

One You can specify both seed and increment values for the number sequence generated. The most common values for seed and increment are (1,1). Example: CREATE TABLE Production.Categories( categoryid INT IDENTITY(1,1) NOT NULL, ...

How many columns with an IDENTITY property are supported in one table?

One.

How many owners can a database schema have?

One... Every database schema must be owned by exactly one authorized database user. That database schema owner can then grant permissions to other users regarding the objects in this schema.

In a CTE, how many times is an anchor query invoked?

Only once.

How many ROLLBACKs must be executed in a nested transaction to roll it back?

Only one ROLLBACK. A ROLLBACK always rolls back the entire transaction, no matter how many levels the transaction has.

Why is it a good idea to not make schema, table, or column names too long?

Organizations often make it part of the naming convention for constraint and index names to include the table name and the names of the columns used as keys in the constraint or index name. Because constraint and index names must also be identifiers, they cannot exceed the maximum identifier length of 128 characters.

What function supports indicating a culture name?

PARSE.

What is XML PATH mode?

PATH mode uses standard XML Xpath expressions to define the elements and attributes of the XML you are creating.

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

PIVOT and UNPIVOT are implemented as table operators.

What is the difference between PIVOT and UNPIVOT?

PIVOT rotates data from a state of rows to a state of columns; UNPIVOT rotates the data from columns to rows.

What is a technique that groups and aggregates data, transitioning it from a state of rows to a state of columns?

Pivoting.

What will prevent a stored procedure from returning a count message (ie 3 row(s) affected) everytime the procedure executes?

Placing a SET NOCOUNT ON at the beginning of every stored procedure prevents the procedure from returning that message to the client. The NOCOUNT setting of ON or OFF stays with the stored procedure when it is created. In addition, SET NOCOUNT ON can improve the performance of frequently executed stored procedures because there is less network communication required when the "rows(s) affected" message is not returned to the client.

When using the same delimiters for ROWS and RANGE, which one includes peers (tied rows in terms of the sort key) and which one does not?

RANGE includes peers. ROWS does not.

What four window ranking functions are supported by T-SQL?

ROW_NUMBER, RANK, DENSE_RANK, and NTILE. The following query demonstrates the use of these functions. SELECT custid, orderid, val, ROW_NUMBER() OVER(ORDER BY val) AS rownum, RANK() OVER(ORDER BY val) AS rnk, DENSE_RANK() OVER(ORDER BY val) AS densernk, NTILE(100) OVER(ORDER BY val) AS ntile100 FROM Sales.OrderValues;

There is an important table that requires some simple logging actions to take place after any changes to the data. The logging is to a custom table built especially to meet application requirements. How can you make it easier for development and still accomplish the logging required.

Recommend that the database developers use a DML AFTER trigger. This type of trigger executes after an INSERT, UPDATE, or DELETE statement and it can write to the logging table.

If you install additional filters on your PC, how do you register them with SQL Server 2012?

Register the filters in SQL Server by using the command: EXEC sys.sp_fulltext_service 'load_os_resources', 1;

What is RDBMS?

Relational database management system (RDBMS). It maintains transactional control over all changes to database data.

How do you Discard Results After Execution

Right-click in the query pane; choose Query Options | Results | Grid; select Discard Results After Execution check box.

When working with VIEWs, what is the purpose of SCHEMABINDING?

SCHEMABINDING guarantees that the underlying table structures cannot be altered without dropping the view.

What is the difference between SELECT INTO and INSERT SELECT?

SELECT INTO creates the target table and inserts into it the result of the query. INSERT SELECT inserts the result of the query into an already existing table.

What function returns a table with key phrases associated with the full-text indexed column from the column_list?

SEMANTICKEYPHRASETABLE SEMANTICKEYPHRASETABLE ( table, { column | (column_list) | * } [ , source_key ] ) The source_key parameter specifies the unique key from the index used in the KEY INDEX clause of the CREATE FULLTEXT INDEX statement. If you omit it, SQL Server returns key phrases for all rows.

What are three table-valued functions that enable the semantic search?

SEMANTICKEYPHRASETABLE, SEMANTICSIMILARITYDETAILSTABLE, and SEMANTICSIMILARITYTABLE.

List properties that you can set for a SEQUENCE object.

START WITH The sequence start value. The default is MINVALUE for an ascending sequence (positive increment) and MAXVALUE for a descending one. INCREMENT BY Increment value. The default is 1. MINVALUE The minimum value to support. The default is the minimum value in the type. For example, for an INT type, it will be -2147483648. MAXVALUE The maximum value to support. The default is the maximum value in the type. CYCLE | NO CYCLE Defines whether to allow the sequence to cycle or not. The default is NO CYCLE. [ { CACHE [ <constant> ] } | { NO CACHE } ] - Increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers. Defaults to CACHE.

Name the Function that provides information like when the statistics were last updated

STATS_DATE()

What T-SQL functions are related to date and time values with an offset?

SWITCHOFFSET and TODATETIMEOFFSET.

Name three types of sub-query results.

Scalar (or single-valued), multi-valued and table-valued.

Name the two types of sub-queries.

Self-contained - are independent of the outer query. Correlated - have a reference to a column from the table in the outer query.

What is the difference between self-contained and correlated subqueries?

Self-contained subqueries are independent of the outer query, whereas correlated subqueries have a reference to an element from the table in the outer query.

What functions give insight into documents and enable you to find key phrases and compare documents?

Semantic similarity functions.

For cases in which you insert rows into a table and you want to specify your own values for an IDENTITY column, what must you do?

Set a session option called SET IDENTITY_INSERT <table> to ON.

What are some advantages of XML?

Since xml supports UNICODE, all most all the human readable written languages can be communicated using xml. It can be used to render data structure, i.e. records and lists and trees. XML is self documenting, i.e. it contains data and description about the data. XML needs another software application called Parser. An XML document is very strict while maintaining a standard. So the parser software for XML is very simple and easy. XML is used both on and offline for storing and processing data. XML follows international standards. XML supports incremental update. XML allows validation of the document using XSD or Schematron. These are types of schema for validating xml documents. The tree like structure of xml is suitable for almost all the types of documents. Being platform independent, it has lots benefits. Like it is not very prone to technological changes. Even though changes are made in DTD or schema, it is easier to keep forward or backward compatibility available. SGML is its predecessor, which is working from 1986.So a vast experience works behind implementation of xml.

You need to replace some of the synonyms in a database. Identify what actions you can take that will not cause users or applications to change their code.

Some synonyms refer to tables. Sometimes column names of the table can change, but the synonym still needs to return the old column names. To keep synonyms working even if column names of a table are changed, you can create a view that refers to the tables and recreate the synonym to refer to the view.

Used whenever input has to be sorted; make sure it's only used on small operations for good performance

Sort operator

What gives you deeper insight into documents by extracting and indexing statistically relevant key phrases?

Statistical semantic search. Full-text search uses these key phrases to identify and index documents that are similar or related.

What types of database objects can have synonyms? (Choose all that apply.) Stored procedures Indexes Temporary tables Database users

Stored procedures and temporary tables

You're a member of the database administrator (DBA) group in a company that manufactures hiking gear. Most tables in the company's OLTP database currently use an IDENTITY property but require more flexibility. For example, often the application needs to generate the new key before using it. Sometimes the application needs to update the key column, overwriting it with new values. Also, the application needs to produce keys that do not conflict across multiple tables.

Suggest an alternative to using the IDENTITY column property. Explain how the alternative solution solves the existing problems. You can address all of the existing problems with the IDENTITY property by using the sequence object instead. With the sequence object, you can generate values before using them by invoking the NEXT VALUE FOR function and storing the result in a variable. Unlike with the IDENTITY property, you can update a column that normally gets its values from a sequence object. Also, because a sequence object is not tied to a specific column in a specific table, but instead is an independent object in the database, you can generate values from one sequence and use them in different tables.

The basic syntax of THROW is...

THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ] There is no severity parameter; the severity is always set to 16.

True or false: temporary tables AND table variables have a physical representation in tempdb

TRUE

What is a disadvantage of using TRUNCATE over DELETE?

TRUNCATE has more restrictions than DELETE and requires stronger permissions.

Must a view consist of only one SELECT statement?

Technically, yes, but a workaround to this is that you can unite (using the UNION, UNION ALL, EXCEPT or INTERSECT statements) multiple SELECT statements that together produce one result set.

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

Temporary tables

Why is it a best practice to always use regular identifiers?

That way, if one of your users does not use the delimiters in a query, their query will still succeed.

What statement is used to modify a full-text catalog?

The ALTER FULLTEXT CATALOG statement.

What two predicates does SQL Server support for limiting the result set of a query by using full-text indexes?

The CONTAINS and FREETEXT predicates.

What predicate allows you to search for the following? Words and phrases in text Exact or fuzzy matches Inflectional forms of a word Text in which a search word is close to another search word Synonyms of a searched word A prefix of a word or a phrase only

The CONTAINS predicate.

What permission is needed to create a synonym?

The CREATE SYNONYM permission, which inherits from the CONTROL SERVER permission. Grant other users permissions such as EXECUTE or SELECT to the synonym, depending on the type of object the synonym stands for.

Can DELETE and TRUNCATE be used against a table if it involves an indexed view?

The DELETE statement is allowed against a table involved in an indexed view. A TRUNCATE statement is disallowed in such a case.

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

The INFORMATION_SCHEMA schema.

What statement inserts the result set returned by a query into the specified target table?

The INSERT SELECT statement.

What is the purpose of the ON clause in the MERGE statement?

The ON clause determines whether a source row is matched by a target row, and whether a target row is matched by a source row. Based on the result of the predicate, the MERGE statement knows which WHEN clause to activate and as a result, which action to take against the target.

Referring to a column alias in the ORDER BY clause is supported. Why is that?

The ORDER BY clause is logically evaluated after the SELECT clause.

What clause allows you to return information from modified rows in modification statements?

The OUTPUT clause.

Explain the importance of the SELECT clause with the XML Auto option

The SELECT clause determines the order of columns returned. The order of the columns should follow one-to-many relationships. A customer can have many orders; therefore, you should have customer columns before order columns in your query.

What object supports defining the start value, increment value, minimum and maximum supported values, cycling, and caching?

The SEQUENCE object.

How can sp_executesql be used to help prevent SQL injection?

The ability to parameterize means that sp_excutesql avoids simple concatenations like those used in the EXEC statement. As a result, it can be used to help prevent SQL injection.

What do the delimiters UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING represent?

The beginning and end of the partition, respectively.

When working with set operators, what determines the column names of result columns?

The column names of result columns are determined by the first query.

There's an UPDATE statement that modifies rows in one table based on information from related rows in another table. The UPDATE statement currently uses a separate subquery for each column that needs to be modified, obtaining the value of the respective column from the related row in the source table. The statement also uses a subquery to filter only rows that have matches in the source table. The process is very slow. Can you suggest ways to improve it?

The customer should consider using an UPDATE statement based on a join instead of the existing use of subqueries. The amount of code will be significantly reduced, and the performance will likely improve. Each subquery requires a separate visit to the related row. So using multiple subqueries to obtain values from multiple columns will result in multiple visits to the data. With a join, through one visit to the matching row, you can obtain any number of column values that you need.

What four built-in database schemas cannot be dropped?

The dbo database schema, the guest schema, the INFORMATION_SCHEMA, and the sys database schema.

Which is the least constraining of all the T-SQL table constraints?

The default constraint. In fact, you could say default constraints don't really constrain anything at all; they just supply a default value during an INSERT if no other value is supplied.

What is the difference between group functions and window functions?

The difference is in how you define the set of rows for the function to work with. With group functions, you use grouped queries to arrange the queried rows in groups, and then the group functions are applied to each group. You get one result row per group not per underlying row. With window functions, you define the set of rows per function and then return one result value per each underlying row and function. You define the set of rows for the function to work with using a clause called OVER.

When using the OVER clause, what do empty parentheses represent?

The entire underlying query's result set. Use a window partition clause to restrict the window. For example, the expression SUM(val) OVER(PARTITION BY custid) represents the current customer's total.

The error number _____ is reserved for a custom message that does not have a custom error number.

The error number 50000 is reserved for a custom message that does not have a custom error number.

What are the rules for formatting identifiers?

The first character must be a letter defined by the Unicode Standard 3.2 (a-z, A-Z, and letters from other Unicode languages), underscore (_), at sign (@), or number sign (#). Subsequent characters can include decimal numbers, dollar sign ($) or any of the characters listed above. The identifier cannot be a reserved keyword in T-SQL, cannot have embedded spaces, and must not include supplementary characters.

What are the forms of aliasing an attribute in T-SQL?

The forms are <expression> AS <alias> <expression> <alias> <alias> = <expression>

What schema is used to contain objects that would be available to the guest user and is rarely used?

The guest schema.

What 5 options are generally used to generate surrogate keys?

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

What is language_term?

The language SQL Server uses for word breaking, stemming, and thesaurus and stopword removal as part of the query. You can use the language_term in the CONTAINS and FREETEXT predicates and CONTAINSTABLE function. You can specify it as an integer, representing the LCID, as a string, representing the language alias or as a hexadecimal string.

What determines what language SQL Server uses to index a document's content?

The locale identifier (LCID).

When I try to refer to a column alias that I defined in the SELECT list in the WHERE clause, I get an error. Can you explain why this isn't allowed and what the workarounds are?

The logical query processing order is FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY. The WHERE clause is evaluated prior to the SELECT clause, and therefore aliases defined in the SELECT clause aren't visible to the WHERE clause.

What are the main advantages of using a TRY/CATCH block over the traditional trapping for @@ERROR?

The main advantage is that you have one place in your code that errors will be trapped, so you only need to put error handling in one place.

Define scan count.

The number of index or table scans performed (going thru every record).

Define LOB logical reads.

The number of large object (LOB) pages read from the data cache. LOBs are columns of types VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), TEXT, NTEXT, IMAGE, XML, or large CLR data types, including the system CLR spatial types GEOMETRY and GEOGRAPHY.

Define LOB read-ahead reads.

The number of large object pages SQL Server reads ahead.

Define LOB physical reads.

The number of large object-type pages read from disk.

Define read-ahead reads.

The number of pages SQL Server reads ahead.

How would you query the Sales.OrderValues view and compute the running total values from the beginning of the current customer's activity until the current order? Use the SUM aggregate. Partition the window by custid. Order the window by orderdate, orderid. Frame the rows from the beginning of the partition (UNBOUNDED PRECEDING) until the current row.

The query should look like the following. SELECT custid, orderid, orderdate, val, SUM(val) OVER(PARTITION BY custid ORDER BY orderdate, orderid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningtotal FROM Sales.OrderValues;

Name the two ways for returning results from the OUTPUT clause.

The result of the OUTPUT clause can be 1) sent back to the caller as a result set from a query or 2) stored in a target table by using the INTO clause.

When SQL Server is executing a query, what is the output of the Optimization stage?

The result of this phase is the actual execution plan, which is a single tree with physical operators.

What is a partitioned view?

The result when you are not able to use table partitioning, but you manually partition your tables and create a view that applies a UNION statement across those tables.

Who is the owner of the Production schema after running the following statement? CREATE SCHEMA Production AUTHORIZATION dbo;

The schema named Production is actually owned by the user named dbo, not by the dbo database schema.

What stored procedure can help determine whether a table with data in it would benefit from compression?

The sp_estimate_data_compression_savings stored procedure.

What T-SQL functions can be considered as abbreviates of the CASE expression?

The standard COALESCE and NULLIF functions, and the nonstandard ISNULL, IIF, and CHOOSE.

What T-SQL functions can be considered as abbreviates of the CASE expression?

The standard COALESCE and NULLIF functions, and the nonstandard ISNULL, IIF, and CHOOSE.

What does a window frame clause require?

The window frame clause requires a window order clause to be present because a set has no order, and without order, limiting rows between two delimiters would have no meaning. In the window frame clause, you indicate the window frame units (ROWS or RANGE) and the window frame extent (the delimiters).

What are the two types of SQL Server identifiers?

There are two types of identifiers: regular and delimited. Regular identifiers are names that follow a set of rules and don't need to be surrounded by delimiters like square brackets ([ ]) or quotation marks (the single character ").

When SQL Server is optimizing a query, what happens in the T-SQL stage?

There is a statement to execute.

In what clauses are windows functions allowed?

They are allowed only in the SELECT and ORDER BY clauses because window functions are supposed to operate on the underlying query's result set.

When are Global temporary tables (##AllStuff) destroyed?

They are destroyed when the session that created them terminates and there are no active references to them.

How are XQuery data types divided?

They are divided into 2 types. Node types and Atomic types. Nodes are basically anything within an XML document and atomic values like integers and strings which have no relationship to any XML document.

What is the visiblity of Global temporary tables (##AllStuff)

They are visible to all sessions

What do word breakers and stemmers do?

They perform linguistic analysis on all full-text data. Because rules differ from language to language, word breakers and stemmers are language specific. A word breaker identifies individual words (or tokens). Tokens are inserted in a full-text index in compressed format. The stemmer generates inflectional forms of a word based on the rules of a language.

When creating a foreign key, the column(s) from the referenced table must be one of what three things?

This column(s) must be a primary key or unique constraint in the table, or else it may instead have a unique index.

In a CTE, how many times is the query containing a recursive member invoked?

This query is invoked repeatedly until it returns an empty result set. In each iteration, the reference to the CTE name from the recursive member represents the previous result set. Then the reference to the CTE name from the outer query represents the unified results of the invocation of the anchor member and all invocations of the recursive member. OR until MAXRECURSION is met (default is 100)

What does the term three-valued logic refer to in T-SQL?

Three-valued logic refers to true, false, and unknown.

While reviewing code in a system, you find many queries that use a number of nesting levels of derived tables, making it very hard to follow the logic. You also find a lot of queries that join multiple derived tables that are based on the same query, and you find that some queries are repeated in a number of places in the code. What can you recommend to reduce the complexity and improve maintainability?

To address the nesting complexity of derived tables, in addition to the duplication of derived table code, you can use CTEs. CTEs don't nest; instead, they are more modular. Also, you can define a CTE once and refer to it multiple times in the outer query. As for queries that are repeated in different places in your code for reusability you can use views and inline table-valued functions. Use the former if you don't need to pass parameters and the latter if you do.

What is the syntax to alter a synonym?

To change a synonym, you must drop and recreate it. Example: DROP SYNONYM dbo.Categories; CREATE SYNONYM dbo.Categories FOR Production.Categories;

In some transactions that update tables, after a session reads a particular value from another table, it is critical that the other table's value not change until the transaction is finished. What is the appropriate transaction isolation level to accomplish this?

To ensure that whenever data is read in a transaction the data will not change until the end of the transaction, you can use the REPEATABLE READ transaction isolation level. This is the least restrictive level that will satisfy the requirements.

You need to replace some of the synonyms in a database. Identify what actions you can take that will not cause users or applications to change their code. Some synonyms refer to tables. However, some of the tables must be filtered. You need to leave the synonym in place but somehow filter what the table returns.

To filter the data coming from the table, you can create a view or inline function that filters the data appropriately, and recreate the synonym to reference the view or function.

Would you recommend using views, inline functions, or synonyms? The application developers do not want to work with complex joins for their reports. For updating data, they will rely on stored procedures.

To remove the need for developers working with complex joins, you can present them with views and inline functions that hide the complexity of the joins. Because they will use stored procedures to update data, you do not need to ensure that the views are updatable.

What are the rules for naming an explicit transaction?

Transaction names must follow the rules for SQL Server identifiers; however, SQL Server only recognizes the first 32 characters as a unique name and ignores any remaining characters, so keep all transaction names to 32 characters or less in length.

What T-SQL functions can be used to apply FORMATTING OPTIONS to an input string?

UPPER, LOWER, LTRIM, RTRIM, and FORMAT functions.

True or False? A PIVOT operator is limited to using only one aggregate function.

True.

True or False? Full text predicates can be a part of the WHERE clause of a query.

True.

True or False? Using attribute aliases, you can assign your own name to a result attribute if you need it to be different than the source attribute name.

True. You can assign your own name to a result attribute by using an alias.

True or False? When using a derived table, the outer query is allowed to refer to column aliases that were assigned by the inner query.

True. That's a classic use of table expressions.

If you want to provide your own value instead of letting the IDENTITY property do it for you, what must you do?

Turn on a session option called IDENTITY_INSERT: SET IDENTITY_INSERT <table> ON; **When you're done, remember to turn it off.

What command should you execute to get information about the number of pages per table accessed by queries?

Turn statistics IO on. You can do this on a session level with the SET STATISTICS IO T-SQL command.

What is a benefit of using a windowed query instead of a grouped query?

Unlike grouped queries, windowed queries do not hide the detail they return a row for every underlying query's row. This means that you can mix detail and aggregated elements in the same query, and even in the same expression.

When filtering date(s) and time(s), is it better to use BETWEEN or <= and >=?

Use >= and < as this form will work correctly in all cases, with all date and time types, whether the time portion is applicable or not.

Suppose you need to capture output from a modification statement, but you are interested only in a subset of the output rows and not all of them. What should you use?

Use Composable DML. With T-SQL, you can define something that looks like a derived table based on a modification with an OUTPUT clause. Then you can have an outer INSERT SELECT statement against a target table, with the source table being this special derived table. The outer INSERT SELECT can have a WHERE clause that filters the output rows from the derived table, inserting only the rows that satisfy the search condition into the target. The outer INSERT SELECT statement cannot have other elements besides WHERE like table operators, GROUP BY, HAVING, and so on.

When working with VIEWs, what does WITH ENCRYPTION do?

Using WITH ENCRYPTION, you can specify that the view text should be stored in an obfuscated manner (this is not strong encryption). This makes it difficult for users to discover the SELECT text of the view.

What does it mean to delimit an Identifier?

Using double quotation marks, as in "Sales"."Orders", as well as a proprietary form using square brackets, as in [Sales].[Orders]. When the identifier is regular, delimiting it is optional.

Is this query valid or invalid? SELECT S.shipperid, MAX(S.companyname) AS companyname, COUNT(*) AS numorders FROM Sales.Shippers AS S INNER JOIN Sales.Orders AS O ON S.shipperid= O.shipperid GROUP BY S.shipperid;

Valid. The aggregate function is an artificial one because there can't be more than one distinct company name per each distinct shipper ID.

Is this query valid or invalid? SELECT S.shipperid, S.companyname, COUNT(*) AS numorders FROM Sales.Shippers AS S INNER JOIN Sales.Orders AS O ON S.shipperid= O.shipperid GROUP BY S.shipperid, S.companyname;

Valid. This query generates the following output. shipperid companyname numorders ----------- -------------- ----------- 1 Shipper GVSUA 249 2 Shipper ETYNR 326 3 Shipper ZHISN 255

What is it called when you check whether an XML document complies with a schema?

Validate the document

Which of the following are reusable? Derived Tables. CTEs (Common Table Expressions). Views. Inline Table-Valued Functions.

Views and Inline Table-Values Functions.

For what two forms of table expressions are the definition of the table expression preserved in the database as an object and are therefore reusable?

Views and Inline table-valued functions.

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

Views can't accept input parameters but inline table-valued functions can accept parameters.

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

Views.

What does the 'V' parameter in the OBJECT_ID() function look for?

Views.

Name the three WAITFOR options.

WAITFOR DELAY, WAITFOR TIME, and WAITFOR RECEIVE. (WAITFOR RECEIVE is used only with Service Broker.) WAITFOR DELAY causes the execution to delay for a requested duration. The following example pauses code execution for 20 seconds: WAITFOR DELAY '00:00:20'; WAITFOR TIME, on the other hand, pauses execution to wait for a specific time. The following example waits until 11:46. WAITFOR TIME '23:46:00';

What is an XML is a document that adheres to the syntax rules specified by the XML 1.0 specification and that it must satisfies both physical and logical structures.

Well-formed document

In some transactions that update tables, after a session reads a particular value from another table, it is critical that the other table's value not change until the transaction is finished.

What is the appropriate transaction isolation level to accomplish this? To ensure that whenever data is read in a transaction the data will not change until the end of the transaction, you can use the REPEATABLE READ transaction isolation level. This is the least restrictive level that will satisfy the requirements.

As a database developer on a key project for your company, you have been asked to refactor a set of stored procedures in your production database server. You have observed that the stored procedures have practically no error handling, and when they do have it, it is ad hoc and unstructured. None of the stored procedures are using transactions.

What plans should you include for refactoring dynamic SQL? Check the stored procedures for the use of dynamic SQL, and where possible, replace calls to the EXECUTE command with the sp_executesql stored procedure.

What is two-valued logic?

When NULLs are not possible in the data and the predicate can evaluate to true or false.

What is three-valued logic?

When NULLs are possible in the data and a predicate can evaluate to true, false or unknown. When using an equality operator in the predicate, you get true when both operands are not NULL and equal, false when both are not NULL and different, and unknown when at least one operand is NULL.

When SQL Server encounters an error in the TRY block, what message is sent to the client?

When SQL Server encounters an error in the TRY block, no message is sent to the client. This contrasts sharply with unstructured error handling, where an error message is always sent to the client and cannot be intercepted. Even a RAISERROR in the TRY block with a severity level from 11 to 19 will not generate a message to the client, but instead transfers control to the CATCH block.

What does SQL Server do when it sees a deadlock?

When SQL Server sees a deadlock, it chooses one of the sessions and terminates the batch.

What is severity level 0 used for?

When you issue a RAISERROR with severity level 0, only an informational message is sent. If you add WITH NOWAIT, the message will be sent without waiting in the output buffer.

When concatenating character strings, what is the difference between the plus (+) operator and the CONCAT function?

When any of the inputs is NULL, the + operator, by default, returns a NULL. That's standard behavior that can be changed by turning off a session option called CONCAT_NULL_YIELDS_NULL, though it's not recommended to rely on nonstandard behavior. CONCAT substitutes a NULL input with an empty string.

What is element centric?

When every element is a nested element of the original element

In what situation can you use a User Defined Function to encapsulate the programming logic you require?

When exactly one value needs to be returned, but not as a parameter.

What is meant by known as "dirty reads?"

When the READ UNCOMMITTED isolation level allows a session to read uncommitted data.

Does SQL Server check for an object's existence when you CREATE a synonym OR when you USE a synonym?

When you USE it.

What namespaces are predefined in SQL Server for XPath and XQuery?

XDT

Describe what happens with an implicit transaction.

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

Is it required to name parameters when passing them to a stored procedure?

When you pass the parameter values by using the parameter names, you can put the named parameters in any order. However, when you pass the parameter values by position, you must use the exact position of the parameters as defined in the CREATE PROCEDURE statement. **Exam Tip: It is a best practice to name the parameters when you call stored procedures. Although passing parameter values by position may be more compact, it is also more error prone. If you pass parameters by name and the parameter order changes in the stored procedure, your call of the procedure will still work.

When using the OUTPUT clause, how must you refer to the modified data?

When you refer to columns from the modified rows, you prefix the column names with the keyword inserted for inserted rows and deleted for deleted rows.

What makes a query a grouped query?

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

When should you recommend using explicit transactions?

Whenever more than one data change occurs in a stored procedure, and it is important that the data changes be treated as a logical unit of work, you should add transaction logic to the stored procedure.

Your company has just recently upgraded the system from SQL Server 2000 to SQL Server 2012. You often need to compute things like running totals, year-to-date calculations, and moving averages. What will you consider now to handle those? What are the things you should watch out for in order to get good performance?

Window aggregate functions are excellent for such computations. As for things to watch out for, with the current implementation in SQL Server 2012, you should generally try to avoid using the RANGE window frame unit. And remember that without an explicit window frame clause, you get RANGE by default, so you want to be explicit and use the ROWS option.

Why can you not refer to a window function in the WHERE clause of a query and what is the workaround for that?

Window functions are allowed only in the SELECT and ORDER BY clauses because the initial window they are supposed to work with is the underlying query's result set. If you need to filter rows based on a window function, you need to use a table expression like a CTE or derived table. You specify the window function in the inner query's SELECT clause and assign the target column an alias. You can then filter the rows by referring to that column alias in the outer query's WHERE clause.

What is the difference between the APPLY and JOIN operators?

With a JOIN operator, both inputs represent static relations. With APPLY, the left side is a static relation, but the right side can be a table expression with correlations to elements from the left table.

Name 2 ways that you can manualy format the XML returned

XML PATH and XML EXPLICIT

What is the most widely used meta-data description for XML?

XML Schema Description (XSD) Documents

What is an XSD Document?

XML Schema Description Document are XML-documents that describe meta-data of other XML documents With the XSD standard, you can specify element names, data types, and number of occurrences of an element, constraints, and more

Are XML data types small, average, or large?

XML data types are large objects. There can be up to 2GB of data in every single column value.

Is XML case sensitive or insentive?

XML is case-sensitive Unicode text

What namespaces are predefined in SQL Server for XML schema?

XML,XS and XSI. XSI is used to associate XML schemas with instance documents

What type of schema is XSD?

XSD schema is considered a inline schema because it is included inside the XML that is returned before the actual XML data

What are the performance benefits in using the WHERE filter?

You reduce network traffic by filtering in the database server instead of in the client, and you can potentially use indexes to avoid full scans of the tables involved.

What is the ANSI standard and the SQL Server default regarding QUOTED_IDENTIFIER?

You should leave QUOTED_IDENTIFIER set to ON because that is the ANSI standard and the SQL Server default.

How would you quickly measure the amount of disk IO a query is performing?

You should use the SET STATISTICS IO command.

The wildcard [<character list>] means ...

[<character list>] - specific character list. '[AC]%': string where first character is A or C

You want result rows to be sorted by orderdate ascending, and then by orderid, ascending. Which of the following clauses gives you what you want? (Choose all that apply.) a. ORDER BY ASC(orderdate, orderid) b. ORDER BY orderdate, orderid ASC c. ORDER BY orderdate ASC, orderid ASC d. ORDER BY orderdate, orderid

b, c and d b. The default direction is ascending, so this clause uses ascending order for both orderdate and orderid. c. This clause explicitly uses ascending order for both orderdate and orderid. d. The default direction is ascending, so this clause uses ascending order for both orderdate and orderid.

You execute a query with a TOP (3) option. Which of the following options most accurately describes how many rows will be returned? a. Fewer than three rows b. Three rows or fewer c. Three rows d. Three rows or more e. More than three rows f. Fewer than three, three, or more than three rows

b. If there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are three rows or more without TOP, the query will return three rows.

Which of the following predicates are search arguments? a. DAY(orderdate) = 1 b. companyname LIKE 'A%' c. companyname LIKE '%A%' d. companyname LIKE '%A' e. orderdate > = '20120212' AND orderdate < '20120213'

b. The LIKE predicate is a search argument when the pattern starts with a known prefix. e. Because no manipulation is applied to the filtered column, the predicate is a search argument.

You organize a table as ___when you create a clustered index

balanced tree

A table organized as a balanced tree is said to be

clustered

If a table is organized as a balanced tree, then the row locator is the ___

clustering key

What form must a predicate take in order to be a search argument?

column operator value or value operator column For example, col1 = 10, and col1 > 10.

System procedure to find how many pages are allocated for an object

dbo.sp_spaceused

How IAM pages for an object are organized; each page has a pointer to the descendant and antecedent

doubly linked list

FIRST_VALUE and LAST_VALUE functions operate on the first or last row in the _____.

frame.

Structure where data is not organized in any logical order; just a bunch of pages and extents.

heap

Implicit conversions can sometimes ____ performance.

hurt.

A table-valued UDF with multiple lines of code is called a ______________________________.

multistatement table-valued UDF.

What are some cases where and iterative solution using cursors are OK?

needing to run a stored procedure per record. management tasks that need to be done per object in a set, such as a set of databases, tables, or indexes. You need to query a catalog view or other system object to return the set of objects in question, iterate through the result rows one at a time, and then perform the task at hand per object, such as rebuilding indexes.

See book for common query hints

page 662

See book for common table hints

page 663

See book for common table hints in the FROM clause

page 664

What is the logical evaluation order of the different predicates?

parentheses have the highest precedence NOT precedes AND and OR AND precedes OR. () NOT AND OR

Used to optimize when you don't want to or can't change the query text with a hint

plan guides

What phase of the Hash join applies the hash function on each of the rows from the other input; then scans all bucket rows (phase 2)

probe phase

By filtering rows in the query (as opposed to in the client), you ______ network traffic

reduce.

If the table is a heap, the row locator is a ___

row identifier (RID)

Unit of transfer from disk to memory.

segment

What is the Dynamic management function to check some basic information about the index?

sys.dm_db_index_ physical_stats

DMV to find how many pages are allocated for an object

sys.dm_db_index_physical_stats

What stored procedure can be used to allocate an entire range of sequence values of a requested size?

sp_sequence_get_range You provide the requested range size by using the input parameter @range_size and collect the first value in the allocated range by using the output parameter @range_first_value. Then you can assign the values in the allocated range as you want. The sequence itself gets modified only once by advancing it from its current value to the current value plus @range_size.

What is an alternative name for bitmap filtered hash join

star join

What algorithm is used if input is ordered by the columns used in the GROUP BY clause

stream aggregation

What are the two algorithms for calculating aggregations?

stream aggregation, hash aggregation

All auto-created statistics names start with this

string _WA_Sys_

SQL Server internally always maintains uniqueness of the clustering key though this

uniquifier

How do you make directives for the query execution.

use optimization hints

When SQL Server is executing a query, what is the output of the Execution stage?

your result set... and posibly a cached plan.

What are some options to make cursors faster?

- FAST_FORWARD CURSORS are usually the fastest option with SQL Server, FORWARD_ONLY CURSOR takes 4 times the time as the FAST FORWARD CURSOR

In the following query, what values will be returned for the GROUPING elements? SELECT shipcountry, GROUPING(shipcountry) AS grpcountry, shipregion , GROUPING(shipregion) AS grpregion, shipcity , GROUPING(shipcity) AS grpcity, COUNT(*) AS numorders FROM Sales.Orders GROUP BY ROLLUP( shipcountry, shipregion, shipcity );

0 when the element is part of the grouping set and 1 when it isn't. Example: shipcountry grpcountry shipregion grpregion shipcity grpcitry numorders ------------ ---------- ----------- ---------- ------------- ---------- ---------- Argentina 0 NULL 0 Buenos Aires 0 16 Argentina 0 NULL 0 NULL 1 16 Argentina 0 NULL 1 NULL 1 16 ... USA 0 AK 0 Anchorage 0 10 USA 0 AK 0 NULL 1 10 USA 0 CA 0 San Francisco 0 4 USA 0 CA 0 NULL 1 4 USA 0 ID 0 Boise 0 31 USA 0 ID 0 NULL 1 31 ... USA 0 NULL 1 NULL 1 122 ... NULL 1 NULL 1 NULL 1 830

If you create a primary key constraint or a unique constraint, what does SQL Server automatically create?

A unique index with the same name as the constraint.

disassociate a cursor

DEALLOCATE cursor

Cursor declaration syntax (with fast forward)

DECLARE cursorname CURSOR FAST_FORWARD FOR logic;

Commands that are required to work with a cursor (5)

DECLARE, OPEN, FETCH in a loop, CLOSE, DEALLOCATE

What is the standard syntax for a DELETE statement?

DELETE FROM <table> WHERE <predicate>;

What are subqueries that are referenced like tables in queries?

Derived tables and table expressions.

True or False? A CTE (Common Table Expression) can be reused?

False. It's a named table expression that is visible only to the statement that defines it.

True or False? SQL Server creates indexes on foreign keys.

False. SQL Server doesn't create any indexes on the foreign key columns. Such indexes could improve the performance of joins based on those relationships. Developers should identify cases where indexes can be useful and create them. When working on index tuning, examine foreign key columns, and evaluate the benefits of creating indexes on those.

True or False? T-SQL requires all result attributes of a query to have names.

False. T-SQL allows a result attribute to be without a name when the expression is based on a computation without an alias.

Should you use SQL Server Extended Events, SQL Trace or SQL Server Profiler?

SQL Server Extended Events because Extended Events is more lightweight and SQL Trace and SQL Server Profiler are deprecated in future versions of SQL Server.

When SQL Server is executing a query, what happens in the Binding stage?

SQL Server binds objects to logical operators (after checking whether objects exist).

What is the benefit of using TRUNCATE over DELETE?

TRUNCATE uses optimized logging, and therefore tends to be much faster.

What function attempts to cast a value as a target data type, and if it succeeds, returns the value, returning NULL if the test fails?

TRY_CONVERT.

What function allows you to take an input string containing data of an indeterminate data type and convert it to a specific data type if possible, and return NULL if it is not?

TRY_PARSE.

Define table expressions.

Table expressions are named queries. Write an inner query that returns a relational result set, name it, and query it from an outer query.

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

Table variables.

What set operator returns only distinct rows that are common to both sets?

The INTERSECT set operator.

In many of your queries, you need to perform recency computations that is, identify the time passed between a previous event and the current, or between the current event and the next. So far, you used subqueries for this. What will you consider now instead?

The LAG and LEAD functions are natural for this purpose.

What is the default database schema for new objects created by users having the db_owner or db_ddl_admin roles?

The dbo database schema.

This algorithm variety used in a data warehousing scenario, where you can have large inputs for a query and few concurrent users, so SQL Server can execute a query in parallel.

bitmap filtered hash join

Can further optimize a hash join; considered either a 4th basic algorithm or an extension of the hash join

bitmap filtering

Can you have indexes on table variables? a. No B. Yes, by running the CREATE INDEX command c. Yes, indirectly by defning primary key and unique constraints D. Yes, by defning foreign keys

c a. incorrect: You can have indexes on table variables. B. incorrect: The CREATE INDEX command is not supported against table variables. c. correct: You can get indexes indirectly by defning primary key and unique constraints. D. incorrect: Foreign keys do not create indexes; besides, they are not supported on temporary tables and table variables.

What schema is reserved by SQL Server for system objects such as system tables and views?

The sys database schema.

When creating a table in T-SQL, what things MUST be specified?

The table name, column names and column data types.

Full-text indexes are stored in __________________.

full-text catalogs. A full-text catalog is a virtual object, a container for full-text indexes. As a virtual object, it does not belong to any filegroup.

You observe complex operations that use nested triggers, which have never been made to work correctly in the application. What action can you recommend to eliminate the use of nested triggers?

You can recommend that the application disable nested triggers on the development server so that the database developers can get used to the idea of completing all necessary actions within only one level of a trigger. That should help simplify the trigger code and improve the ability to debug it.

True or False? Grouped queries return one result row per group.

True.

True or False? If a scalar subquery returns more than one value, the code fails at run time.

True.

True or False? Instead of the verbose form of the frame extent ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW you can use the shorter form ROWS UNBOUNDED PRECEDING and retain the same meaning.

True.

True or False? RANK and DENSE_RANK are deterministic.

True. Because the RANK and DENSE_RANK functions compute the same ranking value to rows with the same ordering value, both functions are deterministic even when the ordering isn't unique.

True or False? A query that invokes a group function but doesn't have an explicit GROUP BY clause arranges all rows in one group.

True. Consider the following query as an example. USE TSQL2012; SELECT COUNT(*) AS numorders FROM Sales.Orders; This query generates the following output. numorders ----------- 830

True or False? It is a bad practice to NOT assign column aliases when the column is a result of a computation.

True. Not aliasing a column that is a result of a computation is non-relational and is considered a bad practice.

What is the best way to retrieve data from an XML document?

Use XML indexes: exist(), secondary indexes are PATH, VALUE, and PROPERTY

How can you pass information from sp_executesql to the caller?

Use one or more OUTPUT parameters. You can also persist the data in a permanent or temporary table, but the most direct method is through the OUTPUT parameter.

When using irregular identifiers, what is the preferred way to reference them?

Use square brackets. Using quotation marks as delimiters is the ANSI SQL standard, but this requires that the SET QUOTED_IDENTIFIER setting is set to ON, which is the SQL Server default. Because it is possible to turn that setting to OFF, using quotation marks is risky.

If you write an expression that involves operands of different types, SQL Server will have to apply ________ conversion to align the types.

implicit.

A nested loops join is efficient when Sql Server can perform a(n) ___ in the ___.

index seek, inner input

With which set operator does the order of the input queries matter?

With the EXCEPT set operator.

Can you generate and execute dynamic SQL in a different database than the one your code is in?

Yes, because the USE <database> command can be inserted into a dynamic SQL batch.

Is it possible to move a table from one schema to another?

Yes, by using the ALTER SCHEMA TRANSFER statement. Example: The following statement moves the Production.Categories table to the Sales database schema. ALTER SCHEMA Sales TRANSFER Production.Categories;

Can you run explicit transactions in stored procedures?

Yes. You can run explicit transactions interactively or in code such as stored procedures.

Can you store indexes from the same full-text catalog to different filegroups?

Yes. A full-text catalog is a virtual object only; full-text indexes are physical objects. You can store each full-text index from the same catalog to a different file group.

You can add your own custom error messages by using sp___________.

You can add your own custom error messages by using sp_addmessage.

means that pages are not full

internal fragmentation

SQL Server uses IAM pages to scan heaps in this order

allocation/physical

Where does SQL Server sort the data, if a sort is needed? a. In the current database B. In the master database c. In the msdb database D. SQL Server sorts data in memory, or spills the data to tempdb if it does not ft in memory.

correct answer: D a. incorrect: SQL Server sorts data in memory, or spills the data to tempdb if it does not ft in memory. B. incorrect: SQL Server sorts data in memory, or spills the data to tempdb if it does not ft in memory. c. incorrect: SQL Server sorts data in memory, or spills the data to tempdb if it does not ft in memory. D. correct: SQL Server sorts data in memory, or spills the data to tempdb if it does not ft in memory.

How can SQL Server estimate the cardinality of a query? a. SQL Server stores the cardinality information on leaf-level pages of indexes. B. SQL Server quickly executes the query on 10 percent of sample data. c. SQL Server cannot estimate the cardinality of a query if you do not provide a table hint. D. SQL Server uses statistics to estimate the cardinality of a query.

correct answer: D a. incorrect: There is no cardinality information on leaf-level pages of indexes. B. incorrect: SQL Server does not execute a query in advance on sample data. c. incorrect: SQL Server can estimate the cardinality of a query. D. correct: SQL Server uses statistics to estimate the cardinality of a query.

The LAG and LEAD functions rely on an offset with respect to the _______ row.

current.

A query with an ORDER BY clause conceptually returns a ______ and not a relation.

cursor.

This algorithm needs at least one equijoin predicate and sorted inputs from both sides; should be supported by indexes on both tables involved.

merge joins

When working with set operators, where can an ORDER BY clause be used (optionally)?

An ORDER BY clause can be used to determine presentation ordering of the result set.

What type of join allows you to preserve all rows from one or both sides of the join, even if there are no matching rows in the other side based on the ON predicate?

An OUTER JOIN.

SQL Server maintains distribution statistics (histograms) for which? Temporary Tables or Table Variables

temporary tables

What is the general format for dealing with special characters in an xml document?

they must be escaped by using an ampersand (&), followed by a special code, followed by a semicolon (;)

What are the stages SQL Server uses for executing queries?

- Parsing - Binding - Optimization - Execution

In every unpivoting task, you need to identify what three elements?

1) The source columns that you need to unpivot. 2) The target names column. 3) The target values column. After you identify these three elements, you use the following query form to handle the unpivoting task: SELECT < column list >, < names column >, < values column > FROM < source table > UNPIVOT( < values column > FOR < names column > IN( <source columns> ) ) AS U;

In all pivot queries, you need to identify what three elements?

1) What you want to see on rows...The ON ROWS or GROUPING element - . 2) What you want to see on columns.... The ON COLS or SPREADING element - 3) What you want to see in the intersection of each distinct row and column value.... The DATA or AGGREGATION element -

Name the three elements of the thesaurus file, and describe the function of each.

1) diacritics_sensitive - Set the value of this element to 0 if the language is accent insensitive, or to 1 if it is accent sensitive. 2) expansion - Use this element to add expansion words for a word. For example, you can add the expansion word "author" to the word "writer" in order to search for "author" as well when an end user searches for the word "writer." 3) replacement - Use this element to define replacement words or terms for a specific word or term. For example, "Windows 2008" could be a replacement for "Win 2k8." In such an example, SQL Server would search for "Windows 2008," even though "Win 2k8" was used in a search term.

Why are window functions allowed only in the SELECT and ORDER BY clauses of a query? A. Because they are supposed to operate on the underlying query's result, which is achieved when logical query processing gets to the SELECT phase. B. Because Microsoft didn't have time to implement them in other clauses. C. Because you never need to filter or group data based on the result of window functions. D. Because in the other clauses, the functions are considered door functions (also known as backdoor functions).

A A. Correct: Window functions are supposed to operate on the underlying query's result set. In terms of logical query processing, this result set is reached in the SELECT phase. B. Incorrect: Standard SQL defines this restriction, so it has nothing to do with Microsoft's time constraints. C. Incorrect: There are practical reasons to want to filter or group data based on the results of window functions. D. Incorrect: There are neither door functions nor backdoor functions in SQL.

How can you create synonyms for the words searched? A. You can edit the thesaurus file. B. You can create a thesaurus table. C. You can use the stopwords for synonyms as well. D. Full-text search does not support synonyms.

A A. Correct: You can add synonyms by editing the thesaurus file. B. Incorrect: Full-text search uses thesaurus files and not tables for synonyms. C. Incorrect: You cannot use stopwords for synonyms. D. Incorrect: Full-text search supports synonyms.

What is a 'QName'?

A 'Qname' is a qualified name. Every identifier in XQuery is a 'Qname'

Is it required to use BEGIN and END statements before and after a WHILE loop?

A BEGIN/END block is optional in a WHILE loop if you only have one statement; however, it is a best practice to include it. The BEGIN/END block helps you organize your code, makes it easier to read, and makes it easier to modify in the future. Any statement block in a WHILE loop with more than one statement requires the BEGIN/END construct.

What are the benefits of using an UPDATE statement based on joins? (Choose all that apply.) A. You can filter the rows to update based on information in related rows in other tables. B. You can update multiple tables in one statement. C. You can collect information from related rows in other tables to be used in the source expressions in the SET clause. D. You can use data from multiple source rows that match one target row to update the data in the target row.

A C The join can be used to filter the updated rows. You cannot update multiple tables in one UPDATE statement. The join gives you access to information in other tables that can be used in the source expressions for the assignments. When multiple source rows match one target row, you get a nondeterministic update in which only one source row is used. Also, the fact that such an update doesn't fail should be considered a disadvantagenot a benefit.

What join performs a Cartesian Product of two input tables?

A CROSS JOIN. It performs a multiplication between the tables, yielding a row for each combination of rows from both sides. If you have m rows in table T1 and n rows in table T2, the result of a cross join between T1 and T2 is a virtual table with m x n rows.

__Which of the following T-SQL statements automatically occur in the context of a transaction? (Choose all that apply.) A. An ALTER TABLE command B. A PRINT command C. An UPDATE command D. A SET command

A and C A. Correct: An ALTER TABLE command is a DDL command that changes metadata and always executes as a transaction. B. Incorrect: A PRINT command does not change data, and therefore does not execute by itself in a transaction. C. Correct: An UPDATE statement changes data and executes as a transaction. D. Incorrect: A SET statement only affects session settings and does not change data, and therefore does not execute as a transaction.

What are the benefits of using the combination of statements CREATE TABLE and INSERT SELECT over SELECT INTO? (Choose all that apply.) A. Using the CREATE TABLE statement, you can control all aspects of the target table. Using SELECT INTO, you can't control some of the aspects, like the destination file group. B.The INSERT SELECT statement is faster than SELECT INTO. C. The SELECT INTO statement locks both data and metadata for the duration of the transaction. This means that until the transaction finishes, you can run into blocking related to both data and metadata. If you run the CREATE TABLE and INSERT SELECT statements in separate transactions, locks against metadata will be released quickly, reducing the probability for and duration of blocking related to metadata. D. Using the CREATE TABLE plus INSERT SELECT statements involves less coding than using SELECT INTO.

A and C A. Correct: SELECT INTO has limited control over the definition of the target, unlike the alternative that has full control. B. Incorrect: The INSERT SELECT statement generally isn't faster than SELECT INTO. In fact, there are more cases where SELECT INTO can benefit from minimal logging. C. Correct: SELECT INTO locks both data and metadata, and therefore can cause blocking related to both. If the CREATE TABLE and INSERT SELECT are executed in different transactions, you hold locks on metadata only for a very short period. D. Incorrect: It's exactly the other way aroundSELECT INTO involves less coding because you don't need to define the target table.

Which full-text search elements can you use to prevent indexing noisy words? (Choose all that apply.) A. Stopwords B. Thesaurus C. Stemmer D. Stoplists

A and D A. Correct: Stopwords include noisy words. B. Incorrect: Thesaurus is used for synonyms. C. Incorrect: Stemmer is used for generating inflectional forms of words. D. Correct: You group stopwords in stoplists.

An application uses a procedure that accepts as inputs attributes of a row. The procedure then uses logic that checks whether the key already exists in the target table, and if it does, updates the target row. If it doesn't, the procedure inserts a new row into the target. The problem is that occasionally the procedure fails due to a primary key violation. This happens when the existence check doesn't find a row, but between that check and the insertion, someone else managed to insert a new row with the same key. Can you suggest a solution to the existing problem with the procedure that updates the row when the source key exists in the target and inserts a row if it doesn't?

A recommended solution is to use the MERGE statement. Define the source for the MERGE statement as a derived table based on the VALUES clause, with a row made of the input parameters for the procedure. Specify the table hint HOLDLOCK or SERIALIZABLE against the target to prevent conflicts such as the ones that currently exist in the system. Then use the WHEN MATCHED clause to issue an UPDATE action if the target row exists, and the WHEN NOT MATCHED clause to issue an INSERT action if the target row doesn't exist.

Which of the following are not allowed in the PIVOT operator's specification? (Choose all that apply.) A. Specifying a computation as input to the aggregate function B. Specifying a computation as the spreading element C. Specifying a subquery in the IN clause D. Specifying multiple aggregate functions

A, B, C, and D A. Correct: You cannot specify a computation as input to the aggregate function, rather just a name of a column from the input table. B. Correct: You cannot specify a computation as the spreading element, rather just a name of a column from the input table. C. Correct: You cannot specify a subquery in the IN clause, rather just a static list. D. Correct: You cannot specify multiple aggregate functions, rather just one.

What can you search for with the CONTAINS predicate? (Choose all that apply.) A. Inflectional forms of a word B. Synonyms of a searched word C. Translations of a word D. Text in which a search word is close to another search word E. A prefix of a word or a phrase only

A, B, D, and E A. Correct: You can search for inflectional forms of a word. B. Correct: You can search for synonyms of a searched word. C. Incorrect: Full-text search does not support translations. D. Correct: You can search for text in which a search word is close to another search word. E. Correct: You can search for a prefix of a word or a phrase only.

Of the following, which can be done with the ALTER TABLE command? select all that apply A. Add/Remove a column B. Add/Remove a constraint C. Add/Remove an Identity property D. Change a column name E. Change the data type of a column

A, B, E Using ALTER TABLE you can: Add or remove a column, including a computed column. (New columns are placed at the end of the table's column order.) Change the data type of a column. Add or remove a constraint.

Which of the following strategies can help reduce blocking and deadlocking by reducing shared locks? (Choose all that apply.) A. Add the READUNCOMMITTED table hint to queries. B, Use the READ COMMTTED SNAPSHOT option. C. Use the REPEATABLE READ isolation level. D. Use the SNAPSHOT isolation level.

A, B, and D A. Correct: Adding a READUNCOMMITTED table hint causes no shared locks to be used by the statement. B. Correct: The READ COMMITTED SNAPSHOT option reads committed data from versions, not by acquiring shared locks. C. Incorrect: The REPEATABLE READ isolation level actually holds shared locks until the end of a transaction, and therefore can actually increase blocking and deadlocking. D. Correct: The SNAPSHOT isolation level also reduces shared locks by reading committed data from committed versions and not by using shared locks, so it also can reduce blocking and deadlocking.

What are the advantages of sp_executesql over the EXECUTE() command? (Choose all that apply.) A. sp_executesql can parameterize search arguments and help prevent SQL injection. B. sp_executesql uses Unicode strings. C. sp_executesql can return data through output parameters.

A, C A. Correct: Parameterization is the key advantage of sp_executesql over the EXEC() statement because it ensures that any injected code will only be seen as a string parameter value, and not as executable code. B. Incorrect: Although sp_executesql does require Unicode strings as parameters, this fact is not necessarily an advantage. The EXECUTE command accepts both Unicode and non-Unicode, and therefore could be considered more flexible. C. Correct: The use of output parameters solves a serious limitation of the EXECUTE command. EXECUTE cannot return information to the calling session directly.

Which of the following statements are true about an INSTEAD OF trigger? (Choose all that apply.) A. INSTEAD OF triggers can be created on views. B. INSTEAD OF triggers execute instead of AFTER triggers. C. INSTEAD OF triggers can only be declared for UPDATE statements. D. INSTEAD OF triggers execute code in place of the original DML statement.

A, D A. Correct: You can create INSTEAD OF triggers on views to reroute inserts or updates to the underlying base tables. B. Incorrect: INSTEAD OF triggers execute instead of their DML statements, not instead of AFTER triggers. C. Incorrect: INSTEAD OF triggers can be declared for all DML statements: INSERT, UPDATE, and DELETE. D. Correct: With INSTEAD OF triggers, you can substitute the trigger code in place of the original DML statement.

Which of the following are true about the SET QUOTED_IDENTIFIER statement? (Choose all that apply.) A. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit T-SQL identifiers such as table and column names. B. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit T-SQL identifiers such as table and column names. C. When set to ON, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit strings. D. When set to OFF, QUOTED_IDENTIFIER allows you to use double quotation marks to delimit strings.

A, D When you set QUOTED_IDENTIFIER to ON, you can use double quotation marks to delimit T-SQL identifiers such as table and column names. When you set QUOTED_IDENTIFIER to OFF, you can use double quotation marks to delimit strings.

Which of the following is applicable to a DELETE statement? (Choose all that apply.) A. The statement writes more to the transaction log than TRUNCATE. B. The statement resets an IDENTITY property. C. The statement is disallowed when a foreign key points to the target table. D. The statement is disallowed when an indexed view based on the target table exists.

A. The DELETE statement writes more to the transaction log than TRUNCATE.

In an INSERT SELECT statement, how do you generate sequence values in specific order? A. Use the OVER clause in the NEXT VALUE FOR function. B. Specify an ORDER BY clause at the end of the query. C. Use TOP (100) PERCENT and ORDER BY in the query. D. Use TOP (9223372036854775807) and ORDER BY in the query.

A. Using the OVER clause, you can control the order of assignment of sequence values in an INSERT SELECT statement.

When referring in the OUTPUT clause to columns from the inserted rows, when should you prefix the columns with the keyword inserted? A. Always B. Never C. Only when the statement is UPDATE D. Only when the statement is MERGE

A. When referring to elements from inserted rows, you must always prefix the column with the keyword inserted.

In a MERGE statement, what can you specify as the source data in the USING clause? (Choose all that apply.) A. A regular table, table variable, or temporary table B. A table expression like a derived table or a CTE C. A stored procedure D. A table function like OPENROWSET or OPENXML

A. B, D Tables, table variables, and temporary tables are allowed. Table expressions are allowed. Table functions are allowed.

What are the benefits of using a CTE over derived tables? (Choose all that apply.) A. CTEs are better performing than derived tables. B. CTEs don't nest; the code is more modular, making it easier to follow the logic. C. Unlike with derived tables, you can refer to multiple instances of the same CTE name, avoiding repetition of code. D. Unlike derived tables, CTEs can be used by all statements in the session, and not just the statement defining them.

A. Incorrect: All types of table expressions are treated the same in terms of optimizationthey get unnested. B. Correct: If you want to refer to one derived table from another, you need to nest them. With CTEs, you separate those by commas, so the code is more modular and easier to follow. C. Correct: Because the CTE name is defined before the outer query that uses it, the outer query is allowed to refer to multiple instances of the same CTE name. D. Incorrect: CTEs are visible only in the scope of the statement that defined them.

What kind of statements does SQL Server consider as transactions?

All operations that in any way write to the database are treated by SQL Server as transactions. This includes: All data manipulation language (DML) statements such as INSERT, UPDATE, and DELETE. All data definition language (DDL) statements such as CREATE TABLE and CREATE INDEX. Technically, even single SELECT statements are a type of transaction in SQL Server; these are called read-only transactions.

What type of join allows you to match rows from two tables based on a predicate, usually one that compares a primary key value in one side to a foreign key value in another side?

An INNER JOIN. The join's matching predicate is specified in the ON clause.

In which phase of query execution does SQL Server check whether the objects referred to by the query exist? A. In the parsing phase B. In the binding phase C. In the optimization phase D. In the execution phase

B A. Incorrect: In the parsing phase, SQL Server checks for syntax correctness. B. Correct: SQL Server resolves object names and binds them to logical operators in the binding phase. C. Incorrect: In the optimization phase, SQL Server generates candidate plans and selects the execution plan. D. Incorrect: In the execution phase, SQL Server executes the query and caches the execution plan.

How does the PIVOT operator determine what the grouping element is? A. It's the element specified as input to the GROUPING function. B. It's determined by elimination the element(s) from the queried table that were not specified as the spreading or aggregation elements. C. It's the element specified in the GROUP BY clause. D. It's the primary key.

B A. Incorrect: The GROUPING function is related to grouping setsnot to pivoting. B. Correct: The PIVOT operator determines the grouping element by eliminationit's what's left besides the spreading and aggregation elements. C. Incorrect: When using the PIVOT operator, the grouping for pivoting happens as part of the PIVOT operatorbefore the GROUP BY clause gets evaluated. D. Incorrect: The PIVOT operator doesn't look at constraint definitions to determine the grouping element.

What are the actions of the optimization phase of query execution? (Choose all that apply.) A. Generation of the algebrized tree B. Generation of candidate plans C. Selection of the best candidate plan D. Caching the plan E. Query execution

B and C A. Incorrect: An algebrized tree is generated in the binding phase. B. Correct: In the optimization phase, SQL Server generates candidate plans. C. Correct: During the optimization phase, SQL Server selects an execution plan from the set of candidate plans. D. Incorrect: The plan is cached in the execution phase. E. Incorrect: A query is executed in the execution phase.

Which commands turn on an XML plan? (Choose all that apply.) A. SET EXECUTION_XML ON B. SET SHOWPLAN_XML ON C. SET XML PLAN ON D .SET STATISTICS XML ON

B and D A. Incorrect: There is no SET EXECUTION_XML command. B. Correct: You use the SET SHOWPLAN_XML command to turn on the estimated XML plans. C. Incorrect: There is no SET XML PLAN command. D. Correct: You use the SET STATISTICS XML command to turn on the actual XML plans.

Which of the following columns would be appropriate as a surrogate key? (Choose all that apply.) A.The time (in hundredths of a second) that the row was inserted B. An automatically increasing integer number C. The last four digits of a social security number concatenated with the first eight digits of a user's last name D. A uniqueidentifier (GUID) newly selected from SQL Server at the time the row is inserted

B and D A. Incorrect: Surrogate keys should be meaningless, and time is a meaningful number. In addition, there is no guarantee that two rows could not be inserted at nearly the same time. B. Correct: An automatically increasing integer value is commonly used as a surrogate key because it does not reflect meaningful data about the row, and it will be unique for every row. C. Incorrect: A surrogate key should not have meaningful data such as a portion of a user id and the user's name. D. Correct: A uniqueidentifier (GUID) can also be used as a surrogate key when it is uniquely generated for each row.

Which of the following is applicable to a TRUNCATE statement? (Choose all that apply.) A. The statement writes more to the transaction log than DELETE. B. The statement resets an IDENTITY property. C. The statement is disallowed when a foreign key points to the target table. D. The statement is disallowed when an indexed view based on the target table exists.

B, C and D. The TRUNCATE statement resets an IDENTITY property. The TRUNCATE statement is disallowed when a foreign key pointing to the table exists. The TRUNCATE statement is disallowed when an indexed view based on the table exists.

What are the advantages of using a sequence object instead of IDENTITY? (Choose all that apply.) A. The IDENTITY property doesn't guarantee that there won't be gaps and the sequence object does. B. The IDENTITY property cannot be added to or removed from an existing column; a DEFAULT constraint with a NEXT VALUE FOR function can be added to or removed from an existing column. C. A new identity value cannot be generated before issuing an INSERT statement, where as a sequence value can. D. You cannot provide your own value when inserting a row into a table with an IDENTITY column without special permissions. You can specify your own value for a column that normally gets its values from a sequence object.

B, C, and D A. Incorrect: Both do not guarantee there won't be gaps. B. Correct: One of the advantages of using a sequence object instead of IDENTITY is that you can attach a DEFAULT constraint that has a call to the NEXT VALUE FOR function to an existing column, or remove such a constraint from a column. C. Correct: You can generate a new sequence value before using it by assigning the value to a variable and later using the variable in an INSERT statement. This cannot be done with IDENTITY. D. Correct: You can specify your own value for a column that has an IDENTITY property, but this requires turning on the session option IDENTITY_INSERT, which in turn requires special permissions. The sequence object is more flexible. You can insert your own values into a column that normally gets its value from a sequence object. And that's without needing to turn on any special options and without needing special permissions.

How can you use output parameters in T-SQL stored procedures? (Choose all that apply.) A. You can pass data into a procedure by using an output parameter, but you cannot receive information back from it. B. You can pass data into a procedure by using an output parameter, and any change made to the parameter will be passed back to the calling routine. C. You cannot pass data into a procedure by using an output parameter; it is only used for passing data back to the caller. D. You cannot pass data into a procedure by using an output parameter, nor can you receive data back from a procedure from an output parameter.

B. A. Incorrect: You can use an output parameter to receive information back from a stored procedure. B. Correct: You can both pass data into a stored procedure and retrieve information back from it, by using an output parameter. C. Incorrect: An output parameter is not used only for passing data back to the caller of the stored procedure. It is also used to pass data from the caller to a stored procedure. D. Incorrect: You can both pass data into a stored procedure and retrieve information back from it, by using an output parameter.

Which WHEN clauses are required in a MERGE statement at minimum? A. At minimum, the WHEN MATCHED and WHEN NOT MATCHED clauses are required. B. At minimum, only one clause is required, and it can be any of the WHEN clauses. C. At minimum, the WHEN MATCHED clause is required. D. At minimum, the WHEN NOT MATCHED clause is required.

B. Only one clause is required at minimum, and it can be any of the WHEN clauses.

Which of the following techniques can be used to inject unwanted code into dynamic SQL when user input is concatenated with valid SQL commands? A. Insert a comment string of two dashes, then the malicious code, and then a single quotation mark. B. Insert a single quotation mark, then the malicious code, and then a comment string of two dashes. C. Insert the malicious code followed by a single quotation mark and a comment string of two dashes.

B. The initial single quotation mark terminates the input string, and the final comment removes the effect of the terminating single quotation mark. Then the malicious code can be inserted in between them.

How do you delete rows from a table for which a ROW_NUMBER computation is equal to 1? A. You refer to the ROW_NUMBER function in the DELETE statement's WHERE clause. B. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered DELETE statement against the table expression. C. You use a table expression like a CTE or derived table computing a column based on the ROW_NUMBER function, and then issue a filtered TRUNCATE statement against the table expression. D. The task cannot be achieved.

B. Using a table expression you can create a result column based on the ROW_NUMBER function, and then refer to the column alias in the outer statement's filter.

How does SET XACT_ABORT ON affect a transaction? A. If a T-SQL error with a severity level > 16 occurs, the transaction will be aborted. B. If a T-SQL error with a severity level > 10 occurs, the transaction will be aborted. C. If a T-SQL error with a severity level > 16 occurs, some statements of the transaction may still be executed. D. If a T-SQL error with a severity level > 10 occurs, some statements of the transaction may still be executed.

B. Correct: A T-SQL error with severity level > 10 causes the transaction to be aborted. A. Incorrect: If a T-SQL error with severity level > 16 occurs, the transaction will not be aborted. C. Incorrect: When a transaction is aborted by XACT_ABORT, no other statements in the transaction will be executed. D. Incorrect: When a transaction is aborted by XACT_ABORT, no other statements in the transaction will be executed.

What is the advantage of using THROW in a CATCH block? A. THROW in a CATCH block does not require parameters and so is easier to write. B. THROW re-throws the original error so that the original error can be handled. C. THROW causes an error severity of level 16 automatically. D. The statement before a THROW requires a semicolon.

B. Correct: The THROW statement in a CATCH block can re-throw an error and thereby allow you to report on an error in the TRY block without having to have stored any prior information. This makes it possible to do all error handling in the CATCH block. A. Incorrect: Although it is true that THROW does not take parameters in a CATCH block, that is not necessarily an advantage. C. Incorrect: THROW always results in a severity level of 16, but that is not necessarily an advantage. RAISERROR is more flexible by allowing a range of severity levels. D. Incorrect: Requiring a semicolon on the previous T-SQL statement is perhaps a good coding requirement, but it is not a benefit provided by the THROW command.

Which of the following is the equivalent of <query 1> UNION <query 2> INTERSECT <query 3> EXCEPT <query 4>? A. (<query 1> UNION <query 2>) INTERSECT (<query 3> EXCEPT <query 4>) B. <query 1> UNION (<query 2> INTERSECT <query 3>) EXCEPT <query 4> C. <query 1> UNION <query 2> INTERSECT (<query 3> EXCEPT <query 4>) D. <query 1> UNION (<query 2> INTERSECT <query 3> EXCEPT <query 4>)

B. Without the parentheses, the INTERSECT precedes the other operators, and with the specified parentheses, it's the same.

All the code within the scalar UDF must be enclosed in a _______________.

BEGIN/END block.

Why is it recommended to specify the target column names in INSERT statements?

Because then you don't care about the order in which the columns are defined in the table. Also, you won't be affected if the column order is rearranged due to future definition changes, in addition to when columns that get their values automatically are added.

Where is the injected code inserted?

Between an initial single quotation mark, which terminates the data input string, and a final comment mark, which disables the internal terminating single quotation mark.

Which of the following is true about scalar UDFs? A. Scalar UDFs are both inline and multistatement. B. Scalar UDFs return the result of a SELECT statement. C. Scalar UDFs can be invoked in a SELECT list or a WHERE clause. D. Scalar UDFs can be invoked in the FROM clause of a SELECT statement.

C A. Incorrect: Scalar UDFs are never inline. Only table-valued UDFs can be inline. B. Incorrect: The results of a SELECT statement would be a table, and scalar UDFs do not return tables. C. Correct: You can invoke a scalar UDF in a SELECT list or in the conditions of a WHERE clause, anywhere a scalar value would be valid. D. Incorrect: A FROM clause requires a table and scalar UDFs cannot return tables.

Which database do you have to install in order to enable the Semantic Search feature? A. msdb B. distribution C. semanticsdb D. tempdb

C A. Incorrect: The msdb database is installed by default and is used for SQL Server Agent. B. Incorrect: The distribution database is installed and used by replication. C. Correct: You need the semanticsdb database in order to enable semantic search. D. Incorrect: The tempdb database is installed by default and is used for all temporary objects.

What is the default frame window functions use when a window order clause is specified but an explicit window frame clause isn't? (Choose all that apply.) A. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW B. ROWS UNBOUNDED PRECEDING C. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW D. RANGE UNBOUNDED PRECEDING

C and D A. Incorrect: The default frame is based on the RANGE unit. B. Incorrect: The default frame is based on the RANGE unit. C. Correct: This is the default frame. D. Correct: This is an abbreviated form of the default frame, having the same meaning.

What is the restriction in regard to the table specified as the target of an OUTPUT INTO clause? (Choose all that apply.) A. The table can only be a table variable. B. The table can only be a temporary table. C. The table cannot participate in either side of a foreign key relationship. D. The table cannot have triggers defined on it.

C and D. The target table cannot take part in a foreign key relationship. The target table cannot have triggers defined on it.

Which function returns a table with key phrases associated with the full-text indexed column? A. CONTAINSTABLE() B. FREETEXTTABLE() C. SEMANTICKEYPHRASETABLE() D. SEMANTICSIMILARITYTABLE() E. SEMANTICSIMILARITYDETAILSTABLE()

C. A. Incorrect: Use the CONTAINSTABLE function to rank documents based on proximity of words. B. Incorrect: Use the FREETEXTTABLE function to rank documents based on containment of words. C. Correct: Use the SEMANTICKEYPHRASETABLE function to return key phrases associated with the full-text indexed column. D. Incorrect: Use the SEMANTICSIMILARITYTABLE function to retrieve documents scored by similarity to a specified document. E. Incorrect: Use the SEMANTICSIMILARITYDETAILSTABLE function to return key phrases that are common across two documents.

You want to enforce that a valid supplierid be entered for each productid in the Production.Products table. What is the appropriate constraint to use? A. A unique constraint B. A default constraint C. A foreign key constraint D. A primary key constraint

C. A foreign key constraint validates that a value exists in another table.

What is the result of the WITH CHECK OPTION in a view that has a WHERE clause in its SELECT statement? A. Data can no longer be updated through the view. B. Data can be updated through the view, but primary key values cannot be changed. C. Data can be updated through the view, but values cannot be changed that would cause rows to fall outside the filter of the WHERE clause. D. Data can be updated through the view, but only columns with check constraints can be changed.

C. The purpose of WITH CHECK OPTION is to prevent any updates from causing rows to violate the WHERE clause of the view. It also prevents updating any rows that are outside the WHERE clause filter.

What is the result of WITH SCHEMABINDING in a view? A. The view cannot be altered without altering the table. B. The tables referred to in the view cannot be altered unless the view's SELECT statement is first altered. C. The tables referred to in the view cannot be altered unless the view is first dropped. D. The view cannot be altered unless the tables it refers to are first dropped.

C. WITH SCHEMABINDING implies that the underlying table schemas are fixed by the view. To alter the tables, you must first drop the view. You can always alter a view without altering the underlying table or tables. Even if you alter the view, if WITH SCHEMABINDING is applied to the view, the underlying tables cannot be altered. You never need to drop the tables in order to alter a view.

What kind of dependencies do synonyms have on the objects they refer to? A. Synonyms can be created WITH SCHEMABINDING to prevent the underlying objects from being altered. B. Synonyms can refer to other synonyms. C. Synonyms can be created to refer to database objects that do not yet exist. D. Synonyms can be created without an initial schema name, which can be added later.

C. You can create a synonym that refers to a nonexistent object. In order to use the synonym, however, you must ensure that the object exists.

Name two regular character data types.

CHAR and VARCHAR (use 1 byte of storage per character). Support only one language in addition to English.

returns a value computed over a row of a table; intended for building hash indexes

CHECKSUM()

What are the two forms of the APPLY operator?

CROSS and OUTER.

How do you prepare the DOM presenation of XML

Call the stored procedure -sys.sp_xml_preparedocument

A database has one large table that is searched often based on three different columns, but a user can choose any of the columns and leave the others blank. How can you use stored procedures to make this searching more efficient?

Consider making a search stored procedure that consists of a driver, and have it call sub-procedures, one for each combination of parameter. Those sub-procedures will always have the same query plan, so the procedures will not need to be recompiled.

What is the difference between the COUNT(*) aggregate function and the COUNT(<expression>) general set function? A. COUNT(*) counts rows; COUNT(<expression>) counts rows where <expression> is not NULL. B. COUNT(*) counts columns; COUNT(<expression>) counts rows. C. COUNT(*) returns a BIGINT; COUNT(<expression>) returns an INT. D. There's no difference between the functions.

Correct Answer: A A. Correct: The COUNT(*) function doesn't operate on an input expression; instead, it counts the number of rows in the group. The COUNT(<expression>) function operates on an expression and ignores NULLs. Interestingly, COUNT(<expression>) returns 0 when all inputs are NULLs, whereas other general set functions like MIN, MAX, SUM, and AVG return a NULL in such a case. B. Incorrect: COUNT(*) counts rows. C. Incorrect: COUNT(*) returns an INT. D. Incorrect: Clearly, there is a difference between the functions in the treatment of NULLs.

What is the difference between the result of T1 CROSS APPLY T2 and T1 CROSS JOIN T2 (the right table expression isn't correlated to the left)? A. CROSS APPLY filters only rows where the values of columns with the same name are equal; CROSS JOIN just returns all combinations. B. If T1 has rows and T2 doesn't, CROSS APPLY returns an empty set and CROSS JOIN still returns the rows from T1. C. If T1 has rows and T2 doesn't, CROSS APPLY still returns the rows from T1 and CROSS join returns an empty set. D. There is no difference.

Correct Answer: D A. Incorrect: Both return all combinations. B. Incorrect: Both return an empty set. C. Incorrect: Both return an empty set. D. Correct: Both return the same result when there's no correlation because CROSS APPLY applies all rows from T2 to each row from T1. A CROSS JOIN will always join (combine) every row in table 1 with every row in table 2. You can't specify a condition on a cross join, even if you want to. CROSS APPLY will join every row in table 1 with only rows with matching column values in table 2. But, if you don't specify any columns to do the matching on, every row automatically matches, which causes CROSS APPLY to produce the same result as a CROSS JOIN.

Name four forms of table expressions.

Derived tables, Common table expressions (CTEs), Views, Inline table-valued functions.

What 5 options can be specified with UDFs?

ENCRYPTION As with stored procedures and triggers, this is really an obfuscation of the source code and not a complete encryption. SCHEMABINDING This binds the schemas of all referenced objects. RETURNS NULL ON NULL INPUT If this is set, any NULL parameters cause a scalar UDF to return NULL without executing the body of the function. CALLED ON NULL INPUT This is the default, and it implies that a scalar function body will execute even if NULL is passed as a parameter. EXECUTE AS This executes under various contexts.

What are three variations of syntax to create an INNER JOIN?

FROM T1 INNER JOIN T2 ON T1.id = T2.id or FROM T1 JOIN T2 ON T1.id = T2.id or T1, T2 ON T1.id = T2.id *must include the ON clause.

True or False? When using the PIVOT operator, the COUNT(*) function is allowed as the aggregate function.

False. If you need a count, you have to use the general COUNT(<col name>) aggregate function. A simple workaround is to define a dummy column in the table expression made of a constant, as in 1 AS agg_col, and then in the PIVOT operator apply the aggregate function to that column: COUNT(agg_col).

True or False? If a scalar subquery returns an empty set, the code fails at run time.

False. It is converted to a NULL. A scalar subquery expression is a subquery that returns exactly one column value from one row. The value of the scalar subquery expression is the value of the select list item of the subquery.

True or False? When generating a surrogate key, consider CURRENT_TIMESTAMP.

False. The CURRENT_TIMESTAMP function is simply the standard version of GETDATE, so it also doesn't guarantee uniqueness.

True or False? When using the PIVOT operator, the IN clause accepts a subquery as input.

False. The IN clause of the PIVOT operator accepts a static list of spreading values. It doesn't support a subquery as input. You need to know ahead what the distinct values are in the spreading column and specify those in the IN clause. When the list isn't known ahead, use dynamic SQL to construct and execute the query string after querying the distinct values from the data.

True or False? When working with general set functions, by default, only DISTINCT occurrences are included?

False. With general set functions, work with distinct occurrences by specifying a DISTINCT clause before the expression. Example: SELECT shipperid, COUNT(DISTINCT shippeddate) AS numshippingdates FROM Sales.Orders GROUP BY shipperid;

True or false: you should name constraints in temporary tables

False; because they span sessions/batches and can't have duplicate names

True or false: you can explicitly name constraints in Table Variables

False; it's not allowed

What do 'flags' do OPENXML?

Flags are a third parameter. It allows you to specify the mapping between XML data and relational row set. 1=attribute centric mapping 2=element centric mapping 3=Both mapping

What is the precedence of set operators? (in what order of priority are they evaluated?)

INTERSECT precedes UNION and EXCEPT and UNION and EXCEPT are evaluated from left to right based on their position in the expression. You can always force precedence by using parentheses.

All schema, table, and column names must be valid SQL Server identifiers. What are the min and max lengths for identifiers?

Identifiers must be at least 1 character long and no longer than 128 characters.

What does DBCC DROPCLEANBUFFERS; do?

Invalidates all the data and index pages in memory/cache... forces the pages to be read from disk (Don't want to do this in production but great for learning, tinkering with sql server and seeing what sql server does with execution plans, io statistics.. etc)

A company has a nightly process that first clears all rows from a table by using a DELETE statement, and then populates the table with the result of a query against other tables. The result contains a few dozen million rows. The process is extremely slow. Provide recommendations for improving the insert part of the process.

It could be that it's currently very slow because it doesn't benefit from minimal logging. Evaluate the feasibility of using minimally logged inserts like the SELECT INTO statement (which would require dropping the target table first), the INSERT SELECT statement with the TABLOCK option, and others. Note that the recovery model of the database needs to be simple or bulk logged, so the customer should evaluate whether this is acceptable in terms of the organization's requirements for recovery capabilities.

Does a synonym expose metadata?

No, a user will not see any columns or data types if the synonym refers to a table or view, nor will the user see any parameters if the synonym refers to a procedure or function.

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

No, all table constraints must have unique names within the schema of a database.

Should you use the type FLOAT to represent a product unit price?

No, because FLOAT is an approximate data type and cannot represent all values precisely. Use Decimal

Can readers (shared locks) block readers?

No, because shared locks are compatible with other shared locks.

When using the TOP option, is an ORDER BY clause mandatory?

No, but the query is not deterministic.

Can synonyms reference other synonyms?

No, synonym chaining is not allowed.

When using an ORDER BY clause, is a deterministic result guaranteed?

No, the result could still have nondeterministic ordering. For deterministic ordering, the ORDER BY list must be unique.

Can synonyms be altered?

No, to change a synonym, you must drop and recreate it.

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

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

Can a TRY/CATCH block span batches?

No, you must have one set of TRY/CATCH blocks for each batch of code.

Can you pass parameters to VIEWs?

No.

Can a VIEW reference both permanent and temporary tables?

No. A view can reference only permanent tables; a view cannot reference a temporary table.

Can you add an ORDER BY to the SELECT statement in a view?

No. A view must appear just like a table. Results of a VIEW are NEVER ORDERED. * You can include an ORDER BY in a view only by adding the TOP operator or the OFFSET FETCH clause to the SELECT clause. Even then, the results of the view will not be ordered. Therefore, an ORDER BY in a view, even when you can enter it, is useless.

Is an exclusive lock compatible with a shared lock request?

No. An exclusive lock is also incompatible with a shared lock request.

Do synonyms utilize SCHEMABINDING?

No. If you drop an object in a database, it will be dropped whether or not a synonym references it.

Can a VIEW create a TABLE?

No. In other words, you cannot use the SELECT/INTO syntax in a view.

When using the INSERT VALUES statement, is it mandatory to specify the target column names after the table name?

No. Specifying the target column names after the table name is optional but considered a best practice because it allows you to control the source value to target column association, irrespective of the order in which the columns were defined in the table.

Does the IDENTITY property support cycling?

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

Will a foreign key violation cause a transaction to roll back?

No. To ensure that your transactions behave correctly, you need to add error handling to your code.

Is the NTILE function deterministic when ordering is not unique?

No. To guarantee determinism, define unique ordering.

Does a view store a result set?

No. A view stores only the definition which allows a query to be run.

Can you reference the previous value of a column in the check constraint expression?

No. If you need to do that, you must use a trigger. Example: if you want to enforce a constraint that unitprice cannot be increased by more than 25 percent in any update, you must use a trigger.

If two transactions never block each other, can a deadlock between them result?

No. In order to deadlock, each transaction must already have locked a resource the other transaction wants, resulting in mutual blocking.

Can a semantic search be used in a WHERE clause of a query?

No. Semantic search is available through the table-valued functions only. It does NOT support any specific predicates for the WHERE clause of a query.

When getting OUTPUT from a MERGE statement, how can you differentiate inserts, deletes and updates?

One MERGE statement can apply different actions against the target table. To know which action (INSERT, UPDATE, or DELETE) affected the output row, SQL Server provides you with the $action function. This function returns a string ('INSERT', 'UPDATE', or 'DELETE') indicating the action.

How can you list the check constraints for a table?

Query the sys.check_constraints. SELECT * FROM sys.check_constraints WHERE parent_object_id= OBJECT_ID(N'Production.Products', N'U');

How can you find a database's foreign keys?

Query the sys.foreign_keys table. The following query finds the row for the FK_Products_Categories table: SELECT * FROM sys.foreign_keys WHERE name = 'FK_Products_Categories';

How can you find the unique index that SQL Server uses to enforce a primary key constraint?

Query the sys.indexes table and filter on the constraint name. Example: SELECT * FROM sys.indexes WHERE object_id= OBJECT_ID(N'Production.Categories', N'U') AND name = 'PK_Categories';

What is management studio's output limit?

SQL Server can generate very large dynamic SQL strings, but SQL Server Management Studio will not show more than 8,000 bytes in its output to the Text window, whether you generate the output by using a PRINT statement or a SELECT statement. To show more than 8,000 bytes, you must break up the long string into substrings less than or equal to 8,000 bytes and generate them individually.

When SQL Server is executing a query, what happens in the Parsing stage?

SQL Server checks whether your query is syntactically correct.

What does SQL do, when multiple source rows match one target row during an UPDATE?

SQL Server doesn't generate an error or even a warning. Instead, SQL Server silently performs a nondeterministic UPDATE where one of the source rows arbitrarily "wins."

How many full-text search and how many semantic search functions are supported by SQL Server?

SQL Server supports two full-text search and three semantic search functions.

What does SQL Server use to enforce the isolation of transactions?

SQL Server uses locking to enforce the isolation of transactions.

How does SQL Server enforce uniqueness in both primary key and unique constraints?

SQL Server uses unique indexes to enforce uniqueness for both primary key and unique constraints.

What is the default type used by IDENTITY?

SQL Server will assume BIGINT by default. If you need a different type, you need to ask for it explicitly by adding AS <type> after the sequence name.

What is the name of Plan guides used by the Query Optimizer to match stand-alone queries or queries in ad hoc batches.

SQL plan guides

How can you insert multiple rows using the INSERT VALUES statement?

Separate the rows with commas: INSERT INTO Sales.MyOrders(custid, empid, orderdate, shipcountry, freight) VALUES (2, 11, '20120620', N'USA', 50.00), (5, 13, '20120620', N'USA', 40.00), (7, 17, '20120620', N'USA', 45.00);

What are the two forms of the CASE expression?

Simple form and Searched form. The simple form compares an input expression to multiple possible scalar when expressions, and returns the result expression associated with the first match. If there's no match and an ELSE clause is specified, the else expression is returned. If there's no ELSE clause, the default is ELSE NULL. SELECT productid, productname, unitprice, discontinued, CASE discontinued WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'Unknown' END AS discontinued_desc FROM Production.Products; The searched form of the CASE expression is more flexible. Instead of comparing an input expression to multiple possible expressions, it uses predicates in the WHEN clauses, and the first predicate that evaluates to true determines which when expression is returned. If none is true, the CASE expression returns the else expression. SELECT productid, productname, unitprice, CASE WHEN unitprice < 20.00 THEN 'Low' WHEN unitprice < 40.00 THEN 'Medium' WHEN unitprice >= 40.00 THEN 'High' ELSE 'Unknown' END AS pricerange FROM Production.Products;

What if you don't want to allow NULL in a table column, but you do want to specify some default value to indicate that the column has not yet been populated?

Specify a DEFAULT constraint by adding the DEFAULT clause right after saying NOT NULL. Example: description NVARCHAR(200) NOT NULL DEFAULT ('')

What are SQL Server's two basic types of transactions?

System transactions - used to maintain internal persistent system tables; these types of transactions are not under user control. and User transactions - are created by users in the process of changing or reading data, whether automatically, implicitly, or explicitly.

A problem arises in identifying the rows that are associated with a single grouping set when a grouped column allows NULLs (for example, the shipregion column). How do you tell whether a NULL in the result represents a placeholder (meaning "all regions") or an original NULL from the table (meaning "inapplicable region")?

T-SQL provides two functions to help address this problem: GROUPING and GROUPING_ID. The GROUPING function accepts a single element as input and returns 0 when the element is part of the grouping set and 1 when it isn't. The GROUPING_ID function accepts the list of grouped columns as inputs and returns an integer representing a bitmap. The rightmost bit represents the rightmost input. The bit is 0 when the respective element is part of the grouping set and 1 when it isn't. Each bit represents 2 raised to the power of the bit position minus 1; so the rightmost bit represents 1, the one to the left of it 2, then 4, then 8, and so on. The result integer is the sum of the values representing elements that are not part of the grouping set because their bits are turned on.

How does THROW differ from RAISERROR?

THROW does not use parentheses to delimit parameters. THROW can be used without parameters, but only in the CATCH block of a TRY/CATCH construct. When parameters are supplied, error_number, message, and state are all required. The error_number does not require a matching defined message in sys.messages. The message parameter does not allow formatting, but you can use FORMATMESSAGE() with a variable to get the same effect. The state parameter must be an integer that ranges from 0 to 255. Any parameter can be a variable. With THROW there is no severity parameter; the severity is always set to 16. You can issue RAISERROR using a severity level higher than 16 by using the WITH LOG clause. THROW always terminates the batch except when it is used in a TRY block. RAISERROR does not normally terminate a batch. You cannot issue THROW with a NOWAIT command in order to cause immediate buffer output.

Synonyms can be used for what types of objects?

Tables (including temporary tables) Views User-defined functions (scalar, table-valued, inline) Stored procedures (T-SQL, extended stored procedures, and replication filter procedures) CLR assemblies (stored procedures; table-valued, scalar, and aggregate functions)

What are some difference between temp tables and table variables?

Temporary tables and table variables differ in a number of ways, including scope, DDL and indexing, interaction with transactions, and distribution statistics. Local temporary tables are visible in the level that created them, across batches, and also in inner levels in the call stack. Table variables are visible only to the batch that declared them. You can apply a DDL to a temporary table after it is created, including creating indexes and other DDL changes. You cannot apply DDL changes to a table variable after it is declared. Changes applied to a temporary table in a transaction are undone if the transaction is rolled back. Changes against a table variable are not undone if the user transaction is rolled back. SQL Server maintains distribution statistics on temporary tables but not on table variables. As a result, the plans for queries using temporary tables tend to be more optimized compared to those for queries using table variables.

Describe the ACID properties.

The ACID properties are: Atomicity - Every transaction is an atomic unit of work, meaning that all database changes in the transaction succeed or none of them succeed. Consistency - Every transaction, whether successful or not, leaves the database in a consistent state as defined by all object and database constraints. If an inconsistent state results, SQL Server will roll back the transaction to maintain a consistent state. Isolation - Every transaction looks as though it occurs in isolation from other transactions in regard to database changes. The degree of isolation can vary based on isolation level. Durability - Every transaction endures through an interruption of service. When service is restored, all committed transactions are rolled forward (committed changes to the database are completed) and all uncommitted transactions are rolled back (uncommitted changes are removed).

What does CHOOSE() do?

The CHOOSE function allows you to provide a position and a list of expressions, and returns the expression in the indicated position. The function takes the following form. CHOOSE(<pos>, <exp1>, <exp2>, , <expn>) For example, the expression CHOOSE(2, 'x', 'y', 'z') returns 'y'. Again, its straightforward to replace a CHOOSE expression with a logically equivalent CASE expression; but the point in supporting CHOOSE, as well as IIF, is to simplify migrations from Access to SQL Server as a temporary solution. CHOOSE ( pos, val_1, val_2 [, val_n ] ) CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned. pos is a 1-based index into the list of the items following it. Like the HLookup in Excel

When creating a VIEW in a batch process, what must you be aware of?

The CREATE VIEW statement must be the first statement in a batch. You cannot put other T-SQL statements ahead of it, or make the CREATE VIEW statement conditional by putting it inside an IF statement.

Explain the CROSS APPLY operator.

The CROSS APPLY operator operates on left and right table expressions as inputs. The right table expression can have a correlation to elements from the left table. The right table expression is applied to each row from the left input. If the right table expression returns an empty set for a left row, the left row isn't returned.

What clause accepts a list of expressions as inputs and defines all possible grouping sets that can be generated from the inputs including the empty grouping set?

The CUBE clause. Example: SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders FROM Sales.Orders GROUP BY CUBE( shipperid, YEAR(shippeddate) ); The CUBE clause defines all four possible grouping sets from the two inputs: ( shipperid, YEAR(shippeddate) ) ( shipperid ) ( YEAR(shippeddate) ) ( )

Which of the following are preferred data types? DATE TIME DATETIME DATETIME2 SMALLDATETIME.

The DATE, TIME, and DATETIME2 data types can store data more efficiently and with better precision than DATETIME and SMALLDATETIME.

Can DELETE and TRUNCATE be used if there's a foreign key pointing to the table in question?

The DELETE statement is supported if there's a foreign key pointing to the table in question as long as there are no related rows in the referencing table. TRUNCATE is not allowed if a foreign key is pointing to the tableeven if there are no related rows in the referencing table, and even if the foreign key is disabled.

What permissions are required to use DELETE and TRUNCATE?

The DELETE statement requires DELETE permissions on the target table. The TRUNCATE statement requires ALTER permissions on the target table.

Which function returns the number of distinct ordering values that are lower than the current, plus 1?

The DENSE_RANK function.

What statement is used to remove a full-text catalog?

The DROP FULLTEXT CATALOG statement.

What directive of the FOR XML clause should you use to produce element-centric XML?

The ELEMENTS directive produces element centric XML.

What function computes the respective end of month date for the input date and time value?

The EOMONTH function. This function supports a second optional input indicating how many months to add to the result. New in 2012 EOMONTH ( start_date [, month_to_add ] )

Which set operator performs set difference?

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

What is XML EXPLICIT mode?

The EXPLICIT mode provides very specific control over your XML, but this mode is much more complex to use than the RAW or AUTO modes. It is included in backward compatibility only. It uses proprietary TSQL syntax for formatting XML

Where does the FOR XML clause go?

The FOR XML clause comes after the ORDER BY clause in a query. Use it in the T-SQL SELECT statement

Which predicate usually returns more rows? CONTAINS or FREETEXT?

The FREETEXT predicate is less selective than the CONTAINS predicate, and thus it usually returns more rows than the CONTAINS predicate.

What predicate searches for the values that match the meaning of a phrase and not just exact words?

The FREETEXT predicate.

What clause can be used to list all grouping sets that you want to define in the query?

The GROUPING SETS clause.

SQL Server provides what two features to help you generate a sequence of keys?

The IDENTITY column property and the sequence object.

What column property is defined with a seed and an increment?

The IDENTITY column property.

List limitations of the IDENTITY property.

The IDENTITY property is tied to a particular column in a particular table. You cannot remove an existing property from a column or add it to an existing column. The column has to be defined with the property. Sometimes you need keys to not conflict across different tables. But IDENTITY is table-specific. Sometimes you need to generate the value before using it. With the IDENTITY property, this is not possible. You have to insert the row and only then collect the new value with a function. You cannot update an IDENTITY column. The IDENTITY property doesn't support cycling. A TRUNCATE statement resets the identity value. *The sequence object doesn't suffer from these limitations

What statement enables you to insert the result set (or sets) returned by a dynamic batch or a stored procedure into the specified target table?

The INSERT EXEC statement.

Which function returns an element from the row in the current partition that is a requested number of rows before the current row (based on the window ordering), with 1 assumed as the default offset?

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

What is the difference between NEWID and NEWSEQUENTIALID?

The NEWID function generates GUID values in random order, whereas the NEWSEQUENTIALID function generates GUIDs that increase in a sequential order. This is significant when it is used as a primary key. Having the values in sequential order reduces the number of page splits when adding rows. Function NEWSEQUENTIALID() cannot be used in SQL queries and it can be only used in DEFAULT clause of table. NEWSEQUENTIALID() are predictable, in case of privacy or security use NEWID() instead of NEWSEQUENTIALID().

What function is used to request a new value from the sequence?

The NEXT VALUE FOR function. You can use this function in INSERT and UPDATE statements, DEFAULT constraints, and assignments to variables. Example: SELECT NEXT VALUE FOR Sales.SeqOrderIDs;

With which function can you arrange the rows within the partition in a requested number of equally sized tiles, based on the specified ordering?

The NTILE function. Specify the desired number of tiles as input to the function.

In T-SQL, what option requires an ORDER BY clause?

The OFFSET-FETCH option.

Why is it that Microsoft made it mandatory to specify an ORDER BY clause when using OFFSET-FETCH but not when using TOP? Does this mean that only TOP queries can have nondeterministic ordering?

The ORDER BY clause is mandatory when using OFFSET-FETCH because this clause is standard, and standard SQL decided to make it mandatory. Microsoft followed the standard. The TOP feature is proprietary, and when Microsoft designed it, they chose to allow using TOP in a completely nondeterministic mannerwithout an ORDER BY clause. The fact that OFFSET-FETCH requires an ORDER BY clause doesn't mean you must use deterministic ordering. If the ORDER BY list isn't unique, the ordering isn't deterministic. And if you want the ordering to be completely nondeterministic, specify ORDER BY (SELECT NULL) and then it's equivalent to not specifying an ORDER BY clause at all.

Explain the OUTER APPLY operator.

The OUTER APPLY operator does what the CROSS APPLY operator does, but also includes in the result rows from the left side that get an empty set back from the right side. NULLs are used as placeholders for the result columns from the right side. In other words, the OUTER APPLY operator preserves the left side.

What clause do windows functions use to operate on a set of rows defined for each underlying row?

The OVER clause.

Occasionally, you need to create crosstab reports where you rotate the data from rows to columns or the other way around. So far, you imported data to Microsoft Excel and handled such needs there, but you prefer to do it in T-SQL. What will you consider using for this purpose? What should you be careful about when using the features you're considering?

The PIVOT and UNPIVOT operators are handy for crosstab queries. One thing to be careful about when using PIVOT is related to the fact that the grouping element is determined by elimination what's left from the input table that wasn't specified as either spreading or aggregation elements. Therefore, it is recommended to always define a table expression returning the grouping, spreading, and aggregation elements, and use that table as the input to the PIVOT operator.

Which function returns the number of rows in the partition that have a lower ordering value than the current, plus 1?

The RANK function.

What isolation option (RCSI) of the default isolation level allows read requests to access previously committed versions of exclusively locked data and can greatly reduce blocking and deadlocking?

The READ COMMITTED SNAPSHOT isolation option (RCSI). RCSI is the default isolation level in Windows Azure SQL Database.

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

The ROWS option.

Describe the difference between ROW_NUMBER and RANK.

The ROW_NUMBER function isn't sensitive to ties in the window ordering values. Therefore, the computation is deterministic only when the window ordering is unique. When the window ordering isn't unique, the function isn't deterministic. The RANK function is sensitive to ties and produces the same rank value to all rows with the same ordering value. Therefore, it is deterministic even when the window ordering isn't unique.

Which function computes a unique sequential integer starting with 1 within the window partition based on the window ordering?

The ROW_NUMBER function. Note that if the ordering isn't unique, the ROW_NUMBER function is not deterministic.

What three functions can you use to query the last identity value generated?

The SCOPE_IDENTITY function returns the last identity value generated in your session in the current scope. The @@IDENTITY function returns the last identity value generated in your session regardless of scope. The IDENT_CURRENT function accepts a table as input and returns the last identity value generated in the input table regardless of session.

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

The SYSDATETIME function.

What option allows you to filter a requested number or percent of rows from the query result based on indicated ordering?

The TOP option.

Does DELETE or TRUNCATE attempt to reset an identity property if one is associated with a column in the target table?

The TRUNCATE statement does. If you use TRUNCATE and would prefer not to reset the property, you need to store the current identity value plus one in a variable (using the IDENT_CURRENT function), and reseed the property with the stored value after the truncation.

What is the alternative to a DELETE statement without a WHERE clause?

The TRUNCATE statement. But there are a few differences between the two that need to be considered.

What set operator unifies the results of the two input queries, but doesn't try to eliminate duplicates?

The UNION ALL set operator.

In our system, we have a number of views that use a UNION operator to combine disjoint sets from different tables. We see performance problems when querying the views. Do you have any suggestions to try and improve the performance?

The UNION operator returns distinct rows. When the unified sets are disjoint, there are no duplicates to remove, but the SQL Server Query Optimizer may not realize it. Trying to remove duplicates even when there are none involves extra cost. So when the sets are disjoint, it's important to use the UNION ALL operator and not UNION. Also, adding CHECK constraints that define the ranges supported by each table can help the optimizer realize that the sets are disjoint. Then, even when using UNION, the optimizer can realize it doesn't need to remove duplicates

What set operator unifies the results of the two input queries?

The UNION set operator.

Which of the following operators work in T-SQL views? (Choose all that apply.) The WHERE clause The ORDER BY clause The UNION or UNION ALL operators The GROUP BY clause

The WHERE clause The UNION or UNION ALL operators The GROUP BY clause **A view can contain an ORDER BY if the SELECT TOP clause is used, but no actual sorting of the results is guaranteed.

In the following query, what values will be returned for the GROUPING_ID elements? SELECT GROUPING_ID( shipcountry, shipregion, shipcity ) AS grp_id, shipcountry, shipregion, shipcity, COUNT(*) AS numorders FROM Sales.Orders GROUP BY ROLLUP( shipcountry, shipregion, shipcity );

The bit is 0 when the respective element is part of the grouping set and 1 when it isn't. Each bit represents 2 raised to the power of the bit position minus 1; so the rightmost bit represents 1, the one to the left of it 2, then 4, then 8, and so on. The result integer is the sum of the values representing elements that are not part of the grouping set because their bits are turned on. This query generates the following output (shown here in abbreviated form): grp_id shipcountry shipregion shipcity numorders ----------- --------------- --------------- --------------- ----------- 0 Argentina NULL Buenos Aires 16 1 Argentina NULL NULL 16 3 Argentina NULL NULL 16 ... 0 USA AK Anchorage 10 1 USA AK NULL 10 0 USA CA San Francisco 4 1 USA CA NULL 4 0 USA ID Boise 31 1 USA ID NULL 31 ... 3 USA NULL NULL 122 ... 7 NULL NULL NULL 830 The last row in this output represents the empty grouping setnone of the three elements is part of the grouping set. Therefore, the respective bits (values 1, 2, and 4) are turned on. The sum of the values that those bits represent is 7.

Describe the difference between the ROWS and RANGE window frame units.

The difference between ROWS and RANGE is actually similar to the difference between ROW_NUMBER and RANK, respectively. When the window ordering isn't unique, ROWS doesn't include peers, and therefore it isn't deterministic, whereas RANGE includes peers, and therefore it is deterministic. Also, the ROWS option can be optimized with an efficient in-memory spool; RANGE is optimized with an on-disk spool and therefore is usually slower.

List the benefits of using a SEQUENCE object instead of IDENTITY.

The sequence object is not tied to a particular column in a particular table. You can, if you want, assign a new value by using a DEFAULT constraint. You can add such a constraint to or remove it from an existing column. Because the sequence is an independent object in the database, you can use the same sequence to generate keys that are used in different tables. This way, the keys won't conflict across tables. You can generate a sequence value before using it by storing the result of the NEXT VALUE FOR function in a variable. You can get a range of values for you to allocate as you wish. You can UPDATE columns with the result of the NEXT VALUE FOR function. The sequence object supports cycling. A TRUNCATE statement doesn't reset the current value of a sequence object because the sequence is independent of the tables that use it.

How does SQL Server enforce transactional isolation?

To enforce transactional isolation, SQL Server ensures that when a transaction makes multiple changes to the database, none of the objects being changed by that transaction are allowed to be changed by any other transactions. SQL Server accomplishes transactional isolation by means of locking as well as row versioning. SQL Server locks objects (for example, rows and tables) to prevent other transactions from interfering with that transaction's activity.

A stored procedure will transfer money from one account to another. During that transfer period, neither account can have any data changed, inserted, or deleted for the range of values read by the transaction. What is the appropriate transaction isolation level to accomplish this?

To ensure that, for the range of values read by the transaction, none of the rows being read can be changed and that no new rows may be inserted and none deleted, you can use the SERIALIZABLE isolation level. This is the most restrictive isolation level and can lead to a lot of blocking, so you need to ensure that the transactions complete as quickly as possible.

How does SQL Server maintain atomicity?

To maintain atomicity, SQL Server treats every data DML or DDL command individually and will not allow any command to only partially succeed.

True or False? Set operators consider two NULLs as equal for the purpose of comparison.

True.

True or False? The UNPIVOT operator filters out rows with NULLs in the value column.

True.

True or False? Both TOP and OFFSET-FETCH support expressions as inputs and not just constants.

True.

True or False? When a window frame is applicable to a function but you do not specify an explicit window frame clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

True. For performance reasons, avoid the RANGE option by being explicit with the ROWS clause. Also, if you're after the first row in the partition, using the FIRST_VALUE function with the default frame returns the correct result. However, if you're after the last row in the partition, using the LAST_VALUE function with the default frame won't return what you want because the last row in the default frame is the current row. So with the LAST_VALUE, you must be explicit about the window frame in order to get what you are after. And if you need an element from the last row in the partition, the second delimiter in the frame should be UNBOUNDED FOLLOWING.

True or False? The types of all columns that you're unpivoting must be the same.

True. In terms of data types, the names column is defined as a Unicode character string (NVARCHAR(128)). The values column is defined with the same type as the type of the source columns that were unpivoted. For this reason, the types of all columns that you're unpivoting must be the same.

True or False? When generating a surrogate key, consider NEWID.

True. The NEWID function creates GUIDs in random order. You would consider it when the size overhead is not a major issue and the ability to generate a unique value across time and space, from anywhere, in random order is a higher priority.

True or False? When generating a surrogate key, consider NEWSEQUENTIALID.

True. The NEWSEQUENTIALID function generates GUIDs in increasing order within the machine. It helps reduce fragmentation and works well when a single session loads the data, and the number of drives is small. However, you should carefully consider an alternative using another key generator, like a sequence object, with a smaller type when possible.

True or False? The simple CASE expression compares an input expression to multiple possible expressions in the WHEN clauses, and the searched CASE expression uses independent predicates in the WHEN clauses.

True. The difference between the two is that the simple form compares expressions and the searched form uses predicates.

True or False? The input table of the UNPIVOT operator can be the result of other table operators.

True. The operator operates on the input table that is provided to its left, which could be the result of other table operators, like joins. The outcome of the UNPIVOT operator is a table result that can be used as the input to other table operators that appear to its right.

True or False? An expression that is based on a computation results has no attribute name unless you assign one with an alias.

True. The relational model requires that all attributes have names.

True or False? The attribute type constrains the values to a certain domain of supported values.

True. The type should be considered a constraint because it limits the values allowed. A certain domain of supported values and will not allow values outside that domain. For example, the DATE type allows only valid dates. An attempt to enter something that isnt a date, like 'abc' or '20120230', is rejected. If you have an attribute that is supposed to represent a date, such as birthdate, and you use a type such as INT or CHAR, you dont benefit from built-in validating of dates. An INT type wont prevent a value such as 99999999 and a CHAR type wont prevent a value such as '20120230'.

True or False? Window functions are allowed only in the SELECT and ORDER BY clauses of the query.

True. To refer to those in other clausesfor example, in the WHERE clauseuse a table expression such as a CTE. Invoke the window function in the inner query's SELECT clause, assigning the expression with a column alias. Then refer to that column alias in the outer query's WHERE clause.

True or False? It is a bad practice to use * in the SELECT list.

True. Using * in the SELECT list is considered a bad practice.

True or False? Using window aggregate functions to perform computations such as running totals, you typically get much better performance compared to using joins or subqueries and group aggregate functions.

True. Window functions lend themselves to good optimizationespecially when using UNBOUNDED PRECEDING as the first delimiter.

How many OUTPUT clauses can a single statement have?

Two one with INTO and one without INTO.

How many WHEN MATCHED clauses can a single MERGE statement have?

Two one with an UPDATE action and one with a DELETE action.

What two functions can be used in trigger code to get information about what is going on? (as far as columns being changed)

UPDATE() and COLUMNS_UPDATED() UPDATE() - is used to determine whether a particular column has been referenced by an INSERT or UPDATE statement. Example, insert the following inside the trigger. IF UPDATE(qty) PRINT 'Column qty affected'; The following statement would make UPDATE(qty) true. UPDATE Sales.OrderDetails SET qty = 99 WHERE orderid= 10249 AND productid= 16; The UPDATE() function returns true even if the column value is set to itself in an UPDATE statement. It is only testing whether the column is referenced. COLUMNS_UPDATED() - can be used if you know the sequence number of the column in the table. It requires you to use the bitwise AND operation (&) to see whether a column was updated.

How do you determine which action affected the OUTPUT row in a MERGE statement?

Use the $action function.

Which is a more specific predicate, CONTAINS or FREETEXT?

Use the CONTAINS predicate for more specific searches.

Since the NOLOCK table hint is deprecated and will not be allowed in UPDATE and DELETE statements in a future version of SQL Server, what should you use instead?

Use the READUNCOMMITTED table hint instead.

How can you get an XSD schema together with an XML document from your SELECT statment?

Use the XMLSCHEMA directive in the FOR XML clause

When working with an OUTPUT clause, how can you filter rows that you want to store in a table?

Use the composable DML feature to filter output rows that you want to store in a target table.

What is something you can do to avoid MERGE conflicts?

Use the hint SERIALIZABLE or HOLDLOCK (both have equivalent meanings) against the target. Example: MERGE INTO Sales.MyOrders WITH (HOLDLOCK) AS TGT ...

How can you check which languages are supported in SQL Server?

Use the query: SELECT lcid, name FROM sys.fulltext_languages ORDER BY name;

What are UDAs?

User Defined Aggregates. SQL Server 2012 allows you to create user defined aggregates (UDA) using .NET code based on the Common Language Runtime (CLR). SQL Server 2012 provides some built-in CLR UDAs for the spatial data types GEOMETRY and GEOGRAPHY and also allows you to create new UDAs operating on spatial types as inputs.

Which of the following T-SQL statements can be used to cause branching within a stored procedure? (Choose all that apply.) WHILE BEGIN/END IF/ELSE GO

WHILE IF/ELSE Correct: A WHILE statement starts a looping structure. Incorrect: BEGIN and END do not cause branching. They are only used to group statements together. Correct: IF and ELSE cause code execution to branch based on a condition in the IF clause. Incorrect: A GO statement is just a batch terminator. It has no effect on code execution as such.

What 4 VIEW options are available?

WITH ENCRYPTION WITH SCHEMABINDING WITH VIEW_METADATA WITH CHECK OPTION * You can add any combination of the first three options.

If you execute the DBCC SHOW_STATISTICS command without the ___ option, you get all statistics information, including the header and the density vector

WITH HISTOGRAM

What is the recommended form for a PIVOT query?

WITH PivotData AS ( SELECT < grouping column >, < spreading column >, < aggregation column > FROM < source table > ) SELECT < select list > FROM PivotData PIVOT( < aggregate function >(< aggregation column >) FOR < spreading column > IN (< distinct spreading values >) ) AS P;

When working with VIEWs, what does WITH SCHEMABINDING do?

WITH SCHEMABINDING binds the view to the table schemas of the underlying tables: The table cannot have its schema definitions changed unless the view is dropped. This protects the view from having table structures changed and breaking the view.

When working with VIEWs, what does WITH VIEW_METADATA do?

WITH VIEW_METADATA, when specified, returns the metadata of the view instead of the base table.

Is it better to use CACHE or NO CACHE with a SEQUENCE object?

With some caching, the performance is much better. There's a very big performance difference between using NO CACHE versus CACHE <some value>. With NO CACHE, SQL Server has to write to disk for every request of a new sequence value.

What does the FORMAT() function do?

With the FORMAT function, you can format an input value based on a format string, and optionally specify the culture as a third input where relevant. You can use any format string supported by the .NET Framework... As in the String.Format() function. New/Available in 2012

What does IIF() do

With the IIF function, you can return one value if an input predicate is true and another value otherwise. The function has the following form. IIF(<predicate>, <true_result>, <false_or_unknown_result>) This expression is equivalent to the following. CASE WHEN <predicate> THEN <true_result> ELSE <false_or_unknown_result> END Basically this is equivalent to C#'s ? ternary operator Exp1 ? Exp2_If_True : Exp3_If_False;

How do you obtain a new value from a sequence?

With the NEXT VALUE FOR function.

What is the difference between structured and unstructured error handling?

With unstructured error handling, you must handle each error as it happens by accessing the @@ERROR function. With structured error handling, you can designate a central location (the CATCH block) to handle errors.

What is the difference between the result of a query with and one without an ORDER BY clause?

Without an ORDER BY clause, the result is relational (from an ordering perspective); with an ORDER BY clause, the result is conceptually what the standard calls a cursor.

How do you write processing instructions in XML?

Write processing instructions like an element but with a question mark at the beginning and end of the tag. examples: <?PItarget data?> ,<?display table-view?> <?sort alpha-ascending?> , <?textinfo whitespace is allowed ?> , <?elementnames <fred>, <bert>, <harry> ?>

X query and order dates

X query considers orderdate to be a sequence, not an atomic value

What can be queried to find the state of the transaction?

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

What can XPath be used for?

Xpath can only be used for navigation.

What is Xquery?

Xquery is a standard language for browsing XML instances and returning XML. Xquery can be used for navigation, looping over nodes, shape the returned XML instance, and more

What date format is always interpreted as ymd, regardless of your language?

YYYYMMDD.

Can XML elements be nested?

Yes

When using foreign keys, can the value of the foreign key be duplicated in the first (child) table?

Yes, a value in the first (child) table may be duplicated, but in the second (parent) table where you look up the corresponding value, it must be unique.

Can the ORDER BY list include elements that are not part of the SELECT list?

Yes, as long as the result rows would have normally been allowed there.

Can readers block writers (exclusive locks)?

Yes, even if only momentarily, because any exclusive lock request has to wait until the shared lock is released.

If your session is in the READ COMMITTED isolation level, is it possible for one of your queries to read uncommitted data?

Yes, if the query uses the WITH (NOLOCK) or WITH (READUNCOMMITTED) table hint. The session value for the isolation level does not change, just the characteristics for reading that table.

Is ORDER BY (SELECT NULL) valid?

Yes, it could be used as a way to signal that you intentionally want an arbitrary result.

Can a table or column name contain spaces, apostrophes, and other nonstandard characters?

Yes, table and column names can be delimited identifiers that contain nonstandard characters.

Is there a way to prevent readers from blocking writers and still ensure that readers only see committed data?

Yes, that is the purpose of the READ COMMITTED SNAPSHOT option within the READ COMMITTED isolation level. Readers see earlier versions of data changes for current transactions, not the currently uncommitted data.

Are column aliases assigned in the SELECT clause visible to the ORDER BY clause?

Yes.

Can a stored procedure call a function or another stored procedure?

Yes.

When working with VIEWs can expressions be used to create new columns?

Yes.

Can using ORs be detrimental to query performance?

Yes. Having multiple conditions in a predicate connected with the OR operator lowers the possibility for SQL Server to use indexes. You should consider rewriting the predicate to a logically equivalent predicate that uses the AND operator

Does the INSERT EXEC statement accept multiple queries?

Yes. INSERT EXEC works even when the source dynamic batch or stored procedure has more than one query as long as all queries return result sets that are compatible with the target table definition.

Can synonyms reference objects on linked servers?

Yes. Synonyms can refer to objects in other databases, in addition to objects referenced by linked servers making it possible to simplify queries by removing the need for three-part and four-part references.

Can you use column aliases when working with the OUTPUT clause?

Yes. The OUTPUT clause is designed like the SELECT clause, allowing you to form expressions and assign the result columns with column aliases.

When creating a unique constraint on a column, can you allow NULL in that column?

Yes. The unique constraint does not require the column to be NOT NULL. You can allow NULL in a column and still have a unique constraint, but only one row can be NULL. * The primary key constraint does not allow NULLs.

Can transactions span batches?

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

Can VIEWs refer to other views?

Yes. Views can refer to tables or views in other databases and in other servers via linked servers.

Can a VIEW contain more than one SELECT statement?

Yes. You can combine SELECT statements that return the same result sets by using a UNION or UNION ALL clause in the SELECT statement. A view must return only one result set so the view can always appear to most SQL statements as though it were a table.

Can primary key and unique constraints be created on computed columns?

Yes. You can create both primary key and unique constraints on computed columns.

Can a SELECT statement be involved in a deadlock?

Yes. If the SELECT statement locks some resource that keeps a second transaction from finishing, and the SELECT cannot finish because it is blocked by the same transaction, the deadlock cycle results.

If you don't supply a database schema name when you create a table, will SQL Server allow you to create the table?

Yes. If you don't supply a database schema name when you create a table, SQL Server will supply one based on your database user name's default schema.

Does xQuery support conditional?

Yes. Xquery supports if then, and else

What syntax would you use to modify a row and also collect the result of the modified columns into variables?

You can achieve this using a single UPDATE statement. Example: DECLARE @newdiscount AS NUMERIC(4, 3) = NULL; UPDATE Sales.MyOrderDetails SET @newdiscount = discount += 0.05 WHERE orderid= 10250 AND productid= 51; SELECT @newdiscount;

Would you recommend using views, inline functions, or synonyms? You need to be able to change the names of tables or views without having to recode the application.

You can change the names or definitions of views and change table names without affecting the application if the application refers to synonyms. You will have to drop and recreate the synonym when the underlying table or view has a name change, and that will have to be done when the application is offline.

What query allows you to check which filters are installed in your instance?

You can check which filters are installed in your instance by using the query: EXEC sys.sp_help_fulltext_system_components 'filter'; You can also check which filters are installed in your instance by querying the sys.fulltext_document_types catalog view: SELECT document_type, path FROM sys.fulltext_document_types;

What types of views are available in T-SQL?

You can create 1) regular views, which are just stored SELECT statements, or 2) indexed views, which actually materialize the data, in addition to 3) partitioned views.

What are the two types of DML triggers that can be created?

You can create AFTER and INSTEAD OF DML-type triggers.

What two options are available to inline table-valued functions? (as in WITH __________ )

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

Most of the tables have a surrogate key, which you have implemented as a primary key. However, there are other columns or combinations of columns that must be unique, and a table can have only one primary key. How can you enforce that certain other columns or combinations of columns will be unique?

You can create a unique constraint on a column or set of columns to ensure their unique values, in addition to the primary key.

What are the two types of table-valued UDFS?

You can create inline or multistatement table-valued UDFs.

How can you ensure that certain combinations of columns in a table have a unique value?

You can ensure that certain columns or combinations of columns in a table are unique by applying primary key and unique constraints. You can also apply a unique index. Normally, it is preferable to use the declared primary key and unique constraints because they are easy to find and recognize within the SQL Server metadata and management tools. If the uniqueness of a row cannot be specified using a constraint or a unique index, you may be able to use a trigger.

What are the different ways you can futz with the statistics that Sql Server maintains?

You can get information about statistics by querying the -sys.stats catalog view -sys.stats_columns catalog view -DBCC SHOW_ STATISTICS command. -CREATE, DROP, and UPDATE statistics commands alow you to manually maintain statistics -sys.sp_updatestats system procedure to manually update statistics for all tables in a database You can also get information like when the statistics were last updated by using the STATS_ DATE() system function.

Several columns allow NULLs, even though the application is supposed to always populate them. How can you ensure that those columns will never allow NULLs?

You can prevent a column from ever having NULLs by altering the table and redefining the column as NOT NULL.

List a couple of advantages of using implicit transactions.

You can roll back an implicit transaction after the command has been completed. Because you must explicitly issue the COMMIT statement, you may be able to catch mistakes after the command is finished.

What is one way you can navigate with XQuery?

You can specify a path absolutley or relatively from the current node

What types of table compression are available?

You can use either page or row compression on a table. Page compression includes row compression.

Would you recommend using views, inline functions, or synonyms? The application needs to filter report data on the database by passing parameters, but the developers do not want to use stored procedures for retrieving the data.

You can use inline functions to provide view-like objects that can be filtered by parameters. Stored procedures are not required because users can reference the inline function in the FROM clause of a query.

How does sp_executesql help stop SQL injection?

You can use sp_executesql to parameterize user input, which can prevent any injected code from being executed.

Client developers would like to put parameters on views but T-SQL doesn't allow them. What can you use in place of parameterized views?

You can use table-valued functions in place of views, and define parameters to match the requirements of the application developers. You can then call the function from inside a stored procedure that accepts those parameters and send the results back to the client.

How can you get an estimated execution plan in XML format for further analysis?

You can use the SET SHOWPLAN_XML command.

How can you add custom error messages?

You can use the system stored procedure sp_addmessage to add your own custom error messages.

How can you ensure that all tables have a primary key, even tables that right now do not have any primary key declared?

You cannot actively enforce every table to have a primary key constraint. However, you can query sys.key_constraints to monitor the tables to make sure that every table does include a primary key.

Can you customize the error messages from check constraints and triggers?

You cannot customize the error message from a check constraint. You can if you implemented the constraint using a trigger.

List some limitations of using SET XACT_ABORT ON.

You cannot trap for the error or capture the error number. Any error with severity level > 10 causes the transaction to roll back. None of the remaining code in the transaction is executed. Even the final PRINT statements of the transaction are not executed. After the transaction is aborted, you can only infer what statements failed by inspecting the error message returned to the client by SQL Server.

How do you search for synonyms of a word with the CONTAINS predicate?

You have to use the CONTAINS(FTcolumn, 'FORMSOF(THESAURUS, SearchWord1)') syntax.

If you want to create an index or primary key or constraint on a temp table, what do you have to watch out for?

You have to watch out for name clashes. Don't provide a specific name, allow Sql Server to generate it. As it turns out, constraint names are considered object names in the schema, and object names must be unique per schemanot per table. So two tables cannot have constraints with the same name in the same schema. Temporary tables are created in tempdb in the dbo schema.

When using TOP, how can deterministic results be guaranteed if more than the number of requested rows meet the criteria?

You have two options: 1) include all ties with the last row by adding the WITH TIES option ex. SELECT TOP (3) WITH TIES 2) break the ties by adding a tiebreaker that makes the ordering unique.

If the clause CUBE(a, b, c) defines eight grouping sets and the clause ROLLUP(x, y, z) defines four grouping sets, by specifying a comma between the two, as in CUBE(a, b, c), ROLLUP(x, y, z), how many grouping sets do you end up with?

You multiply them and get 32 grouping sets. You can specify multiple GROUPING SETS, CUBE, and ROLLUP clauses in the GROUP BY clause separated by commas. By doing so, you achieve a multiplication effect.

What is used to end an explicit transaction?

You must end the transaction at some point by committing it or rolling it back. To commit a transaction, issue the COMMIT TRANSACTION command, which you can also write as COMMIT TRAN, COMMIT WORK, or just COMMIT. To roll back a transaction, issue the ROLLBACK TRANSACTION command, or alternatively, ROLLBACK TRAN, ROLLBACK WORK, or just ROLLBACK.

List a few rules associated with using WITH MARK in restoring the database

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

As a database developer on a key project for your company, you have been asked to refactor a set of stored procedures in your production database server. You have observed that the stored procedures have practically no error handling, and when they do have it, it is ad hoc and unstructured. None of the stored procedures are using transactions. When should you recommend using a different isolation level?

You need to adapt the isolation levels to the requirements for transactional consistency. You should investigate the current application and the database for instances of blocking and especially deadlocking. If you find deadlocks, and establish that they are not due to mistakes in T-SQL coding, you can use various methods of lowering the isolation level in order to make deadlocks less likely. However, be aware that some transactions may require higher levels of isolation.

As a database developer on a key project for your company, you have been asked to refactor a set of stored procedures in your production database server. You have observed that the stored procedures have practically no error handling, and when they do have it, it is ad hoc and unstructured. None of the stored procedures are using transactions. When should you recommend using a different isolation level?

You need to adapt the isolation levels to the requirements for transactional consistency. You should investigate the current application and the database for instances of blocking and especially deadlocking. If you find deadlocks, and establish that they are not due to mistakes in T-SQL coding, you can use various methods of lowering the isolation level in order to make deadlocks less likely. However, be aware that some transactions may require higher levels of isolation.

As a database developer on a key project for your company, you have been asked to refactor a set of stored procedures in your production database server. You have observed that the stored procedures have practically no error handling, and when they do have it, it is ad hoc and unstructured. None of the stored procedures are using transactions. What type of error handling should you recommend?

You should use TRY/CATCH blocks in every stored procedure where errors might occur, and encourage your team to standardize on that usage. By funneling all errors to the CATCH block, you can handle errors in just one place in the code.

Which of the following OFFSET-FETCH options are valid in T-SQL? (Choose all that apply.) a. SELECT ... ORDER BY orderid OFFSET 25 ROWS b. SELECT ... ORDER BY orderid FETCH NEXT 25 ROWS ONLY c. SELECT ... ORDER BY orderid OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY d. SELECT ... <no ORDER BY> OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY

a. Correct: T-SQL supports indicating an OFFSET clause without a FETCH clause. b. Incorrect: Contrary to standard SQL, T-SQL does not support a FETCH clause without an OFFSET clause. c. Correct: T-SQL supports indicating both OFFSET and FETCH clauses. d. Incorrect: T-SQL does not support OFFSET-FETCH without an ORDER BY clause.

Which syntax is recommended to use for cross joins and inner joins, and why? a. The syntax with the JOIN keyword because it's consistent with outer join syntax and is less prone to errors. b. The syntax with the comma between the table names because it's consistent with outer join syntax and is less prone to errors. c. It is recommended to avoid using cross and inner joins. d. It is recommended to use only lowercase characters and omit default keywords, as in join instead of INNER JOIN because it increases energy consumption.

a. Correct: The syntax with the JOIN keyword is consistent with the only standard syntax available for outer joins and is less prone to errors.

What is the difference between the ON clause and the WHERE clause? a. The ON clause uses two-valued logic and the WHERE clause uses three-valued logic. b. The ON clause uses three-valued logic and the WHERE clause uses two-valued logic. c. In outer joins, the ON clause determines filtering and the WHERE clause determines matching. d. In outer joins, the ON clause determines matching and the WHERE clause determines filtering.

a. Incorrect: Both clauses use three-valued logic. b. Incorrect: Both clauses use three-valued logic. c. Incorrect: ON determines matching and WHERE determines filtering. d. Correct: ON determines matching and WHERE determines filtering.

You execute a query with TOP (3) WITH TIES and nonunique ordering. Which of the following options most accurately describes how many rows will be returned? a. Fewer than three rows b. Three rows or fewer c. Three rows d. Three rows or more e. More than three rows f. Fewer than three, three, or more than three rows

a. Incorrect: If there are at least three rows in the query result without TOP, the query will return at least three rows. b. Incorrect: If there are more than three rows in the result, as well as ties with the third row, the query will return more than three rows. c. Incorrect: If there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are more than three rows in the result, as well as ties with the third row, the query will return more than three rows. d. Incorrect: If there are fewer rows than three in the query result without TOP, the query will return only those rows. e. Incorrect: If there are three rows or less in the query result without TOP, the query won't return more than three rows. f. Correct: If there are fewer rows than three in the query result without TOP, the query will return only those rows. If there are at least three rows in the result and no ties with the third, the query will return three rows. If there are more than three rows in the result, as well as ties with the third row, the query will return more than three rows.

You want result rows to be sorted by orderdate descending, and then by orderid, descending. Which of the following clauses gives you what you want? a. ORDER BY orderdate, orderid DESC b. ORDER BY DESC orderdate, DESC orderid c. ORDER BY orderdate DESC, orderid DESC d. DESC ORDER BY orderdate, orderid

c. The correct syntax is to specify DESC after each expression whose ordering direction needs to be descending.

How do you read a graphical execution plan? a. From top to bottom, from left to right B. From top to bottom, from right to left c. From left to right, from top to bottom D. From right to left, from top to bottom

correct answer: D a. incorrect: You read execution plans from right to left, from top to bottom. B. incorrect: You read execution plans from right to left, from top to bottom. c. incorrect: You read execution plans from right to left, from top to bottom. D. correct: You read execution plans from right to left, from top to bottom.

What is the most important drawback of DMOs? a. You must have enough data collected from the last restart of SQL Server. B. DMOs are complex to use. c. DMOs are not available in the Standard edition of SQL Server. D. You have to recreate DMOs before each analysis.

correct answer: a a. correct: Not having enough data is the most important drawback of DMOs. B. incorrect: Although some queries that use DMOs can become quite complex, you can easily overcome this by learning more about T-SQL and DMOs. c. incorrect: DMOs are available in all editions. D. incorrect: DMOs are system objects; you cannot drop or create them.

Which of the following is not a reason to update statistics manually? a. You just rebuilt an index. B. You bulk-inserted a large amount of data to a table and want to query this table immediately after the insert. c. You upgraded the database. D. Query execution times are slow; however, you know that the queries are written correctly and supported with appropriate indexes.

correct answer: a a. correct: When you rebuild an index, SQL Server updates the statistics automatically. B. incorrect: You should update statistics for a table after you bulk-inserted a large amount of data to the table and want to query this table immediately. c. incorrect: You should update statistics for the complete database after an upgrade. D. incorrect: You should update statistics when queries execute slowly and you know that the queries are written correctly and supported with appropriate indexes.

When you fetch rows from a cursor, how do you know when there are no more rows to fetch? a. When the @@FETCH_STATUS function returns 0 B. When the @@FETCH_STATUS function returns -1 c. When the @@FETCH_STATUS function returns -2 D. When the @@FETCH_STATUS function generates an error

correct answer: b a. incorrect: 0 means that the last fetch was successful. There could be more rows. B. correct: -1 means that the row is beyond the result set. c. incorrect: -2 means that the row fetched is missing. There still could be more rows. D. incorrect: The function shouldnt generate any errors.

In which phase of query execution does SQL Server check whether the objects referred to by the query exist? a. In the parsing phase B. In the binding phase c. In the optimization phase D. In the execution phase

correct answer: b a. incorrect: In the parsing phase, SQL Server checks for syntax correctness. B. correct: SQL Server resolves object names and binds them to logical operators in the binding phase. c. incorrect: In the optimization phase, SQL Server generates candidate plans and selects the execution plan. D. incorrect: In the execution phase, SQL Server executes the query and caches the execution plan.

How can you find the text of the query executed by using DMOs? a. This info is provided in the sys.dm_exec_query_stats dynamic management view. B. By querying the sys.dm_exec_sql_text dynamic management function. c. The sys.dm_exec_query_plan dynamic management function returns the query text. D. You cannot find the query text through DMOs.

correct answer: b a. incorrect: There is no query text provided from the sys.dm_exec_query_stats DMO. B. correct: You can get the query text by querying the sys.dm_exec_sql_text dynamic management function. c. incorrect: There is no query text provided from the sys.dm_exec_query_plan DMO. D. incorrect: You can get the query text by querying the sys.dm_exec_sql_text dynamic management function.

How can you support the SELECT clause of a query by using a nonclustered index that is already used for the WHERE clause? a. You could use SELECT *. B. You could modify the index that is already used to include the columns from the select list that are not part of the key. c. You could add column aliases. D. There is no way to support the SELECT clause with indexes.

correct answer: b a. incorrect: Using SELECT * is a very bad practice and of course does not help SQL Server to use indexes at all. B. correct: You could modify the index that is already used to include the columns from the SELECT list that are not part of the key. c. incorrect: Adding column aliases has no inuence on index usage. D. incorrect: You can support the SELECT clause with indexes.

When you need to operate on one row at a time, what are the alternatives to using a cursor? a. Using the FOR EACH looping construct. B. Retrieving the minimum and maximum keys, and then looping with a counter that starts with the minimum and keeps being incremented by 1 in each iteration until it reaches the maximum. c. Using a TOP (1) query ordered by the key to fetch the frst row. Then use a loop while the last key returned is not NULL. In each iteration of the loop, process the current row and then use a TOP (1) query where the key is greater than the last, ordered by the key, to fetch the next row. D. Defne a per-row SELECT trigger.

correct answer: c a. incorrect: T-SQL doesnt support a FOR EACH loop. B. incorrect: In case there are gaps between keys, this approach will result in an attempt to treat nonexistent keys. c. correct: This approach with the TOP option does give you a correct alternative to a cursor. However, you need to think about the fact that it is more I/O-intensive. D. incorrect: There are no SELECT triggers or per-row triggers in T-SQL.

What is the row locator when a table is stored as a balanced tree? a. RID. B. Columnstore index key. c. Clustering key. D. A table is never stored as a balanced tree.

correct answer: c a. incorrect: RID is used for heaps. B. incorrect: Columns in a columnstore index are not used as row locators. c. correct: The clustering key is the row locator when a table is stored as a balanced tree. D. incorrect: A clustered table is stored as a balanced tree.

How many clustered indexes can you create on a table? a. 999 B. 16 c. 1 D. 900

correct answer: c a. incorrect: You can create up to 999 nonclustered indexes on a table. B. incorrect: You can have up to 16 columns in a composite key. c. correct: There can be only one clustered index, because this is the table itself, organized as a balanced tree. D. incorrect: The size of the columns in a key must not exceed 900 bytes.

Which SET session options are useful for query optimization? (Choose all that apply.) a. SET STATISTICS IO B. SET STATISTICS EXECUTION_DETAILS c. SET IDENTITY_INSERT D. SET STATISTICS TIME

correct answers: a and D a. correct: The SET STATISTICS IO session option is useful for analyzing query performance. B. incorrect: There is no SET STATISTICS EXECUTION_DETAILS option. c. incorrect: You use the SET IDENTITY_INSERT option to provide a value for the column that has an identity property. D. correct: The SET STATISTICS TIME session option is useful for analyzing query performance.

Why is it important to prefer set-based solutions for querying tasks instead of iterative ones? (Choose all that apply.) a. Because set-based solutions are based on the relational model, which is the foundation of T-SQL B. Because set-based solutions always provide better performance than iterative solutions c. Because set-based solutions usually involve less code than iterative solutions D. Because set-based solutions enable you to rely on the order of data

correct answers: a and c a. correct: Set-based solutions are based on principles from the relational model, and this model is the foundation of SQL (the standard language) and T-SQL (the dialect in SQL Server). B. incorrect: Although it is not common, sometimes iterative solutions are faster than set-based ones. c. correct: Because set-based solutions are declarative and iterative solutions are imperative, set-based solutions tend to involve less code. D. incorrect: Set-based solutions cannot make any assumptions regarding the order of the data because sets are unordered.

You create an index to support the WHERE clause of a query. However, SQL Server does not use the index. What are the possible reasons? (Choose all that apply.) a. The arguments in the predicate are not searchable. B. SQL Server does not consider using an index to support the WHERE clause. c. The predicate is not selective enough. D. You are in the context of the tempdb database, and SQL Server does not use indexes in this database.

correct answers: a and c a. correct: SQL Server does not use an index to support the WHERE clause if the arguments in the predicate are not searchable. B. incorrect: SQL Server supports the WHERE clause with indexes. c. correct: SQL Server might decide not to use an index to support the WHERE clause if the query is not selective enough. D. incorrect: SQL Server considers using indexes in the context of the tempdb database just like in the context of any other database.

Which of the following cases is suitable for using table variables? (Choose all that apply.) a. When the tables are very small and the plan is trivial B. When the tables are very small and the plan is nontrivial c. When the tables are large and the plan is trivial D. When the tables are large and the plan is nontrivial

correct answers: a, b, and c a. correct: Table variables are suitable when the tables are very small. B. correct: Table variables are suitable when the tables are very small. c. correct: When the plan is trivial, table variables are still suitable even if they are large. D. incorrect: When the tables are large and the plan is nontrivial, temporary tables are preferable.

What levels can an index have? (Choose all that apply.) a. Intermediate level B. Heap level c. Root level D. Leaf level

correct answers: a, c, and D a. correct: An index can have zero or more intermediate levels. B. incorrect: A heap is a separate structure, not a level of an index. c. correct: Every index has the root level, with a single root page. D. correct: The lowest level of an index is the leaf level.

Which commands turn on an XML plan? (Choose all that apply.) a. SET EXECUTION_XML ON B. SET SHOWPLAN_XML ON c. SET XML PLAN ON D. SET STATISTICS XML ON

correct answers: b and D a. incorrect: There is no SET EXECUTION_XML command. B. correct: You use the SET SHOWPLAN_XML command to turn on the estimated XML plans. c. incorrect: There is no SET XML PLAN command. D. correct: You use the SET STATISTICS XML command to turn on the actual XML plans.

What are the actions of the optimization phase of query execution? (Choose all that apply.) a. Generation of the algebrized tree B. Generation of candidate plans c. Selection of the best candidate plan D. Caching the plan e. Query execution

correct answers: b and c a. incorrect: An algebrized tree is generated in the binding phase. B. correct: In the optimization phase, SQL Server generates candidate plans. c. correct: During the optimization phase, SQL Server selects an execution plan from the set of candidate plans. D. incorrect: The plan is cached in the execution phase. e. incorrect: A query is executed in the execution phase.

A __________ _________ means that Sql Server can find all data needed for the query in a nonclustered index and does not need to do any lookups in the base table.

covering index

What algorithm is used if the input for the aggregation is not ordered and the input is so big that sorting would be inefficient

hash aggregation

This algorithm uses function to split the rows from the smaller input into buckets; then scans all bucket rows

hash join

This algorithm is best used if none of the inputs is supported by an index and an equijoin predicate is used

hash joins

What phase of the Hash join uses a hash function to split the rows from the smaller input into buckets (phase 1)?

hash phase

A table-valued UDF with a single line of code is called an _______________________.

inline table-valued UDF.

What is the Sys proc to get the parameterized form of a query; especially useful to get the parameterized query for the TEMPLATE plan guide

sys.sp_get_query_template

Use these two things to get information about statistics by querying (2)

sys.stats, sys.stats_columns

What data type should be used to store a date with a time?

DATETIME2 (6-8 bytes) or SMALLDATETIME (4 bytes)

If you don't specify a value for a column during an insert, what will SQL Server do?

1) SQL Server will first check whether the column gets its value automatically for example, from an IDENTITY property or a default constraint. 2) If that's not the case, SQL Server will check whether the column allows NULLs, in which case it will assume a NULL. 3) If that's not the case, SQL Server will generate an error.

How do you create an inline table-valued function?

1) Specify parameters. Parameters are optional, but the parentheses that would enclose parameters are not optional. 2) Add the clause RETURNS TABLE to signal to SQL Server that this is a table-valued function. 3) Following the AS block, enter a single RETURN statement. This acts like an internal function to return the embedded SELECT statement. 4) Embed the SELECT statement that will define what you want the function to return as a rowset to the caller. 5) The semicolon following the last parenthesis is optional, but if present, it must follow the closing parenthesis. Example: USE TSQL2012; GO IF OBJECT_ID (N'Sales.fn_OrderTotalsByYear', N'IF') IS NOT NULL DROP FUNCTION Sales.fn_OrderTotalsByYear; GO CREATE FUNCTION Sales.fn_OrderTotalsByYear (@orderyear int) RETURNS TABLE AS RETURN ( SELECT orderyear, qty FROM Sales.OrderTotalsByYear WHERE orderyear = @orderyear ); GO Query the function and pass the year you want to see, as follows: SELECT orderyear, qty FROM Sales.fn_OrderTotalsByYear(2007);

What are three requirements when creating a primary key on a column?

1) The column or columns cannot allow NULL. If the column or columns allow NULL, the constraint command will fail. 2) Any data already in the table must have unique values in the primary key column or columns. If there are any duplicates, the ALTER TABLE statement will fail. 3) There can be only one primary key constraint at a time in a table. If you try to create two primary key constraints on the same table, the command will fail.

You need to analyze some Microsoft Word documents to find the documents that are semantically similar to a document that you get from your manager. You need to provide a quick and simple solution for this problem. 1. Would you create a Microsoft .NET application or use T-SQL queries for this problem? 2. If you decide to use a T-SQL solution, which T-SQL function would you use?

1. A T-SQL solution is simpler in this scenario because the SQL Server Full-Text Search and Semantic Search features support the functionality you need out of the box. 2. Use the SEMANTICSIMILARITYTABLE function.

What can be queried to find the level of transaction?

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

Which form of the proximity term defines the distance and the order? A. NEAR((SearchWord1, SearchWord2), 5, TRUE) B. NEAR((SearchWord1, SearchWord2), CLOSE, ORDER) C. NEAR((SearchWord1, SearchWord2), 5) D. NEAR(SearchWord1, SearchWord2)

A A. Correct: This proximity term defines both distance and order of searched terms. B. Incorrect: This is not a valid syntax. C. Incorrect: This proximity term defines distance of searched terms only. D. Incorrect: This proximity term does not define either distance or order of searched terms.

What is used to mark the start of an explicit transaction in your code?

A T-SQL BEGIN TRANSACTION statement. The actual command can also be written as BEGIN TRAN.

What are the two types of parameters for a T-SQL stored procedure?

A T-SQL stored procedure can have input and output parameters.

X_Which of the following T-SQL statements automatically occur in the context of a transaction? (Choose all that apply.) A. An ALTER TABLE command B. A PRINT command C. An UPDATE command D. A SET command

A and C A. Correct: An ALTER TABLE command is a DDL command that changes metadata and always executes as a transaction. B. Incorrect: A PRINT command does not change data, and therefore does not execute by itself in a transaction. C. Correct: An UPDATE statement changes data and executes as a transaction. D. Incorrect: A SET statement only affects session settings and does not change data, and therefore does not execute as a transaction.

What occurs when ONE session has an exclusive lock on a resource, preventing another session from obtaining any kind of lock on the resource?

A block.

What happens when a scalar subquery returns more than one value? A. The query fails at run time. B. The first value is returned. C. The last value is returned. D. The result is converted to a NULL.

A. The query fails at run time, indicating that more than one value is returned.

In relational databases, what acronym is used to describe the properties of transactions?

ACID. Atomicity, Consistency, Isolation, Durability.

What are the three database options that influence the automatic creation of statistics

AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS, AUTO_UPDATE_STATISTICS_ASYNC,

When turned on, enables SQL Server to automatically update statistics when there are enough changes in the underlying tables and indexes

AUTO_UPDATE_STATISTICS

This Option determines whether SQL Server uses synchronous or asynchronous statistics updates during query optimization

AUTO_UPDATE_STATISTICS_ASYNC

Which rows from the target table get deleted by a DELETE statement without a WHERE clause?

All target table rows.

What is the difference between an Expression and a Statement?

An expression can be evaluated, and as such returns a single value. It is only one possible part of a statement. Column names, variables, constants, functions and formulas using any of the preceding are all expressions. Certain subqueries that return only a single value may also be considered expressions. A statement defines control of flow, data manipulation, or data definition operations. A statement is a collection of elements such as identifiers, reserved keywords, data types, functions, expressions, operators and comments, to make the smallest possible unit of code. A statement can be executed.

What is the default transaction management mode?

Autocommit. In the autocommit mode, you do not issue any surrounding transactional commands such as BEGIN TRAN, ROLLBACK TRAN, or COMMIT TRAN. Further, the @@TRANCOUNT value (for the user session) is not normally detectable for that command, though it would be in a data modification statement trigger.

How do the COMMIT and ROLLBACK commands work with nested transactions in T-SQL? (Choose all that apply.) A. A single COMMIT commits the entire nested transaction. B. A single ROLLBACK rolls back the entire nested transaction. C. A single COMMIT commits only one level of the nested transaction. D. A single ROLLBACK rolls back only one level of the nested transaction.

B A. Incorrect: A single COMMIT commits only the innermost level of the transaction and will not commit the entire nested transaction. B. Correct: A single ROLLBACK will roll back the entire outer transaction of a nested transaction. C. Incorrect: A single COMMIT commits data only at the outermost level of a nested transaction. D. Incorrect: A single ROLLBACK does not roll back just one level of the transaction; instead, it rolls back the entire transaction.

What do the RANK and DENSE_RANK functions compute? A. The RANK function returns the number of rows that have a lower ordering value (assuming ascending ordering) than the current; the DENSE_RANK function returns the number of distinct ordering values that are lower than the current. B. The RANK function returns one more than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one more than the number of distinct ordering values that are lower than the current. C. The RANK function returns one less than the number of rows that have a lower ordering value than the current; the DENSE_RANK function returns one less than the number of distinct ordering values that are lower than the current. D. The two functions return the same result unless the ordering is unique.

B A. Incorrect: These definitions are one less than the correct ones. B. Correct: These are the correct definitions. C. Incorrect: These definitions are two less than the correct ones. D. Incorrect: The opposite is truethe two functions return the same result when the ordering is unique.

How can you update a table, setting a column to the result of a window function? A. By using an UPDATE based on a join B. By using an UPDATE based on a table expression C. By using an UPDATE with a variable D. The task cannot be achieved.

B With an UPDATE based on table expressions, you can invoke a window function in the inner query's SELECT list. You can then refer to the alias you assigned to the result column in the outer UPDATE statement's SET clause.

When using THROW and not RAISERROR, why must THROW with or without parameters be the last statement in the CATCH block?

Because it terminates the batch and does not execute any remaining commands in the CATCH block.

A company has a nightly process that first clears all rows from a table by using a DELETE statement, and then populates the table with the result of a query against other tables. The result contains a few dozen million rows. The process is extremely slow. Provide recommendations for improving the delete part of the process.

Because the entire table needs to be cleared, consider using the TRUNCATE statement, which is minimally logged.

In SQL Server when sorting NULLs, do they sort before or after non-NULLs (in ASC order)?

Before.

How can you enter implicit transaction mode?

By issuing the following command: SET IMPLICIT_TRANSACTIONS ON; You can also issue the following command (this command just effectively issues the first command for you): SET ANSI_DEFAULTS ON;

How can you get a list of default constraints?

By querying sys.default_constraints. The following query finds all the default constraints for the Production.Products table: SELECT * FROM sys.default_constraints WHERE parent_object_id= OBJECT_ID(N'Production.Products', N'U');

How is it possible to refer to the result of a window function in any clause that is evaluated before the SELECT clause?

By using a table expression. Invoke the window function in the SELECT clause of the inner query and assign the expression with a column alias. Then you can refer to that column alias in the outer query in all clauses.

Which sentence best describes the difference between an inline table-valued UDF and a multistatement table-valued UDF? A. An inline table-valued UDF defines the schema of a table variable, with column names and data types, and inserts data into the table variable. B. An inline table-valued UDF defines the schema of a permanent table, with column names and data types, and then inserts data into that table. C. A multistatement table-valued UDF defines the schema of a table variable, with column names and data types, and inserts data into the table variable. D. A multistatement table-valued UDF defines the schema of a permanent table, with column names and data types, and then inserts data into that table.

C A. Incorrect: An inline table-valued UDF does not define the schema of the table structure it returns. B. Incorrect: An inline table-valued UDF cannot create a permanent table. C. Correct: A multistatement table-valued UDF defines an explicit schema of a table variable, and then inserts data into the table variable. D. Incorrect: A multistatement table-valued UDF cannot create a permanent table.

How do the inserted and deleted tables work with a DML statement in an AFTER trigger? A. For a DELETE statement, the inserted table contains new rows and the deleted table contains the deleted rows. B. The inserted table only contains rows from the INSERT statement, and the deleted table contains only rows from the DELETE statement. C. For an INSERT statement, the inserted table contains new rows and the deleted table is empty. D. For an UPDATE statement, the inserted table is empty and the deleted table contains all the changed rows.

C A. Incorrect: In the case of a DELETE statement, there are no new or changed rows, so the inserted table is empty. B. Incorrect: The inserted and deleted tables also contain rows for the UPDATE statement, not just the INSERT and DELETE statements. C. Correct: An INSERT statement has all inserted rows in the inserted table but no rows in the deleted table. D. Incorrect: For an UPDATE statement that updates rows in a table, the rows being changed will be in the inserted table with their new values, and in the deleted table with their old values.

Which clause of the MERGE statement isn't standard? A. The WHEN MATCHED clause B. The WHEN NOT MATCHED clause C. The WHEN NOT MATCHED BY SOURCE clause D. All MERGE clauses are standard.

C. The WHEN NOT MATCHED BY SOURCE clause isn't standard.

What function supports a style number representing the conversion style?

CONVERT.

What general set functions are supported by T-SQL?

COUNT SUM AVG MIN MAX * General set functions are applied to an expression and ignore NULLs.

What is the difference between COUNT(ColumnName) and COUNT(*)?

COUNT(ColumnName) ignores NULLs and therefore the counts are less than or equal to those produced by COUNT(*).

What is the basic syntax for adding table compression?

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

What is the restriction that grouped queries impose on your expressions? A. If the query is a grouped query, you must invoke an aggregate function. B. If the query has an aggregate function, it must have a GROUP BY clause. C. The elements in the GROUP BY clause must also be specified in the SELECT clause. D. If you refer to an element from the queried tables in the HAVING, SELECT, or ORDER BY clauses, it must either appear in the GROUP BY list or be contained by an aggregate function.

Correct Answer: D A. Incorrect: You can group rows without invoking an aggregate function. B. Incorrect: A query can have an aggregate function without a GROUP BY clause. The grouping is implied all rows make one group. C. Incorrect: There's no requirement for grouped elements to appear in the SELECT list, though it's common to return the elements that you group by. D. Correct: A grouped query returns only one row per group. For this reason, all expressions that appear in phases that are evaluated after the GROUP BY clause (HAVING, SELECT, and ORDER BY) must guarantee returning a single value per group. That's where the restriction comes from.

What T-SQL functions construct a desired date and time value from its numeric parts?

DATEFROMPARTS, TIMEFROMPARTS, DATETIMEFROMPARTS, DATETIME2FROMPARTS, SMALLDATETIMEFROMPARTS, and DATETIMEOFFSETFROMPARTS.

Which of the following are true about table-valued UDFs? A. Table-valued UDFs can return scalar values or tables. B. Table-valued UDFs always involve multiple T-SQL statements. C. Table-valued UDFs can be invoked in a SELECT list or a WHERE clause. D. Table-valued UDFs can be invoked in the FROM clause of a SELECT statement.

D A. Incorrect: Table-valued UDFs only return tables. B. Incorrect: Inline table-valued UDFs consist of only one T-SQL statement. Even multistatement table-valued UDFs only require one T-SQL statement. C. Incorrect: Invoking in a SELECT list or a WHERE clause would require a scalar value, and table-valued UDFs only return tables. D. Correct: The FROM clause requires a table and table-valued UDFs return tables.

What is the data type of the target values column in the result of an UNPIVOT operator? A. INT B. NVARCHAR(128) C. SQL_VARIANT D. The data type of the source columns that you unpivot

D A. Incorrect: The type of the values column is not necessarily always an INT. B. Incorrect: The type of the values column is not necessarily always an NVARCHAR(128)that's the case with the names column. C. Incorrect: The type of the values column is not SQL_VARIANT. D. Correct: The type of the values column is the same as the type of the columns that you unpivot, and therefore they must all have a common type.

You are tasked with implementing a trigger. As part of the triggers code in specifc conditions, you need to roll back the transaction. However, you need to copy the data from the inserted and deleted tables in the trigger into audit tables to keep track of what was supposed to be changed. How can you achieve this? a. Roll back the transaction, and then copy the data from the inserted and deleted tables into the audit tables. B. Copy the data from the inserted and deleted tables into the audit tables and then roll back the transaction. c. Copy the rows from the inserted and deleted tables into temporary tables, roll back the transaction, and then copy the data from the temporary tables into the audit tables. D. Copy the rows from the inserted and deleted tables into table variables, roll back the transaction, and then copy the data from the table variables into the audit tables.

D a. incorrect: After you roll back the transaction in the trigger, the inserted and deleted tables are emptied. B. incorrect: The rollback causes the copying to the audit tables to be undone. c. incorrect: Changes against temporary tables are undone after you roll back a transaction. D. correct: Changes against table variables arent undone if you roll back a transaction, so this solution works correctly.

How can you turn off nested triggers on a SQL Server instance by using T-SQL? A. Use the sp_configure stored procedure followed by 'nested triggers' and 'OFF'. B. Use the sp_configure stored procedure followed by 'nested triggers' and 0. C. Use the sp_configure stored procedure followed by 'nested triggers' and 'OFF', followed by the RECONFIGURE statement. D. Use the sp_configure stored procedure followed by 'nested triggers' and 0, followed by the RECONFIGURE statement.

D After issuing the sp_configure stored procedure followed by 'nested triggers' and 0, you must also execute the RECONFIGURE statement.

What data type should be used to store a date without a time?

DATE (3 bytes)

What T-SQL date and time functions support addition and differences?

DATEADD and DATEDIFF.

In which case out of the following are you normally not allowed to specify the target column in an INSERT statement? A. If the column has a default constraint associated with it B. If the column allows NULLs C. If the column does not allow NULLs D. If the column has an IDENTITY property

D. A. Incorrect: If you want, you are allowed to not specify the column and let the default constraint generate the value, but it's not like you have to skip it. If you want, you can indicate your own value. B. Incorrect: Again, if you want, you are allowed to not specify the column and let SQL Server assign a NULL to the column, but it's not like you have to skip it. If you want, you can indicate your own value. C. Incorrect: If the column doesn't allow NULLs and doesn't somehow get its value automatically, you actually must specify it. D. Correct: If the column has an IDENTITY property, you must normally skip it in the INSERT statement and let the property assign the value. To provide your own value, you need to turn on the IDENTITY_INSERT option, but that's not what happens normally.

Which function can be used to find the document that is most semantically similar to a specified document? A. CONTAINSTABLE() B. FREETEXTTABLE() C. SEMANTICKEYPHRASETABLE() D. SEMANTICSIMILARITYTABLE() E. SEMANTICSIMILARITYDETAILSTABLE()

D. A. Incorrect: Use the CONTAINSTABLE function to rank documents based on proximity of words. B. Incorrect: Use the FREETEXTTABLE function to rank documents based on containment of words. C. Incorrect: Use the SEMANTICKEYPHRASETABLE function to return key phrases associated with the full-text indexed column. D. Correct: Use the SEMANTICSIMILARITYTABLE function to retrieve documents scored by similarity to a specified document. E. Incorrect: Use the SEMANTICSIMILARITYDETAILSTABLE function to return key phrases that are common across two documents.

How do you read a graphical execution plan? A. From top to bottom, from left to right B. From top to bottom, from right to left C. From left to right, from top to bottom D. From right to left, from top to bottom

D. You read execution plans from right to left, from top to bottom.

What is the correct form for a regular character string literal?

Delimit the literal with single quotation marks; for example, 'literal'.

What is so important about a function being deterministic or not?

Deterministic functions can be used in indexed views and computed columns whereas nondeterministic functions cannot. There are several properties of user-defined functions that determine the ability of the SQL Server Database Engine to index the results of the function, either through indexes on computed columns that call the function, or through indexed views that reference the function. The determinism of a function is one such property. For example, a clustered index cannot be created on a view if the view references any nondeterministic functions.

In which operator does the order of the input queries matter? UNION UNION ALL INTERSECT EXCEPT

EXCEPT

True or False? When using the PIVOT operator, the results of expressions can be used for the aggregation and spreading elements.

False. The aggregation and spreading elements cannot directly be results of expressions; instead, they must be column names from the queried table. You can, however, apply expressions in the query defining the table expression, assign aliases to those expressions, and then use the aliases in the PIVOT operator.

True or False? The attribute type prevents duplicates.

False. The type itself doesn't prevent duplicates. If you need to prevent duplicates, you use a primary key or unique constraint.

True or False? When generating a surrogate key, consider GETDATE.

False. There's no assurance that GETDATE will generate unique values; therefore, it's not a good choice to generate keys.

True or False? All identifiers must be delimited.

False. When an identifier is "regular," delimiting it is optional. An irregular identifier must be delimited, for example, "2006" or [2006].

What is the difference in logging DELETE and TRUNCATE statements?

For DELETE, SQL Server records in the log the actual data that was deleted. For TRUNCATE, SQL Server records information only about which pages were deallocated. As a result, the TRUNCATE statement tends to be substantially faster.

How does SQL Server enforce consistency?

For consistency, SQL Server ensures that all constraints in the database are enforced.

Give an example for creating an inline table-valued function.

IF OBJECT_ID('HR.GetManagers', 'IF') IS NOT NULL DROP FUNCTION HR.GetManagers; GO CREATE FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE AS RETURN WITH EmpsCTE AS ( SELECT empid, mgrid, firstname, lastname, 0 AS distance FROM HR.Employees WHERE empid= @empid UNION ALL SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance FROM EmpsCTE AS S JOIN HR.Employees AS M ON S.mgrid= M.empid ) SELECT empid, mgrid, firstname, lastname, distance FROM EmpsCTE; GO To use the function: SELECT * FROM HR.GetManagers(9) AS M;

Give an example of the syntax used to drop a VIEW.

IF OBJECT_ID(N'Sales.OrderTotalsByYear', N'V') IS NOT NULL DROP VIEW Sales.OrderTotalsByYear;

If the mode of a parameter in a procedure or function is not declared, will the default mode be INPUT, OUTPUT, or INPUT/OUTPUT?

INPUT.

When checking values with a MERGE statement and NULLs are possible, what does your logic need to look like for compares?

If NULLs are possible in the data, you need to add logic to deal with those, and consider a case when one side is NULL and the other isn't as true. For example, if the custid column allowed NULLs, you would use the following predicate: TGT.custid <> SRC.custid OR (TGT.custid IS NULL AND SRC.custid IS NOT NULL) OR (TGT.custid IS NOT NULL AND SRC.custid IS NULL).

What does creating a constraint WITH CHECK imply?

If there is any data in the table already, and if there would be violations of the constraint, then the ALTER TABLE will fail.

Why is it recommended to prepare a table expression for the pivot operator returning only the three elements that should be involved in the pivoting task?

If you query the underlying table directly, all columns from the table besides the aggregation and spreading columns will implicitly become your grouping elements. By defining a table expression, you control which columns will be used as the grouping columns.

What is the default full-text language?

If you use a localized version of SQL Server, SQL Server Setup sets the default full-text language to the language of your instance, if the language is supported on your instance. If the language is not supported, or if you use a nonlocalized version of SQL Server, the default full-text language is English.

Can database schemas be nested?

No. There can be only one level of database schema; one schema cannot contain another schema.

When using an OUTPUT clause to return updated data, what do the prefixes inserted and deleted represent?

In an UPDATE statement, inserted represents the state of the rows after the update and deleted represents the state before the update

When are you limited in the ORDER BY list to only elements that appear in the SELECT list?

In cases when the DISTINCT clause is used.

Where does SQL Server search for synonyms?

In the thesaurus files located in SQL_Server_install_path\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\FTDATA\.

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

Indexed views.

How does XACT_ABORT behave differently when used in a TRY block?

Instead of terminating the transaction as it does in unstructured error handling, XACT_ABORT transfers control to the CATCH block, and as expected , any error is fatal. The transaction is left in an uncommittable state (and XACT_STATE() returns a -1). Therefore, you cannot commit a transaction inside a CATCH block if XACT_ABORT is turned on; you must roll it back.

Is this query valid or invalid? SELECT S.shipperid, S.companyname, COUNT(*) AS numorders FROM Sales.Shippers AS S JOIN Sales.Orders AS O ON S.shipperid= O.shipperid GROUP BY S.shipperid;

Invalid. This query generates the following error. Msg 8120, Level 16, State 1, Line 1 Column 'Sales.Shippers.companyname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Because the S.companyname column neither appears in the GROUP BY list nor is it contained in an aggregate function, it's not allowed in the HAVING, SELECT, and ORDER BY clauses.

How are literals of regular character strings delimited?

Literals of regular character strings are delimited with single quotation marks, as in 'abc'.

What is the visibility of a temp table? (#TempOrders)

Local temporary tables are visible throughout the level that created them, across batches, and in all inner levels of the call stack. So if you create a temporary table in a specifc level in your code and then execute a dynamic batch or a stored procedure, the inner batch can access the temporary table.

When working with transactions, what are Savepoints?

Locations within transactions that you can use to roll back a selective subset of work. You can define a savepoint by using the SAVE TRANSACTION <savepoint name> command. The ROLLBACK statement must reference the savepoint. Otherwise, if the statement is unqualified, it will roll back the entire transaction.

How many database schemas can one user own?

Many.

What is the name of Plan guides used by the Query Optimizer to match queries inside stored procedures, scalar UDFs, multistatement table-valued UDFs, and DML triggers.

OBJECT plan guides

You can create the following types of plan guides (3)

OBJECT plan guides, SQL plan guides, TEMPLATE plan guides

What is the syntax for using OFFSET-FETCH?

OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY; Either of the keywords NEXT or FIRST can be used. Either ROW or ROWS can be used.

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

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

Which is standard? OFFSET-FETCH and/or TOP?

OFFSET-FETCH is standard. The TOP option is a proprietary T-SQL feature.

What are some objects that cannot be referenced in T-SQL by using variables?

Objects that you cannot use variables for in T-SQL commands include : -database name in a USE statement, -table name in a FROM clause, -column names in the SELECT and WHERE clauses, -lists of literal values in the IN() and PIVOT() functions.

How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed?

One COMMIT for each level of the nested transaction. Only the last COMMIT actually commits the entire transaction.

An application has a monthly process that archives data that it needs to purge. Currently, the application first copies data that needs to be deleted to the archive table in one statement and then deletes those rows in another statement. Both statements use a filter that is based on a date column called dt. You need to filter the rows where dt is earlier than a certain date. The problem is that sometimes rows representing late arrivals are inserted into the table between the copying and the deletion of rows, and the deletion process ends up deleting rows that were not archived. Can you suggest a solution to the problem with the archiving process that prevents deleting rows that were not archived?

One option is to work with the SERIALIZABLE isolation level, handling both the statement that copies the rows to the archive environment and the statement that deletes the rows in one transaction. But a simpler solution is to do both tasks in one statementa DELETE with an OUTPUT INTO clause. This ensures that only rows that are copied to the archive table are deleted. And if for whatever reason the copying of the rows to the archive table fails, the delete operation also fails, because both activities are part of the same transaction.

What kinds of locks are compatible with each other?

Only shared locks are compatible with each other. An exclusive lock is not compatible with any other kind of lock.

What are the options within the OVER clause that the different types of window functions support?

Partitioning, ordering, and framing clauses.

What is the correct form for a Unicode character string literal?

Prefix the literal with a capital N and delimit the literal with single quotation marks; for example, N'literal'.

The RAISERROR command uses what syntax?

RAISERROR ( { msg_id | msg_str | @local_variable } { ,severity ,state } [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ] The message (a message ID, string, or string variable), along with the severity and state, are required.

List the six most commonly used isolation levels.

READ COMMITTED - the default isolation level, READ UNCOMMITTED - a SELECT statement could read uncommitted data that was changed during a transaction and then later was rolled back to its initial state. This is called reading dirty data, READ COMMITTED SNAPSHOT - default isolation level in Windows Azure SQL Database; option is set at the database level and is a persistent database property, REPEATABLE READ - set per session; the transaction may see new rows added after its first read; this is called a phantom read, SNAPSHOT - is enabled as a persistent database property and then set per transaction, SERIALIZABLE - is the strongest level and is set per session.

An application must often insert data into a main table and several subsidiary tables in the same action, making the application code very complex. What can you recommend as a way of moving some of that complexity into the database and out of the application?

Recommend that the database developers use an INSTEAD OF trigger to execute. In that trigger, multiple inserts can be made before inserting into the main table.

What query lists the primary key constraints in a database?

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

What command lists unique constraints in a database?

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

What function returns a table with key phrases that are common across two documents?

SEMANTICSIMILARITYDETAILSTABLE SEMANTICSIMILARITYDETAILSTABLE ( table, source_column, source_key, matched_column, matched_key ) Define the source document with the source_key, which is the unique key from the index used in the KEY INDEX clause of the CREATE FULLTEXT INDEX statement, and with source_column, which is the name of the full-text indexed column.

What function returns a table with documents scored by semantic similarity to the searched document specified with the source_key parameter?

SEMANTICSIMILARITYTABLE SEMANTICSIMILARITYTABLE ( table, { column | (column_list) | * }, source_key ) The source_key parameter specifies the unique key from the index used in the KEY INDEX clause of the CREATE FULLTEXT INDEX statement. Use this function to find which documents are the most similar to a specified document.

You can make an entire batch fail if any error occurs by beginning it with _______________________________.

SET XACT_ABORT ON. You set XACT_ABORT per session. After it is set to ON, all remaining transactions in that setting are subject to it until it is set to OFF.

Name the two major types of UDF (user-defined functions).

Scalar and table-valued. The scalar function returns a single value back to the caller, whereas the table-valued function returns a table. Both scalar UDFs and table-valued UDFs can consist of a single line of T-SQL code, or of multiple lines.

Can you point out the advantages of using set operators like INTERSECT and EXCEPT compared to the use of inner and outer joins?

Set operators have a number of benefits. They allow simpler code because you don't explicitly compare the columns from the two inputs like you do with joins. Also, when set operators compare two NULLs, they consider them the same, which is not the case with joins. When this is the desired behavior, it is easier to use set operators. With join, you have to add predicates to get such behavior.

What are the two general modes of locking? (What are the two kinds of locks)

Shared locks Used for sessions that read datathat is, for readers Exclusive locks Used for changes to datathat is, writers

Does SQL support using joins in an UPDATE?

Standard SQL doesn't support using joins in UPDATE statements, but T-SQL does.

What function allows you to extract from an input date and time value a desired part, such as a year, minute, or nanosecond, and return the extracted part as an integer?

The DATEPART function.

Which function returns an element from the row that is in the requested offset after the current row?

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

What clause is an abbreviation of the GROUPING SETS clause, used when there's a hierarchy formed by the input elements?

The ROLLUP clause. Example: SELECT shipcountry, shipregion, shipcity, COUNT(*) AS numorders FROM Sales.Orders GROUP BY ROLLUP( shipcountry, shipregion, shipcity ); Results: shipcountry shipregion shipcity numorders --------------- --------------- --------------- ----------- Argentina NULL Buenos Aires 16 Argentina NULL NULL 16 Argentina NULL NULL 16 ... USA AK Anchorage 10 USA AK NULL 10 USA CA San Francisco 4 USA CA NULL 4 USA ID Boise 31 USA ID NULL 31 ... USA NULL NULL 122 ... NULL NULL NULL 830

What directive of the FOR XML clause should you use to specify the root element?

The ROOT directive specifies the name of the root element

What are the 3 main parts of a CTE?

The name you assign to the query and its columns. The inner query. The outer query. First name the CTE, then specify the inner query, and then the outer query. WITH <CTE_name> AS ( <inner_query> ) <outer_query>;

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

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

Name two requirements for the queries involved in a set operator.

The number of columns in the two queries needs to be the same. Corresponding columns need to have compatible types.

Define logical reads.

The number of pages read from the data cache. When you read a whole table as in the queries from the example, this number gives you an estimate about table size.

Define physical reads.

The number of pages read from the disk. This number is lower than the actual number of pages because many pages are cached.

How do you guarantee the order of the rows in the result of a query?

The only way to do so is by adding an ORDER BY clause.

In the following query what is the parent_object_id? SELECT * FROM sys.check_constraints WHERE parent_object_id= OBJECT_ID(N'Production.Products', N'U');

The parent_object_id is the object_id of the table to which the check constraint belongs.

When SQL Server is executing a query, what happens in the Execution stage?

The physical execution of the execution plan.

Name two ways to concatenate strings in T-SQL.

The plus (+) operator, and the CONCAT function.

When using NTILE what happens when there is a remainder?

The remainder tiles are assigned with an additional row.

How can you enforce that all columns that contain values from lookup tables are valid?

To enforce that lookup values are valid, you should normally use foreign key constraints. Foreign key constraints are declared constraints, and as such are known through metadata to SQL Server and the query optimizer. When joining a table that has a foreign key constraint to its lookup table, it is helpful to add an index on the foreign key column to assist join performance.

You notice that almost all data validation against a database occurs in the client software. Bugs in the client software have caused database inconsistency, and you want to refactor the system by using stored procedures to help protect the database. What steps can be taken to prevent duplicates or inconsistencies on unique keys and mismatched foreign keys? How can you present a standard interface from the application code to the database?

To prevent inconsistency in the database, ensure that the proper constraints are in place: primary key and unique key constraints on tables, check constraints on columns, and foreign key constraints between tables. Other more complex business rules can be enforced by using triggers. To present a standard interface to the database, use data tier stored procedures that is, use standard insert, update, and delete stored procedures for every table. The client software should only be allowed to change data in tables by using those stored procedures.

Which of the following operators removes duplicates from the result? (Choose all that apply.) UNION UNION ALL INTERSECT EXCEPT

UNION INTERSECT EXCEPT

T-SQL supports what three set operators?

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

What is the syntax for a standard UPDATE statement?

UPDATE <target table> SET <col 1> = <expression 1>, <col 2> = <expression 2>, ..., <col n> = <expression n> WHERE <predicate>;

What are the possible actions in the WHEN MATCHED clause?

UPDATE and DELETE.

When using the OFFSET-FETCH option, how can you return an arbitrary list?

Use ORDER BY (SELECT NULL).

Which of the following is the preferred data type? ROWVERSION TIMESTAMP

Use ROWVERSION instead of the deprecated TIMESTAMP.

Most important clause in a query that can benefit from an index

WHERE clause

A stored procedure will transfer money from one account to another. During that transfer period, neither account can have any data changed, inserted, or deleted for the range of values read by the transaction.

What is the appropriate transaction isolation level to accomplish this? To ensure that, for the range of values read by the transaction, none of the rows being read can be changed and that no new rows may be inserted and none deleted, you can use the SERIALIZABLE isolation level. This is the most restrictive isolation level and can lead to a lot of blocking, so you need to ensure that the transactions complete as quickly as possible.

As a database developer on a key project for your company, you have been asked to refactor a set of stored procedures in your production database server. You have observed that the stored procedures have practically no error handling, and when they do have it, it is ad hoc and unstructured. None of the stored procedures are using transactions.

What type of error handling should you recommend? You should use TRY/CATCH blocks in every stored procedure where errors might occur, and encourage your team to standardize on that usage. By funneling all errors to the CATCH block, you can handle errors in just one place in the code.

How can using a wildcard in a LIKE predicate affect query performance?

When the LIKE pattern starts with a known prefix for example, col LIKE 'ABC%' SQL Server can potentially efficiently use an index on the filtered column; in other words, SQL Server can rely on index ordering. When the pattern starts with a wildcard for example, col LIKE '%ABC%'SQL Server cannot rely on index ordering anymore. When looking for a string that starts with a known prefix (say, ABC) make sure you use the LIKE predicate, as in col LIKE 'ABC%', because this form is considered a search argument. Applying manipulation to the filtered column prevents the predicate from being a search argument. For example, the form LEFT(col, 3) = 'ABC' isn't a search argument and will prevent SQL Server from being able to use an index efficiently.

In what case is more than just the VIEW's definition stored in the database?

When you create a unique clustered index on a view and materialize the data. The actual results of the view query are stored on disk, in the clustered index structure.

How would you force SQL Server to recompile a stored procedure?

When you create it... use: WITH RECOMPILE

You will use T-SQL scripts to deploy new objects such as tables, views, or T-SQL code to a database. If any kind of T-SQL error occurs, you want the entire deployment script to quit. How can you accomplish this without adding complex error handling?

When you deploy new database objects by using T-SQL scripts, you can wrap the batches in a single transaction and use SET XACT_ABORT ON right after the BEGIN TRANSACTION statement. Then if any T-SQL error occurs, the entire transaction will abort and you will not have to add complex error handling.

What permissions are required to be able to use the IDENTITY_INSERT option on a table?

You need to be the owner of the table or have ALTER permissions on the table.

If you want to change the definition of a constraint or the definition of a computed column, what do you need to do?

You need to drop the constraint or column with the old definition and add the constraint or computed column back in with the new definition.

Which keywords can be omitted in the new standard join syntax without changing the meaning of the join? (Choose all that apply.) a. JOIN b. CROSS c. INNER d. OUTER

a. Incorrect: The JOIN keyword cannot be omitted in the new syntax for joins. b. Incorrect: If the CROSS keyword is omitted from CROSS JOIN, the keyword JOIN alone means inner join and not cross join anymore. c. Correct: If the INNER keyword is omitted from INNER JOIN, the meaning is retained. d. Correct: If the OUTER keyword is omitted from LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN, the meaning is retained.

What is the limit for the number of steps in statistic histograms? a. 10 steps per histogram B. 200 histograms per column c. 200 pages per histogram D. 200 steps per histogram

correct answer: D a. incorrect: You can have up to 200 steps in a histogram. B. incorrect: You have one histogram per statistics. c. incorrect: There is a limit of steps per histogram. D. correct: You can have up to 200 steps in a histogram.

Which DMO gives you information about index usage? a. sys.dm_exec_query_stats B. sys.dm_exec_query_text c. sys.dm_db_index_usage_stats D. sys.indexes

correct answer: c a. incorrect: The sys.dm_exec_query_stats DMO gives you statistics about queries, not indexes. B. incorrect: The sys.dm_exec_query_text DMO gives you the text of the batches and queries. c. correct: The sys.dm_db_index_usage_stats DMO gives you information about index usage. D. incorrect: sys.indexes is a catalog view, not a DMO.

What is the only algorithm that supports non-equijoins

nested loop

Algorithm that uses one table for the outer loop, typically that with fewer rows. For each row in the outer table Sql Server seeks for matching rows in the inner table

nested loops

What are the basic join algorithms Sql Server supports ?

nested loops, merge joins, hash joins

What node type test can be used to retrieve all nodes of an XML instance?

node(); with a asterick(*) you retrieve all principal nodes with comment() you retrieve all comment nodes with text() you retrieve all text nodes

Means the Equals operator does not need to be part of the join predicate

non-equijoin

What system stored proc is used get information about cached plans and the number of times the plans were reused

sys.dm_exec_query_stats

What system stored proc is used to get the exact text of a cached query

sys.dm_exec_sql_text

What is the Sys function for validating a Plan

sys.fn_validate_plan_guide

What is the Catalog/table to get a list of all plan guides in a database

sys.plan_guides

What are the 3 kinds of hints?

table hints, query hints, join hints