Saturday, December 12, 2009

SQL Server Database Administrator Interview Questions and Answers Series 8

What is a deadlock and what is a live lock?
When two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process. 
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering.A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.

What is blocking?
When one connection from an application holds a lock and a second connection requires a conflicting lock type.
Ways of moving data/databases between servers and databases in SQL Server?
BACKUP/RESTORE,
Dettach/attach of databases,
Replication, DTS, BCP, logshipping,
INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

What are cursors in SQL Server?
Cursors allow row-by-row prcessing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven.
Each time you fetch a row from the cursor, it results in a network roundtrIP
Cursors are also costly because they require more resources and temporary storage

What is an extended stored procedure in SQL Server?
An extended stored procedure compiles as DLL and are created to expand capabilties of user defined stored procedure. It uses xp_ prefix as naming convention.

What are triggers in SQL Server?
Triggers are special kind of event driven stored procedures.
Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table,
Can specify which trigger fires first or fires last using sp_settriggerorder,
Triggers can't be invoked on demand,
They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens,
Triggers are generally used to implement business rules, auditing,
Triggers can also be used to extend the referential integrity checks.

What is a self join in SQL Server?
Two instances of the same table will be joined in the query.

Write some disadvantage of Cursor?
Cursor manipulates records row by row. It requires temporary storage for row manipulation. Thus manipulating row using cursor is costly affair since it consumes extra resource. Fetch record row by row means server roundtrIP that consumes network recourses.

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.

Define COLLATION?
Collation is the order that SQL Server uses for sorting or comparing textual data. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.

Where is users names and passwords stored in SQL Server?
They are stored in master db in the sysxlogins table.

How can we move data along with schema of the server object?
We can copy data along schema using DTS package. 

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