Solution rollup for SQL Server developers applying for frequently asked questions

Server| problem is difficult to find a very qualified database developer in the workplace. One of my colleagues once said: "SQL Development is a language, it is easy to learn, but difficult to grasp." ”

When interviewing a candidate for a SQL Server Database developer, I used a standard set of benchmark technical issues. Here are a few of the questions I think can really help to eliminate unqualified candidates. They are arranged in order from easy to difficult. When you ask questions about primary and foreign keys, the following questions are difficult, because the answer may be more difficult to explain and describe, especially in the case of an interview.

　　Can you give me a brief account of some of the database objects used in SQL Server 2000?

The answers you want to hear include objects such as tables, views, user-defined functions, and stored procedures, even better if they can mention objects like triggers. If the candidate can't answer the basic question, then this is not a good omen.

　　What is an index? What type of index is in SQL Server 2000?

Any experienced database developer should be able to easily answer this question. Some of the less experienced developers can answer the question, but there are some areas where it is unclear.

Simply put, an index is a data structure that is used to quickly access data in a database table or view. In SQL Server, they have two forms: clustered indexes and nonclustered indexes. The clustered index holds the data at the leaf level of the index. This means that regardless of which (or which) fields are in the clustered index, the fields are saved sequentially in the table. Because of this sort, each table will have only one clustered index. A nonclustered index has a row identifier at the leaf level of the index. This row identifier is a pointer to data on the disk. It allows multiple nonclustered indexes per table.

　　What do you mean by null?

Null (empty) This value is a very difficult thing in the database world, so there are a lot of candidates who will stumble over this issue and don't be surprised.

This value of NULL represents unknown (unknown): It does not represent "" (an empty string). Suppose you have ansi_nulls in your SQL Server database, of course, by default, and any comparison of NULL values will produce a null value. You can't compare any value to a unknown value and logically expect an answer. You must use the IS null operator.

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

A primary key is a field (one or more) in a table that is used only to define rows in a table; the values in the primary key are always unique. A foreign key is a constraint used to establish a relationship between two tables. This relationship generally involves a series of connected fields in a table with a primary key field and another table (although it may be the same table). Then these connected fields are foreign keys.

　　What is a trigger? What are the different types of triggers for SQL Server 2000?

It is useful for future database developers to know the types of triggers available and how to implement them.

A trigger is a specialized type of stored procedure that is bundled into a table or view of SQL Server 2000. In SQL Server 2000, there are instead-of and after two kinds of triggers. A instead-of trigger is a stored procedure that replaces a statement executed against a table by a data manipulation language (information manipulation LANGUAGE,DML) statement. For example, if I have a instead-of-update trigger for TableA, and an UPDATE statement is executed on the table, the code in the Instead-of-update trigger executes, not the UPDATE statement I execute.

After triggers are not executed until the DML statements have been used in the database. These types of triggers are very useful for monitoring data that occurs in a database table.

　　How do you make sure that a TableB table with the name Fld1 field has only those values in the Fld1 field that are in the Fld1 field of the table named TableA?

There are two possible answers to this relationship-related question. The first answer (and the answer that you want to hear) is to use a foreign key restriction. Foreign key restrictions are used to maintain referential integrity. It is used to ensure that the fields in the table only hold values that have been defined in another field in a different (or identical) table. This field is the candidate key (usually the primary key for another table).

Another answer is a trigger. Triggers can be used to ensure that the same effect is achieved in another way, but it is very difficult to set up and maintain, and performance is generally bad. For this reason, Microsoft recommends that developers use foreign key restrictions rather than triggers to maintain referential integrity.

What are the performance considerations for an input online transaction table that has too many indexes to use?

You are looking for candidates who are involved in data manipulation. The more indexes you have on a table, the more time the database engine will take to update, insert, or delete data, because indexes must be maintained when data manipulation occurs.

　　What can you use to make sure that the fields in the table only accept values in a specific range?

This question can be answered in many ways, but only one answer is "good". The answer you want to hear is the check limit, which is defined in the database table to limit the value entered for that column.

Triggers can also be used to limit the values that fields in a database table can accept, but this approach requires triggers to be defined in a table, which may affect performance in some cases. Therefore, Microsoft recommends restricting domain integrity by using check restrictions rather than other means.

　　What is the difference between the return parameter and the output parameter?

If the candidate is able to answer the question correctly, his chances are very great because it shows that they have experience using stored procedures.

The return parameter is always returned by the stored procedure, which is used to indicate whether the stored procedure succeeded or failed. The return parameter is always an int data type.

The output parameter explicitly requires the developer to specify that it can return other types of data, such as character and numeric values. (The data type that can be used as an output parameter has some limitations.) You can use multiple output parameters in a stored procedure, and you can use only one return parameter.

　　What is a related subquery and how do you use these queries?

More experienced developers will be able to accurately describe this type of query.

A related subquery is a special type of query that contains subqueries. A subquery contained in a query can actually request the value of an external query, thus creating a loop-like condition.

　　Thinking about the interview process

These questions are simply a starting point for determining whether a SQL Server Database developer qualifies. Depending on the candidate's answers to these questions, I might ask them to attend my TSQL programming exam, which is typically a set of 10 to 12 database queries based on different situations.

You need to decide for yourself what kind of expertise the developer is going to hire. Then, you need to use your experience, your judgment, and your perception of the candidate in the interview (to make a final decision).