Querying T-SQL Interview Questions - Part 1 of 2 (On Comprehension from TK 70-461)

SSAS Comprehension Questions

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 6

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

The forms are AS , , and = .

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 10

Which function returns the current date and time value as a DATETIME2 type? Answer.

The SYSDATETIME function.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 19

What are the different types of outer joins? Answer.

Left, right, and full.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 24

What makes a query a grouped query? Answer.

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

Hide the Answer.

Question 25

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


Hide the Answer.

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.

Hide the Answer.

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).

Hide the Answer.

Question 28

What are the clauses that the different types of window functions support? Answer.

Partitioning, ordering, and framing clauses.

Hide the Answer.

Question 29

What do the delimiters UNBOUNDED PRECEDING and UNBOUNDED FOLLOW- ING represent? Answer.

The beginning and end of the partition, respectively.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 33

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


Hide the Answer.

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

Hide the Answer.

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.

Hide the Answer.

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.

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

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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).

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 47

Can synonyms be altered? Answer.

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

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 50

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

All table rows.

Hide the Answer.

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.

Hide the Answer.

Question 52

Which rows from the target table get deleted by a DELETE statement without a WHERE clause? Answer.

All target table rows.

Hide the Answer.

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.

Hide the Answer.

Question 54

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

Question 55

How do you obtain a new value from a sequence? Answer.

With the NEXT VALUE FOR function.

Hide the Answer.

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.

Hide the Answer.

Question 57

What are the possible actions in the WHEN MATCHED clause? Answer.


Hide the Answer.

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.

Hide the Answer.

Question 59

How many OUTPUT clauses can a single statement have? Answer.

Two—one with INTO and one without INTO.

Hide the Answer.

Question 60

How do you determine which action affected the OUTPUT row in a MERGE statement? Answer.

Use the $action function.

Hide the Answer.

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.

Hide the Answer.

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

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 65

Can readers block readers? Answer.

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

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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 command can be inserted into a dynamic SQL batch.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 89

What are the two types of table-valued UDFS? Answer.

You can create inline or multistatement table-valued UDFs.

Hide the Answer.

Question 90

What type of UDF returns only a single value? Answer.

A scalar UDF returns only a single value.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

Question 94

What are the commands that are required to work with a cursor? Answer.


Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.

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.

Hide the Answer.