Cursor and Join Questions: SQL Server Interview Answers Part 2

This post answers nine SQL Server interview questions, including cursor and join questions. It is the second part of my series on interview questions and answers.

What Is a Cursor?

A cursor is a database object that applications use to work with data one row at a time. The usual SQL commands work on all the rows in a set at once.

To work with a cursor, you perform these steps in this order:

• Declare the cursor
• Open the cursor
• Fetch a row from the cursor
• Process the fetched row
• Close the cursor
• Deallocate the cursor (read more here)

What Is Collation?

Collation is a set of rules that decides how data is sorted and compared. Character data is sorted by rules that define the correct character order. The options cover case sensitivity, accent marks, kana character types and character width (read more here).

What Is the Difference Between a Function and a Stored Procedure?

You can use a user-defined function (UDF) anywhere in the WHERE, HAVING or SELECT part of a SQL statement. You can’t do that with a stored procedure.

A UDF that returns a table works like another rowset. You can use it in joins with other tables. An inline UDF is like a view that takes parameters. You can use it in joins and other rowset operations.

What Is a Subquery, and What Are Its Properties?

A subquery is a SELECT statement nested inside another T-SQL statement. It is also called a sub-select. You write it inside a set of parentheses.

Subqueries usually return a single row as one value. You can also compare a value against multiple rows with the IN keyword.

A subquery can stand alone. If you run it by itself, it returns a result set, so it doesn’t depend on the statement around it. It can return any number of values. You can find it in the column list of a SELECT. It also works in FROM, GROUP BY, HAVING and ORDER BY clauses. You can also use it as a parameter to a function call. In short, you can use a subquery anywhere you can use an expression (read more here).

What Are the Different Types of Join?

Cross Join

A cross join with no WHERE clause produces the Cartesian product of the tables. The result has as many rows as the first table’s row count times the second table’s row count. A common example is a company that combines each product with a pricing table. That shows each product at each price.

Inner Join

An inner join shows only the rows that have a match in both tables. It is the default join type in the Query and View Designer.

Outer Join

An outer join includes rows even when they have no related rows in the joined table. You can create three kinds of outer join to decide which unmatched rows to include:

• Left Outer Join: includes all rows of the first-named table, the “left” table that appears leftmost in the JOIN clause. Unmatched rows in the right table don’t appear.
• Right Outer Join: includes all rows of the second-named table, the “right” table that appears rightmost in the JOIN clause. Unmatched rows in the left table aren’t included.
• Full Outer Join: includes all rows of all joined tables, whether they match or not.

Self Join

A self join is a case where a table joins to itself, with one or two aliases to avoid confusion. It can be of any type, as long as the joined tables are the same. It is unusual because it involves a relationship with only one table.

A common example is a company with a reporting structure, where one member of staff reports to another. A self join can be an outer join or an inner join (read more here).

What Are Primary Keys and Foreign Keys?

Primary keys are the unique identifiers for each row. They must contain unique values and can’t be null. They are the most fundamental of all keys and constraints, because they matter so much in relational databases. A table can have only one primary key.

Foreign keys ensure data integrity, and they show the relationship between tables.

What Are User-Defined Functions, and What Kinds Can You Create?

User-defined functions let you write your own T-SQL functions. They accept zero or more parameters, and they return a single scalar value or a table.

Scalar User-Defined Function

A scalar function returns one of the scalar data types. The text, ntext, image and timestamp types aren’t supported. This is the kind most developers know from other programming languages. You pass in zero to many parameters and you get a return value.

Inline Table-Valued User-Defined Function

An inline table-valued function returns a table and is a good alternative to a view. It can pass parameters into a T-SQL SELECT. In effect, it gives you a parameterized, non-updateable view of the underlying tables.

Multi-Statement Table-Valued User-Defined Function

A multi-statement table-valued function also returns a table, and it is also a good alternative to a view. It can support multiple T-SQL statements to build the final result, while a view is limited to a single SELECT. It also takes parameters, so it gives you a parameterized, non-updateable view of the underlying tables.

Inside the CREATE FUNCTION command, you define the structure of the table that is returned. You can use this type in the FROM clause of a T-SQL command. A stored procedure, which can also return record sets, can’t be used that way (read here for an example).

What Is Identity?

Identity, also called AutoNumber, is a column that generates numeric values on its own. You can set a start value and an increment, but most DBAs leave both at 1.

A GUID column holds 16-byte unique values instead of numbers, usually from NEWID(). You can’t control the value it generates. SQL Server doesn’t index either one automatically, so add an index when you search or join on them.

What Is Data Warehousing?

A data warehouse is a database with these four traits:

• Subject-oriented: the data is organized so that all elements about the same real-world event or object are linked together.
• Time-variant: changes to the data are tracked and recorded, so you can produce reports that show changes over time.
• Non-volatile: once committed, the data is never overwritten or deleted. It is static and read-only, and it is kept for future reporting.
• Integrated: the database holds data from most or all of an organization’s operational applications, and the data is made consistent.

An interview answer is not a definition to recite, it is an idea you can explain.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.