|
Microsoft SQL Server Interview Questions and Answers
You have a SQL Server 2005 cluster and need to add
anti-virus software as per your corporate standards.
What should you exclude from scans?
Log and data files for your databases as well as the
Quorum drive and the backup folders.
Anti-virus programs can exist on the same server as SQL
Server without an issue if you exclude certain items.
The database data and log files, backup files and
folders, the quorum drive for clusters, temporary
replication files, the SQL Server log files, and Log
Shipping folders/files. You do not need to exclude that
SQL Server executables and probably do not want to so
that they are protected.
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.
Weekly SQL tips in your inbox
TechRepublic's free SQL Server newsletter, delivered
each Tuesday, contains hands-on tips that will help you
become more adept with this powerful relational database
management system.
Automatically sign up today!
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.
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.
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.
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.
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.
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.
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.
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.
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.
Page Numbers : 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
