"SQL SERVER " Interview Questions And Answers for SQL SERVER

"SQL SERVER " Interview Questions And Answers for SQL SERVER 

1.   What is an Entity?

The basic data item stored in database is called entity. An entity can be any object, item, place, person, concept, or activity about which data is stored.

2.  What is an attribute?

An attribute is a property of an entity. It describes a part of an entity. Entity could have one or more attributes.

3.    What is ER diagram?

An Entity Relationship Diagram is diagrammatic representation of the logical structure of a database system.

4.     Describe the concept of keys.

Candidate key

An attribute that uniquely identifies a row is called candidate key. It is also called das surrogate key.

Primary key

A candidate key that you choose to identify rows uniquely is called a primary key.

Alternate key

If there are multiple candidate keys in a table, the candidate keys that are chosen as primary key are called the alternate keys.

Composite key

When the key that uniquely identifies the rows of a table is made up of more than one attribute, it is called as a composite key.

Foreign key

Two tables can be related using a common attribute. When a primary key of one table is also available as an attribute in another related table it is called a foreign key.

5.    What are joins?

Sometimes, data from multiple tables is to be displayed using select statement. For this purpose, the tables in use must have a column that is equated. This is termed as simple join or multiple join.

Sometimes, you might want to display all records from one table and some from another. This type of join is called an outer join. An outer join is only possible between two tables.

There are two types of outer join, namely left and right. In a left outer join, all the rows of the first table named in the FROM clause are displayed. In a right outer join all the rows from the second table mentioned in the FROM clause are displayed. In either case, all the matching rows from other table are displayed.

6.    What is a sub query?

Sometimes the results of one query are dependant on the results of another query. For this purpose one query is nested inside another query, this is called as sub query.

7.    What are the types of constraints?

You can enforce data integrity by using constraints. Constraints are divided in to five categories.

Primary key constraint

A primary key constraint is defined on a column are a set of columns whose values uniquely identify the rows in a table. It cannot contain null values.

Unique constraint

Unique constraints are used to enforce uniqueness on non-primary key columns. It allows null values but only one row can have a null value.

Multiple unique constraints can be created on a table.

Foreign Key constraint

You can use the foreign key constraint to remove the inconsistency in two tables when the data in the one table is dependant on the other table.

Check constraint

It enforces domain integrity by restricting the values to be inserted in a column. It is possible to define multiple check constraints on a single column. These are evaluated in the order in which they are defined.

Default Constraint

A default constraint can be use to assign a constant value to a column and the user need not insert values in to that column.

8.   What is a rule?

The required integrity can be enforced by specifying a check constraint or by defining a rule. But check constraint modifies the table structure. The constraint can there fore be implemented using rules with out changing the table structure. This rule is applied before an insert or update statement .

A rule must be bound to a column or a user-define d data type. This is done using stored procedure sp_bindrule. Rules do not apply to data that has already been inserted in the table. The existing values in tables do not have to meet the criteria specified by the rule.

9.     What are indexes?

To speed up data retrieval indexes are used. Indexes also enforce the uniqueness of rows.

Advantages

1.     Improves the speed of execution.

2.     Enforces uniqueness of data.

3.     Speeds up joins between tables.

Disadvantages

1.Takes disk space to store.

2.Data modification takes longer.

3.Takes time to create index.

Types of indexes

Clustered index

1.The data is physically sorted

2.One clustered index can be created per table, so you should build it on attributes that have a high percentage of unique values and that are not modified often.

Nonclustered index

1.The physical order of the rows is not the same as the index order.

2.There can be as many as 249 nonclustered indexes per column.

10.  What are views?

A view is a virtual table, which gives access to a subset of columns from one or more tables. It is a query stored as an object in the database. Hence a view is an object that derives it data from one or more tables.

Advantages

1.A view serves as a security mechanism.

2.A view simplifies the usage of complex queries.

11.   What are store procedures and its advantages?

A stored procedure is collection or batch of Transact-SQL statements and control flow language that is stored under one name, and executed as single unit. It helps in improving the performance of a query. It is a precompiled object. As it is ready to execute no time is needed for parsing and compiling the procedure.

Advantages

Improved performance – Applications do not have to compile the procedure over and over again.

Reduction in network congestion – applications need not submit multiple SQL statements to server for the purpose of processing.

Enhanced accuracy – SQL statements included in a procedure are designed by experienced programmers and are therefore more efficient, error free, and tested.

Better security mechanism – users can be granted permission to execute a stored procedure even if they do not own it.

Types of stored procedures

1.User-defined

2.System defined

These are prefixed with sp_. These are for administrative purpose and are stored in the database and are accessible to all users.

3.Temporary

These are prefixed with #, stored in tempdb and are automatically dropped when connection terminates.

4.Remote

These are created and stored in remote servers and can accessed by users with appropriate permissions.