Saturday, December 5, 2009

SQL Server Database Administrator Interview Questions and Answers Series 7

What is bit datatype?
Bit datatype is used to store boolean information like 1 or 0 (true or false). Bit datatype can represent a Null state.

Define composite key.
A key formed by combining at least two or more columns.

Define default constraint?
Default constraint is used to fill column with default value defined during creation of table if nothing is supplied while inserting data.
IDENTITY columns and timestamp columns can't be associated with default constraint.

What is lock escalation?
Lock escalation is the process of converting low level locks (row locks, page locks) into higher level locks (table locks).
Each lock is a memory structure. Too many locks mean more memory being occupied by locks.
SQL Server escalates the many fine-grain locks to fewer coarse-grain locks.

Explain SQL Server Service Manager.
SQL Server Service Manager is used to start, stop, and pause SQL Server services that exist as the separate components on the server.
The service components are as follows:
SQL Server service
SQL Server Agent service
Microsoft Search service
MSDTC service
MSSQLServerOLAPService service

Define DTS service in SQL Server.
DTS, Data Transformation Services is used to import and export data between heterogeneous data source.
A DTS package can not only transfer data from a table to another, but can also specify query as source of data.


Define Bulk Copying and Distributed Queries of SQL Server.
Bulk copying is used to transfer large amount of data.
Distributed Queries in SQL server are used to reference heterogeneous data source such as a Microsoft Access database or Oracle database.


What are constraints in SQL Server?
Constraints enforce the data integrity to the database and protect columns of the table from unwanted values. NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY are the types of contraints define in SQL Server.

What is an index?
Indexes of SQL Server are similar to the indexes in books.
They help SQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes.
Rows in the table are stored in the order of the clustered index key.
There can be only one clustered index per table.
Non-clustered indexes have their own storage separate from the table data storage.
Non-clustered indexes are stored as B-tree structures.
Leaf level nodes having the index key and it's row locater.

What are the disadvantages of the Indexes?
Use of intexes slow down Data modification operations (such as INSERT, UPDATE, DELETE).
Every time data changes in the table, all the indexes need to be updated.
Indexes need disk space, the more indexes you have, more disk space is used.

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