Saturday, August 1, 2009

SQL Server Database Administrator Interview Questions and Answers Series 1


What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn't allow Nulls, but unique key allows one NULL only.


What's the difference between DELETE TABLE and TRUNCATE TABLE commands?
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.


What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY 



What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS. 



What are cursors? Explain different types of cursors. What are the disadvantages of cursors?
Cursors allow row-by-row processing of the result sets.   
Types of cursors:  
1. Static, 
2. Dynamic, 
3. Forward only, 
4. Key set-driven.


Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network round-trip, where as a normal SELECT query makes only one round-trip, however large the result set is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Further, there are restrictions on the SELECT statements that can be used with some types of cursors. 


What is the basic difference between clustered and a non-clustered index?
The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.


What is an Index? What are the types of indexes available with SQL Server?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.
There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.


What is normalization? Explain different levels of normalization
It is set of rules that have been established to aid in the design of tables that are meant to be connected through relationships. This set of rules is known as Normalization. 


Benefits of normalizing your database will include:
1. Avoiding repetitive entries

2. Reducing required storage space
3. Preventing the need to restructure existing tables to accommodate new data.
4. Increased speed and flexibility of queries, sorts, and summaries.


Following are the normal forms
1NF Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2NF Eliminate Redundant Data - If an attribute depends on only part of a multivalued key, remove it to a separate table.
3NF Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
BCNF Boyce-Cod Normal Form - If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF Optimal Normal Form - a model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF Domain-Key Normal Form - a model free from all modification anomalies. 


What's the difference between stored procedures and triggers?
Stored procedure:- A stored procedure is a group of Transact-SQL statements that is compiled one time, and then can be executed many times. This increases performance when the stored procedure is executed because the Transact-SOL statements do not have to be recompiled.


Trigger:- A trigger is a special type of stored procedure that is not called directly by a user. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.
A CREATE PROCEDURE or CREATE TRIGGER statement cannot span batches. This means that a stored procedure or trigger is always created in a single batch and compiled into an execution plan. 


What is Isolation Levels ?What are the types of Transaction Isolation Levels?
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed
There are four isolation levels:
READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ
SERIALIZABLE
SNAPSHOT
READ COMMITTED SNAPSHOT 

Please check these interview questions as well. 
[SQL Server Database Administrator Interview Questions and Answers - Part #12] 



Tags: sql server interview questions. sql server 2008 interview questions. sql server 2008 interview questions and answers . sql server interview questions and answers for experienced. sql server interview questions and answers for freshers. sql server interview questions and answers. sql server dba interview questions and answers. sql server dba interview questions. sql server database administration interview questions . sql server 2008 database administration interview questions. sql server 2012 interview question and answers. sql server 2012 FAQ. sql server 2012 dba interview. sql dba 2012 interview question and answers. sql server 2008 r2 interview question and answers. sql 2012. sql 2008 r2. sql 200 r2 interview question and answers. sql server question and answers. sql server developer interview question and answers. sql developer interview question and answers.  sql server interview questions. sql server 2008 interview questions. sql server 2008 interview questions and answers. sql server interview questions and answers for experienced. sql server interview questions and answers for freshers. sql server interview questions and answers. sql server dba interview questions and answers. sql server dba interview questions. sql server database administration interview questions. sql server 2012 database administration interview questions. sql server interview questions for entry level. sql server interview questions for experienced.

8 comments:

Hi

I like this post:

You create good material for community.

Please keep posting.

Let me introduce other material that may be good for net community.

Source: Database interview questions

Best rgs
Peter

Nice Work.......
Now please Tell me What is difference between sql Developer and Sql DBA?

WOW! superb collections! all questions are coming from this only...thanks

REALLY MORE USEFULL !
GOOD WORK ,KEEP GOING .

Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! Its always nice when you can not only be informed, but also entertained! Im sure you had fun writing this article.
Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!


http://www.sqlservermasters.com/

Post a Comment