Friday, October 16, 2009

SQL Server Database Administrator Interview Questions and Answers Series 2

What is difference between OSQL and Query Analyzer ?
Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool. OSQL have not ability like query analyzer to analyze queries and show statics on speed of execution and other usefull thing about OSQL is that its helps in scheduling

What is COMMIT & ROLLBACK statement in SQL ?
Commit statement helps in termination of the current transaction and do all the changes that occur in transaction persistent and this also commits all the changes to the database.COMMIT we can also use in store procedure.
ROLLBACK do the same thing just terminate the currenct transaction but one another thing is that the changes made to database are ROLLBACK to the database.

Define candidate key, alternate key, composite key.
Candidate key: A column or a set of columns can be called as candidate key if they identify each row of a table uniquely. A table can have multiple candidate keys. One of them is specified as Primary key and rest of them can be called as alternate key.
Alternate key: There can be more than one keys which can identify each row of the table uniquely. One of them is defined as primary key and rest of them is called alternate keys of the table.
Composite Key: A key formed by combining at least two or more columns.  

What are the different types of Locks?
Shared Lock: Shared locks are used for operations that read data, such as a SELECT statement. During Shared locks used, concurrent transactions can read a resource, but cannot modify the data.
Update Lock: Update locks are used when SQL Server intends to modify a row or page, and later promotes the update page lock to an exclusive lock before actually making the changes. The Update locks are used to prevent a deadlock.
Exclusive Lock: Exclusive locks are used for data modification operations, such as UPDATE, INSERT, or DELETE. Other transactions cannot read or modify data locked with an Exclusive lock. 


What is Extent and types of Extent ?
An extent is 8 continuous pages to hold server object.
Uniform extents - This type of extent contains data from one table.
Mixed extents - This type of extent contains data from two to eight different tables. 


What are the different types of joins? What is the difference between them?
INNER JOIN: Inner join shows matches only when they exist in both tables. Example, in the below SQL there are two tables Customers and Orders and the inner join in made on Customers Customerid and Orders Customerid.So this SQL will only give you result with customers who have orders. If the customer does not have order, it will not display that record.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN: Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN: Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined CustomerID values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID 



What is the difference between function and stored procedure?
Function: 
1. Should return at-least one output parameter.Can return more than one parameter using OUT argument. 2. Parsed and compiled at run time. 
3.Cannot affect the state of database. 
4.Can be invoked from SQL statement eg. SELECT statement. 
5. Functions are mainly used to compute values. 
Procedure: 
1. Doesn't need to return values, but can return value. 
2.Stored as a pseudo-code in database i.e. compiled form. 
3.Can affect the state of database using commit etc. 
4.Cannot be invoked from SQL statements eg. SELECT statement. 
5.Procedures are mainly used to process the tasks. 


What is the difference between trigger and stored procedure?
Trigger
Trigger gets executed automatically whenever we perform the Insert, Update, Delete Operations.
Procedure:
While stored procedure are the pre-compiled one and reduce the traffic on the client/server side . It can be used by the multiple users. 

What is the difference between Index Seek Vs. Index Scan?
Index Scan:
1.   An index scan is a complete scan of all the pages in a non-clustered index.
2.   Index Scan retrieves all the rows from the table.
3.   Index Scan is nothing but scanning on the data pages from the first page to the last page.
Index Seek: 
1.   Index Seek retrieves selective rows from the table.
2.   Since a seek only touches rows that qualify and pages that contain these qualifying rows.
3.   An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.
4.   A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf. 

What is the difference between UNION ALL Statement and UNION?
UNION statement eliminates duplicate rows whereas UNION ALL statement includes duplicate rows. UNION statement can be used to combine any number of queries whereas UNION ALL statement can be used to combine a maximum of two queries. UNION statement cannot be used with aggregate functions whereas UNION ALL statement can be used with aggregate functions. 


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.

0 comments:

Post a Comment