Sql server interview questions and answers-Part-3

QUESTION - Ways to troubleshoot performance problems in SQL Server.          

ANSWER - SET SHOWPLAN_ALL ON          

SET SHOWPLAN_TEXT ON          

SET STATISTICS IO ON          

SQL Server Profiler          

Windows NT /2000 Performance monitor          

Graphical execution plan in Query Analyzer

QUESTION - Steps to secure an SQL Server.          

ANSWER -  Use NT authentication.          

Use server database and application roles to control access to the data.          

Secure the physical database files using NTFS permissions.          

Use an ungues sable SA password.          

Restrict physical access to the SQL Server.          

Rename the Administrator account on the SQL Server computer.          

Disable the Guest account.          

Enable auditing.          

Use multiprotocol encryption.          

Set up SSL.          

Set up firewalls.          

Isolate SQL Server from the web server etc.

QUESTION - What is a deadlock and what is a live lock?

ANSWER - 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.

QUESTION - What is blocking?          

ANSWER - 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?          

ANSWER - BACKUP/RESTORE,          

Dettach/attach of databases,          

Replication, DTS, BCP, logshipping,          

INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

QUESTION - Explian different types of BACKUPs avaialabe in SQL Server?          

ANSWER - Full database backup.          

Differential database backup.          

Transaction log backup.          

Filegroup backup.

QUESTION - What is database replicaion? 

ANSWER - The process of copying/moving data between databases on the same or different servers.     

Snapshot replication,          

Transactional replication,          

Merge replication.

QUESTION - What are cursors in SQL Server?          

ANSWER - 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

QUESTION - What is a join and explain different types of joins.          

ANSWER - 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.

QUESTION - What is an extended stored procedure in SQL Server?          

ANSWER - 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.  

   

QUESTION - What are triggers in SQL Server?

ANSWER - 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.

QUESTION - What is a self join in SQL Server?          

ANSWER - Two instances of the same table will be joined in the query.

QUESTION - What is the difference between UNION ALL Statement and UNION? 
ANSWER - 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.

QUESTION - Write some disadvantage of Cursor? 
ANSWER - 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.

QUESTION - What is Log Shipping? 
ANSWER - This process synchronizes two SQL servers and thus provides ready server in case one fails. It automatically backup transaction log file throughout the day and restore them on the standby server