Question 1
Name two aspects in which T-SQL deviates from the relational model. Answer.
A relation has a body with a distinct set of tuples. A table doesn’t have to have a key. T-SQL allows referring to ordinal positions of columns in the ORDER BY clause.
Question 2
Explain how you can address the two items in question 1 and use T-SQL in a relational way. Answer.
Define a key in every table. Refer to attribute names—not their ordinal positions—in the ORDER BY clause.
Question 3
What is the difference between the WHERE and HAVING clauses? Answer.
The WHERE clause is evaluated before rows are grouped, and therefore is evaluated per row. The HAVING clause is evaluated after rows are grouped, and therefore is evaluated per group.
Question 4
Why are you not allowed to refer to a column alias defined by the SELECT clause in the WHERE clause? Answer.
Because the WHERE clause is logically evaluated in a phase earlier to the one that evaluates the SELECT clause.
Question 5
Why are you not allowed to refer to a column alias defined by the SELECT clause in the same SELECT clause? Answer.
Because all expressions that appear in the same logical query processing phase are evaluated conceptually at the same point in time.
Question 6
What are the forms of aliasing an attribute in T-SQL? Answer.
The forms are
Question 7
What is an irregular identifier? Answer.
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.
Question 8
Would you use the type FLOAT to represent a product unit price? Answer.
No, because FLOAT is an approximate data type and cannot represent all values precisely.
Question 9
What is the difference between NEWID and NEWSEQUENTIALID? Answer.
The NEWID function generates GUID values in random order, whereas the NEWSEQUENTIAL ID function generates GUIDs that increase in a sequential order.
Question 10
Which function returns the current date and time value as a DATETIME2 type? Answer.
The SYSDATETIME function.
Question 11
When concatenating character strings, what is the difference between the plus (+) operator and the CONCAT function? Answer.
The + operator by default yields a NULL result on NULL input, whereas the CONCAT function treats NULLs as empty strings.
Question 12
What are the performance benefits in using the WHERE filter? Answer.
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.
Question 13
What is the form of a filter predicate that can rely on index ordering called? Answer.
A search argument, or SARG, for short.
Question 14
How do you guarantee the order of the rows in the result of a query? Answer.
The only way to do so is by adding an ORDER BY clause.
Question 15
What is the difference between the result of a query with and one without an ORDER BY clause? Answer.
Without an ORDER BY clause, the result is relational (from an ordering perspec- tive); with an ORDER BY clause, the result is conceptually what the standard calls a cursor.
Question 16
How do you guarantee deterministic results with TOP? Answer.
By either returning all ties by using the WITH TIES option or by defining unique ordering to break ties.
Question 17
What are the benefits of using OFFSET-FETCH over TOP? Answer.
OFFSET-FETCH is standard and TOP isn’t; also, OFFSET-FETCH supports a skipping capability that TOP doesn’t.
Question 18
What is the difference between the old and new syntax for cross joins? Answer.
The new syntax has the CROSS JOIN keywords between the table names and the old syntax has a comma.
Question 19
What are the different types of outer joins? Answer.
Left, right, and full.
Question 20
What is the difference between self-contained and correlated subqueries? Answer.
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.
Question 21
What is the difference between the APPLY and JOIN operators? Answer.
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.
Question 22
Which set operators does T-SQL support? Answer.
The UNION, INTERSECT, and EXCEPT set operators, as well as the UNION ALL multiset operator.
Question 23
Name two requirements for the queries involved in a set operator. Answer.
The number of columns in the two queries needs to be the same, and corresponding columns need to have compatible types.
Question 24
What makes a query a grouped query? Answer.
When you use an aggregate function, a GROUP BY clause, or both.
Question 25
What are the clauses that you can use to define multiple grouping sets in the same query? Answer.
GROUPING SETS, CUBE, and ROLLUP.
Question 26
What is the difference between PIVOT and UNPIVOT? Answer.
PIVOT rotates data from a state of rows to a state of columns; UNPIVOT rotates the data from columns to rows.
Question 27
What type of language constructs are PIVOT and UNPIVOT implemented as? Answer.
PIVOT and UNPIVOT are implemented as table operators (The other table operators are JOIN and APPLY).
Question 28
What are the clauses that the different types of window functions support? Answer.
Partitioning, ordering, and framing clauses.
Question 29
What do the delimiters UNBOUNDED PRECEDING and UNBOUNDED FOLLOW- ING represent? Answer.
The beginning and end of the partition, respectively.
Question 30
Can you store indexes from the same full-text catalog to different filegroups? Answer.
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.
Question 31
How do you search for synonyms of a word with the CONTAINS predicate? Answer.
You have to use the CONTAINS(FTcolumn, ‘FORMSOF(THESAURUS, SearchWord1)’) syntax.
Question 32
Which is a more specific predicate, CONTAINS or FREETEXT? Answer.
The FREETEXT predicate is less specific and thus returns more rows than the CONTAINS predicate. It searches for the values that match the meaning of a phrase and not just exact words. The form is much simpler than the form of the CONTAINS predicate: SELECT…FROM…WHERE FREETEXT(FTcolumn, ‘SearchWord1 SearchWord2’). With this, you are searching for rows where the FTcolumn includes any of the inflectional forms and any of the defined synonyms of the words SearchWord1 and SearchWord2.
Question 33
How many full-text search and how many semantic search functions are supported by SQL Server? Answer.
SQL Server supports two full-text search (CONTAINSTABLE and FREETEXTTABL) and three semantic search functions (SEMANTICKEYPHRASETABLE, SEMANTICSIMILARITYTABLE, and SEMANTICSIMILARITYDETAILSTABLE)
Question 34
How can you get an XSD schema together with an XML document from your SELECT statement? Answer.
You should use the XMLSCHEMA directive in the FOR XML clause. For Example:
SELECT [Customer].custid AS [custid],
[Customer].companyname AS [companyname],
[Order].orderid AS [orderid],
[Order].orderdate AS [orderdate]
FROM Sales.Customers AS [Customer]
INNER JOIN Sales.Orders AS [Order]
ON [Customer].custid = [Order].custid
WHERE 1 = 2
FOR XML AUTO, ELEMENTS,
XMLSCHEMA('TK461-CustomersOrders');
Question 35
What do you do in the Return clause of the FLWOR (For, Let, Where, Order by, Return) expressions? Answer.
The return clause is evaluated once per iteration, and the results are returned to the client in the iteration order. With this clause, you format the resulting XML.
Question 36
What would be the result of the expression (12, 4, 7) != 7? Answer.
The result would be true. This is why:
Boolean predicates select all nodes for which the predicate evaluates to true. XQuery supports logical and and or operators. However, you might be surprised by how comparison operators work. They work on both atomic values and sequences. For sequences, if one atomic value in a sequence leads to a true exit of the expression, the whole expression is evaluated to true. Look at the following example.
DECLARE @x AS XML = N'';
SELECT @x.query('(1, 2, 3) = (2, 4)'); -- true
SELECT @x.query('(5, 6) < (2, 4)'); -- false
SELECT @x.query('(1, 2, 3) = 1'); -- true
SELECT @x.query('(1, 2, 3) != 1'); -- true
Question 37
Which XML data type method would you use to retrieve scalar values from an XML instance? Answer.
The value() XML data type method retrieves scalar values from an XML instance.
Question 38
Can a table or column name contain spaces, apostrophes, and other nonstandard characters? Answer.
Yes, table and column names can be delimited identifiers that contain nonstandard characters.
Question 39
What types of table compression are available? Answer.
You can use either page or row compression on a table. Page compression includes row compression.
Question 40
How does SQL Server enforce uniqueness in both primary key and unique constraints? Answer.
SQL Server uses unique indexes to enforce uniqueness for both primary key and unique constraints.
Question 41
Can a primary key on one table have the same name as the primary key in another table in the same database? Answer.
No, all table constraints must have unique names in a database.
Question 42
Must a view consist of only one SELECT statement? Answer.
Technically, yes, but a workaround to this is that you can unite (using the UNION statement) multiple SELECT statements that together produce one result set.
Question 43
What types of views are available in T-SQL? Answer.
You can create regular views, which are just stored SELECT statements, or indexed views (Create the view first, then index the view by CREATING A UNIQUE CLUSTERED INDEX ), which actually materialize the data, in addition to partitioned views (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).
Question 44
What type of data does an inline function return? Answer.
Inline functions return tables, and accordingly, are often referred to as inline table-valued functions.
Question 45
What type of view can an inline function simulate? Answer.
An inline table-valued function can simulate a parameterized view—that is, a view that takes parameters.
Question 46
Does a synonym store T-SQL or any data? Answer.
No, a synonym is just a name. All that is stored with a synonym is the object it refers to.
Question 47
Can synonyms be altered? Answer.
No, to change a synonym, you must drop and recreate it.
Question 48
Why is it recommended to specify the target column names in INSERT statements? Answer.
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.
Question 49
What is the difference between SELECT INTO and INSERT SELECT? Answer.
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.
Question 50
Which table rows are updated in an UPDATE statement without a WHERE clause? Answer.
All table rows.
Question 51
Can you update rows in more than one table in one UPDATE statement? Answer.
No, you can use columns from multiple tables as the source, but update only one table at a time.
Question 52
Which rows from the target table get deleted by a DELETE statement without a WHERE clause? Answer.
All target table rows.
Question 53
What is the alternative to a DELETE statement without a WHERE clause? Answer.
The TRUNCATE statement. But there are a few differences between the two that need to be considered.
Question 54
How many columns with an IDENTITY property are supported in one table? Answer.
One.
Question 55
How do you obtain a new value from a sequence? Answer.
With the NEXT VALUE FOR function.
Question 56
What is the purpose of the ON clause in the MERGE statement? Answer.
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.
Question 57
What are the possible actions in the WHEN MATCHED clause? Answer.
UPDATE and DELETE.
Question 58
How many WHEN MATCHED clauses can a single MERGE statement have? Answer.
Two—one with an UPDATE action and one with a DELETE action.
Question 59
How many OUTPUT clauses can a single statement have? Answer.
Two—one with INTO and one without INTO.
Question 60
How do you determine which action affected the OUTPUT row in a MERGE statement? Answer.
Use the $action function.
Question 61
Why is it important for SQL Server to maintain the ACID quality of transactions? Answer.
To ensure that the integrity of database data will not be compromised.
Question 62
How does SQL Server implement transaction durability? Answer.
By first writing all changes to the database transaction log before making changes to the database data. In short, ACID is implemented as follows:
A - through Transaction
C - through constraints
I - through locking and versioning
D - through transaction log
Question 63
How many ROLLBACKs must be executed in a nested transaction to roll it back? Answer.
Only one ROLLBACK. A ROLLBACK always rolls back the entire transaction, no matter how many levels the transaction has.
Question 64
How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed? Answer.
One COMMIT for each level of the nested transaction. Only the last COMMIT actually commits the entire transaction.
Question 65
Can readers block readers? Answer.
No, because shared locks are compatible with other shared locks.
Question 66
Can readers block writers? Answer.
Yes, even if only momentarily, because any exclusive lock request has to wait until the shared lock is released.
Question 67
If two transactions never block each other, can a deadlock between them result? Answer.
No. In order to deadlock, each transaction must already have locked a resource the other transaction wants, resulting in mutual blocking.
Question 68
Can a SELECT statement be involved in a deadlock? Answer.
Yes. If the SELECT statement locks some resource that keeps a second transac- tion from finishing, and the SELECT cannot finish because it is blocked by the same transaction, the deadlock cycle results.
Question 69
If your session is in the READ COMMITTED isolation level, is it possible for one of your queries to read uncommitted data? Answer.
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.
Question 70
Is there a way to prevent readers from blocking writers and still ensure that readers only see committed data? Answer.
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.
Question 71
How can you add custom error messages? Answer.
You can use the system stored procedure sp_addmessage to add your own custom error messages.
Question 72
What is severity level 0 used for? Answer.
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.
Question 73
What are the main advantages of using a TRY/CATCH block over the traditional trapping for @@ERROR? Answer.
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.
Question 74
Can a TRY/CATCH block span batches? Answer.
No, you must have one set of TRY/CATCH blocks for each batch of code.
Question 75
Can you generate and execute dynamic SQL in a different database than the one your code is in? Answer.
Yes, because the USE
Question 76
What are some objects that cannot be referenced in T-SQL by using variables? Answer.
Objects that you cannot use variables for in T-SQL commands include the database name in a USE statement, the table name in a FROM clause, column names in the SELECT and WHERE clauses, and lists of literal values in the IN() and PIVOT() functions.
In other words, Dynamic SQL is useful because T-SQL will not permit the direct replacement of many parts of
commands with variables, including:
■ The database name in the USE statement.
■ Table names in the FROM clause.
■ Column names in the SELECT, WHERE, GROUP BY, and HAVING clauses, in addition to
the ORDER BY clause.
■ Contents of lists such as in the IN and PIVOT clauses.
Question 77
How can a hacker detect that SQL injection may be possible? Answer.
By inserting a single quotation mark and observing an error message.
Question 78
Where is the injected code inserted? Answer.
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.
Question 79
How can you pass information from sp_executesql to the caller? Answer.
Use one or more OUTPUT parameters. You can also persist the data in a perma- nent or temporary table, but the most direct method is through the OUTPUT parameter.
Question 80
How does sp_executesql help stop SQL injection? Answer.
You can use sp_executesql to parameterize user input, which can prevent any injected code from being executed.
Question 81
What is the result of the parsing phase of query execution? Answer.
The result of this phase, if the query passed the syntax check, is a tree of logical operators known as a parse tree.
Question 82
How would you quickly measure the amount of disk IO a query is performing? Answer.
You should use the SET STATISTICS IO command.
Question 83
How can you get an estimated execution plan in XML format for further analysis? Answer.
You can use the SET SHOWPLAN_XML command.
Question 84
Which DMO gives you detailed text of queries executed? Answer.
You can retrieve the text of batches and queries executed from the sys.dm_exec_sql_text DMO.
Question 85
What are the two types of parameters for a T-SQL stored procedure? Answer.
A T-SQL stored procedure can have input and output parameters.
Question 86
Can a stored procedure span multiple batches of T-SQL code? Answer.
No, a stored procedure can only contain one batch of T-SQL code.
Question 87
What are the two types of DML triggers that can be created? Answer.
You can create AFTER and INSTEAD OF DML-type triggers.
Question 88
If an AFTER trigger discovers an error, how does it prevent the DML command from completing? Answer.
An AFTER trigger issues a THROW or RAISERROR command to cause the trans- action of the DML command to roll back.
Question 89
What are the two types of table-valued UDFS? Answer.
You can create inline or multistatement table-valued UDFs.
Question 90
What type of UDF returns only a single value? Answer.
A scalar UDF returns only a single value.
Question 91
What kind of clustering key would you select for an OLTP environment? Answer.
For an OLTP environment, a short, unique, and sequential clustering key might be the best choice.
Question 92
Which clauses of a query should you consider supporting with an index? Answer.
The list of the clauses you should consider supporting with an index includes, but is not limited to, the WHERE, JOIN, GROUP BY, and ORDER BY clauses.
Question 93
How would you quickly update statistics for the whole database after an upgrade? Answer.
You should use the sys.sp_updatestats system procedure.
Question 94
What are the commands that are required to work with a cursor? Answer.
DECLARE, OPEN, FETCH in a loop, CLOSE, and DEALLOCATE.
Question 95
When using the FAST_FORWARD option in the cursor declaration command, what does it mean regarding the cursor properties? Answer.
That the cursor is read-only, forward-only.
Question 96
How do you create a local temporary table, and how do you create a global one? Answer.
You name a local temporary table by using a single number sign as a prefix and a global one with two number signs as a prefix.
Question 97
Can you name constraints in local temporary tables and in table variables? Answer.
You can name constraints in local temporary tables, although it’s not recommended because it can generate name conflicts. You cannot name constraints in table variables.
Question 98
How would you determine whether SQL Server used the batch processing mode for a specific iterator? Answer.
You can check the iterator’s Actual Execution Mode property.
Question 99
Why might you prefer using plan guides instead of optimizer hints? Answer.
With plan guides, you do not need to change the query text.
