MS SQL Interview Questions and Answers

Answer - 1 : -                                            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.                                                                       

Answer - 2 : - 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.

Answer - 3 : - 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.

Answer - 4 : - 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.

Answer - 5 : - 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.

Answer - 6 : - 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.

Answer - 7 : - Transactional deadlock errors. This level of error severity indicates a transaction deadlock error.

Answer - 8 : - Distributor, Publisher, then Subscriber. You always perform an upgrade in this order: distributor, publisher, subscriber.

Answer - 9 : -  The answer is up in the air and this is more of a poll than a real QOD. Based on the ways things are going, the staff here sees just 1, though our hope would be that 3 or 4 would be released. You setup a linked server from a SQL Server 2000 server to your new SQL Server 2005 server (with defaults), however you cannot execute

Answer - 10 : - You need to enable RPC. By default, RPC is disabled in SQL Server 2005. You need to set the "remote access option" in your server configuration to 1 to allow the execution of stored procedures from a remote server.