| |||||
|
Microsoft SQL Server FAQs Part: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 (Continued from previous part...)
Questions to ask a SQL Server database developer applicant 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. What is an index? What types of indexes are available in SQL Server 2000?
Any experienced database developer should be able to answer this question with ease. Some of the less-experienced developers will be able to answer it, but with a little less clarity.
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.
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. 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.
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).
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. 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.
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.
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.
Part: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
||||