Tuesday, January 24, 2012

SQL Server Database Administrator Interview Questions and Answers Series 12

What is Peer to peer Replication?
Peer-to-peer replication also known as multimaster replication.
Peer to peer replication configuration has the following characteristics:
1.   Replication occurs between tables on two or more servers.
2.   Updates on any one server are replicated to all other associated servers.
3.   Applications on any of the servers can update the same rows and columns in those tables at the same time.
4.   All servers are equal peers with equal ownership of the data; no server is the "master" or source owner of the data.

What is cursor and what's the difference between Trigger and cursor? 

CURSOR: Cursor is an Database object and retrive the rows from database row-by-row, and it's mainly use for to reduce the network traffic, it contains 5 features.
1.  DECLARE the Cursor
2.  OPEN the cusrsor
3.  FETCH the cursor
4.  CLOSE the cursor
5.  DEALLOCATE the cursor
 
TRIGGER : A trigger is a procedure that is executed automatically when some specific events occur in a table of a database. Triggers are mainly used for maintaining integrity in a database. Triggers are also used for enforcing business rules, auditing changes in the database and replicating data. Most common triggers are Data Manipulation Language triggers are triggered when data is manipulated.

Difference Between Trigger and Cursor.

A trigger is executed automatically when some specific events occur in a table of a database, while a cursor is a control structure used in databases to go through the database records.

A cursor can be declared and used within a trigger. In such a situation, the declare statement would be inside the trigger. Then the scope of the cursor would be limited to that trigger.

Within a trigger, if a cursor is declared on an inserted or a deleted table, such a cursor would not be accessible from a nested trigger. Once a trigger is completed, all the cursors created within the trigger will be de-allocated.

What is CLR Integration ?

Common Language Runtime (CLR ) in .NET applications using SQL Server 2008. The common language runtime (CLR) integration feature is off by default in Microsoft SQL Server, and must be enabled in order to use objects that are implemented using CLR integration.

Here is the query for enabling CLR integration In SQL Server 2008. 

SP_configure 'clr enabled', 1 
GO
RECONFIGURE 
GO 

What is Notification services ?
Notification Services was designed to ease the pain of  developing and deploying notification applications that generate personalized, timely information to subscribers

What is Constraint? How many types of constraints in SQL ?
Constraints are used to limit the type of data that can go into a table.
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT

What are the differences between stored procedure and functions in SQL Server?
1.  Functions are used for computations where as procedures can be used for performing business logic
2.  Functions MUST return a value, procedures need not be.
3.  You can have DML(insert, update, delete) statements in a function. But, you cannot call such a function in a SQL query.
4.  Function parameters are always IN, no OUT is possible. 

How to determine the service pack currently installed on SQL Server?
There are so many ways to determine:
1. Right click on the server name and then click on properties from there you can see all the Server details including SP.
2. In a new Query Window window type select @@version and execute
3. SELECT SERVERPROPERTY('PRODUCTLEVEL') 

What is blocking and how would you troubleshoot it? 
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first. 
SP_who2 will give the list of blocking sessions. From that we can find the objects using dbcc command. 

USE master
GO
Select spid,blocked from sysprocesses where blocked>0 
GO 

What are statistics, under what circumstances they go out of date,  and how do you update them?
Statistics determine the selectivity of the indexes.
If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.

Under which situations you should update statistics? 
1.  If there is significant change in the key values of the index
2.  If a large amount of data in an indexed column has been added, changed, or removed, or the table has been truncated using the TRUNCATE TABLE statement and then repopulated.
3) Database is upgraded from a previous version. 

To update statistics here is the command. 
USE AdventureWorks;
GO
UPDATE STATISTICS dbo.Tablename; 
GO 

What is the difference between a Local temporary table and a Global temporary table? 
A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

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.

1 comments:

Very nice, this is very helpful, thank you for your postings...


Jerome

Post a Comment