SQL interview questions

When interviewing the SQL Server database developer, I used a set of standard benchmark technical questions. The following problems are the problems that I think can really help eliminate unqualified candidates. They are arranged in the order of ease to difficulty. When you ask questions about the primary key and foreign key, the following questions are very difficult, because the answer may be more difficult to explain and explain, especially during the interview.

1. Can you briefly describe some database objects used in SQL Server 2000 to me?

The answer you want to hear includes the following objects: tables, views, user-defined functions, and stored procedures. It would be better if they can also mention objects like triggers. If the applicant cannot answer this basic question, this is not a good sign.

2. What does null mean?

NULL (null) is a very difficult thing in the database world, so there are a lot of candidates who will fall behind in this issue and you should not be surprised.

Null indicates unknown (unknown): it does not represent "" (empty string ). Assume that your SQL Server database contains ansi_nulls. Of course, by default, any comparison of the null value produces a null value. You cannot compare any value with an unknown value and want to obtain an answer logically. You must use the is null operator.

3. What is an index? What types of indexes does SQL Server 2000 contain?

Any experienced database developer should be able to easily answer this question. Some developers with less experience can answer this question, but some may not be clear.

In short, an index is a data structure used to quickly access data in a database table or view. SQL Server has two forms: clustered index and non-clustered index. Clustered indexes store data at the leaf level of the index. This means that no matter which (OR) field of the table exists in the clustered index, these fields will be saved in the table in order. Because such sorting exists, each table has only one clustered index. A non-clustered 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 each table to have multiple non-clustered indexes.

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

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

5. What is a trigger? What are the different types of triggers in SQL Server 2000?

It is helpful to let future database developers know the available trigger types and how to implement them.

A trigger is a special type of stored procedure, which is bound to a table or view of SQL Server 2000. In SQL Server 2000, there are two triggers: instead-of and after. The instead-of trigger is a stored procedure that replaces data manipulation language (DML) statements to execute statements on tables. For example, if I have an instead-of-update trigger for tablea and execute an update statement for this tableCodeWill be executed, rather than the update statement I executed will not be executed.

The After trigger is executed only after the DML statement is used in the database. These types of triggers are useful for monitoring data changes in database tables.

6. How do you ensure that only the values in the fld1 field exist in Table B with the fld1 field, and these values are in the fld1 field of the table named tablea at the same time?

There are two possible answers to this link-related question. The first answer (and the answer you want to hear) is to use a foreign key restriction. The foreign key restriction is used to maintain the integrity of the reference. It is used to ensure that fields in the table only store values defined in another field in different (or identical) tables. This field is the candidate key (usually the primary key of another table ).

Another answer is the trigger. The trigger can be used to ensure the same effect as the limitation in another way, but it is very difficult to set and maintain, and the performance is generally very bad. For this reason, Microsoft recommends that developers use foreign key restrictions instead of triggers to maintain the integrity of the reference.

7. What performance considerations do I have to consider if I have too many indexes on a used online transaction processing table?

You are looking for candidates for data manipulation. The more indexes a table has, the more time it takes for the database engine to update, insert, or delete data, because the indexes must be maintained when data control occurs.

8. What can you use to ensure that fields in the table only accept values in a specific range?

This question can be answered in multiple ways, but only one answer is "good. The answer you want to hear is the check restriction, which is defined in the database table to restrict the value of the input column.

A trigger can also be used to limit the acceptable values of fields in a database table. However, this method requires that the trigger be defined in the table, which may affect the performance in some cases. Therefore, Microsoft recommends that you use check instead of other methods to limit domain integrity.

If a candidate can answer this question correctly, his chances are very high, because it indicates that they have experience in using stored procedures.

The returned parameter is always returned by the stored procedure. It indicates whether the stored procedure is successful or failed. The return parameter is always of the int data type.

The output parameter must be specified by the developer. It can return other types of data, such as numeric and numeric values. (There are some restrictions on the data types that can be used as output parameters .) You can use multiple output parameters in a stored procedure, but you can only use one response parameter.

8. What are related subqueries? How to use these queries?

More experienced developers will be able to accurately describe such types of queries.

Related subqueries are special types of queries that contain subqueries. The subquery in the query actually requests the value of the external query to form a loop-like situation.