sql server basic interview questions

Can you give me an overview of some of the database objects available foruse 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 mentionadditional objects such as triggers. It's not a good sign if an applicantcannot answer this basic question.

What does NULL mean?

The value NULL is a very tricky subject in the databaseworld, so don't be surprised if several applicants trip up on this question.

The value NULL means UNKNOWN; it does not mean '' (emptystring). Assuming ANSI_NULLS are on in your SQL Server database, which they areby default, any comparison to the value NULL will yield the value NULL. Youcannot compare any value with an UNKNOWN value and logically expect to get ananswer. 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 uniquelydefines 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 twotables. This relationship typically involves the primary key field(s) from onetable with an adjoining set of field(s) in another table (although it could be thesame table). The adjoining field(s) is the foreign key.

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 aCheck constraint, which is defined on a database table that limits the valuesentered into that column. These constraints are relatively easy to create, andthey are the recommended type for enforcing domain integrity in SQL Server.

Triggers can also be used to restrict the values accepted ina field in a database table, but this solution requires the trigger to bedefined on the table, which can hinder performance in certain situations. Forthis reason, Microsoft recommends Check constraints over all other methods forrestricting domain integrity.

What is a left outer join? Give an example.

Assume you have two tables, TableA and TableB. You need all the rows from TableA and all matching rows from TableB. You would use a left outer join to accomplish this with TableA being the left table as in the following.

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1

 What is the default value of an integer data type in SQL Server 2005?
NULL
• What is the difference between a CHAR and a VARCHAR data type?

CHAR and VARCHAR data types are both non-Unicode character data types with a maximum length of 8,000 characters. The main difference between these 2 data types is that a CHAR data type is fixed-length while a VARCHAR is variable-length. If the number of characters entered in a CHAR data type column is less than the declared column length, spaces are appended to it to fill up the whole length.

Another difference is in the storage size wherein the storage size for CHAR is n bytes while for VARCHAR is the actual length in bytes of the data entered (and not n bytes).

You should use CHAR data type when the data values in a column are expected to be consistently close to the same size. On the other hand, you should use VARCHAR when the data values in a column are expected to vary considerably in size.

How are the UNIQUE and PRIMARY KEY constraints different?

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.