Questions to ask a SQL Server database developer applicant

1  Can you give me an overview of some of the database objects available for use    in SQL Server 2000?

You are looking for objects such as: tables, views, user-defined functions, and stored procedures; it's even better if they mention additional objects such as triggers. It's not a good sign if an applicant cannot answer this basic question.

2  What is an index? What types of indexes are available in SQL Server 2000?

 

      In its most simple terms, an index is a data structure used to provide quick access to data in a database table or view. In SQL Server, they come in two flavors: clustered and non-clustered. Clustered indexes store the data at the leaf level of the index. This means that whichever field(s) in your table are included in the clustered index, they will be stored in an orderly fashion in the table. Because of this sorting, you can only have one clustered index per table. Non-clustered indexes contain a row identifier at the leaf level of the index. This row identifier is a pointer to a location of the data on the disk. This allows you to have more than one non-clustered index per table.

 

3   What does NULL mean?

        The value NULL is a very tricky subject in the database world, so don't be surprised if several applicants trip up on this question.

      The value NULL means UNKNOWN; it does not mean '' (empty string). Assuming ANSI_NULLS are on in your SQL Server database, which they are by default, any comparison to the value NULL will yield the value NULL. You cannot compare any value with an UNKNOWN value and logically expect to get an answer. You must use the IS NULL operator instead.

NULL 值表示列的数据值未知或不可用。NULL 值与零（数值或二进制值）、零长度的字符串或空白（字符值）的含义不同。相反，空值可用于区分输入的是零（数值列）或空白（字符列）还是无数据输入（NULL 可用于数字列和字符列）。

可以通过以下两种方式在允许空值的列中输入 NULL 值（根据 CREATE TABLE 语句中的指定）：

• 如果无数据输入并且列或数据类型无默认值或 DEFAULT 约束，Microsoft SQL Server 将自动输入值 NULL 。
  
• 用户可以通过键入不带引号的 NULL 显式输入 NULL 值。如果在字符列中键入带引号的 NULL ，则它将被视为字母 N、U、L 和 L，而非空值。
  

当检索到空值时，应用程序通常会在相应的位置显示诸如 NULL 、(NULL ) 或 (null ) 的字符串

 

当 SET ANSI_NULLS 为 ON 时，如果比较中有一个或多个表达式为 NULL ，则既不输出 TRUE 也不输出 FALSE，而是输出 UNKNOWN。这是因为未知值不能与其他任何值进行逻辑比较。这种情况发生在一个表达式与 NULL 单词进行比较，或者两个表达式相比，而其中一个表达式取值为 NULL 时。例如，当 ANSI_NULLS 为 ON 时，以下比较总是生成 UNKNOWN：

 

Transact-SQL 支持在与空值进行比较时，允许比较运算符返回 TRUE 或 FALSE。通过设置 ANSI_NULLS OFF 可将此选项激活。当 ANSI_NULLS 为 OFF 时，如果 ColumnA 包含空值，则比较操作 ColumnA = NULL 返回 TRUE；如果 ColumnA 除包含 NULL 外还包含某些值，则比较操作返回 FALSE。此外，两个都取空值的表达式的比较也输出 TRUE。当 ANSI_NULLS 设置为 OFF 时，以下 SELECT 语句返回 Customer 表中 Region 为空值的所有行：

 

PS： SET ANSI_NULLS

当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。

当 SET ANSI_NULLS 为 OFF 时，等于 (=) 和不等于 (<>) 比较运算符不遵守 ISO 标准。使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。此外，使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。

 

 

4  What is a primary key? What is a foreign key?

    A primary key is the field(s) in a table that uniquely defines the row in the table; the values in the primary key are always unique. A foreign key is a constraint that establishes a relationship between two tables. This relationship typically involves the primary key field(s) from one table with an adjoining set of field(s) in another table (although it could be the same table). The adjoining field(s) is the foreign key.

 

5  What are triggers? What are the different types of triggers in SQL Server 2000?

      It's very beneficial for a potential database developer to know the types of triggers available, and how to implement them.

      A trigger is a specialized type of stored procedure that is bound to a table or view in SQL Server 2000. In SQL Server 2000, there are INSTEAD-OF triggers and AFTER triggers. INSTEAD-OF triggers are procedures that execute in place of a Data Manipulation Language (DML) statement on a table. For example, if I have an INSTEAD-OF-UPDATE trigger on TableA, and I execute an update statement on that table, the code in the INSTEAD-OF-UPDATE trigger will execute instead of the update statement that I executed.

     An AFTER trigger executes after a DML statement has taken place in the database. These types of triggers are very handy for auditing data changes that have occurred in your database tables.

 

6  How can you ensure that a table named TableB with a field named Fld1 will only have those values in the Fld1 field that are also in the table named TableA with a field named Fld1?

         This relationship related question has two potential answers. The first answer (and the one that you want to hear) is the use of foreign key constraints. A foreign key constraint is used to maintain referential integrity. It is used to ensure that a field in a table will only hold values that are already defined in another field in a different (or the same) table. That field is the candidate key (usually a primary key of the other table).

         The other option is the use of triggers. Triggers can be used to ensure the same effect of constraints in a roundabout way, but it is much more difficult to set up and maintain, and the performance is typically worse. Because of this, Microsoft recommends that developers use foreign key constraints instead of triggers for maintaining referential

integrity.

 

7  What is a performance consideration of having too many indexes on a production online transaction processing (OLTP) table?

     You are looking for the applicant to make some reference regarding data manipulations. The more indexes on a table, the more time it takes for the database engine to update, insert, or delete data, as the indexes all have to be maintained as the data manipulation occurs.

 

8   What can be used to ensure that a field in a table only accepts a certain range of values?

       This question can be answered a couple of different ways, but only one answer is a "good" one. The answer you want to hear is a Check constraint, which is defined on a database table that limits the values entered into that column. These constraints are relatively easy to create, and they are the recommended type for enforcing domain integrity in SQL Server.

       Triggers can also be used to restrict the values accepted in a field in a database table, but this solution requires the trigger to be defined on the table, which can hinder performance in certain situations. For this reason, Microsoft recommends Check constraints over all other methods for restricting domain integrity.

 

9  What is the difference between a return parameter and an OUTPUT parameter?

    If the applicant is able to answer this question correctly, the odds are good that they have some experience working with stored procedures.

    A return parameter is always returned by a stored procedure, and it is meant to indicate the success or failure of the stored procedure. The return parameter is always an INT data type.

   An OUTPUT parameter is designated specifically by the developer, and it can return other types of data, such as characters and numeric values. (There are some limitations on the data types that can be used as output parameters.) You can use multiple OUTPUT parameters in a stored procedure, whereas you can only use one return parameter.

 

10  What is a correlated sub-query? How can these queries be useful?

    The more seasoned developer will be able to accurately describe this type of query.

A correlated sub-query is a special type of query containing a sub-query. The sub-query contained in the query actually requests values from the outside query, creating a situation similar to a loop. You can find a more detailed description as to how these special types of queries work in this article .