Saturday, November 21, 2009

SQL Server Database Administrator Interview Questions and Answers Series 5

How do I make a DTS package log to a text file?.
Open an existing DTS package. 
Right click any white area in the package and select Package Properties. 
Now in the error handling section on the General tab (for SQL Server 7.0) or the error handling section on the Logging tab (for SQL Server 2000) enter a path and file name in the box for error file.
Click OK and save the package. 
Now when you execute the DTS package it will log the success or failure of each task using the task name the DTS package assigns to each task upon creation. 
When a failure occurs a description of the failure will be logged in that text file.

What is Dirty reads?
A dirty read occurs when a transaction reads data that is being changed by another transaction that has not yet been committed or rolled back; the data read cannot really be trusted. For example, let's suppose you have a transaction in which you SELECT data from a table and specify of range of values—let's say last names between the letters K through M. If an uncommitted transaction is changing a name in that range and your SELECT statement were to read the changed data, your transaction would be performing a dirty read.

How to pass a list of values or array to SQL Server stored procedure?
Unfortunately, there is no built-in support for arrays in SQL Server's T-SQL. SQL Server 2000 did add some new datatypes like sql_variant, bigint etc, but no support for the much needed arrays. There are some situations, that require the ability to pass a list of values to a stored procedure. Think about a web page, that lets the user select one or more of his/her previous orders, on submit, retrieves complete information about the selected orders. In this case, passing a list of selected order numbers to the stored procedure, in one go, and getting the results back is more efficient, compared to calling the same stored procedure for each selected order number.

How tempdb works?
Tempdb holds all temporary tables and temporary stored procedures. It also fills any other temporary storage needs such as work tables generated by SQL Server. tempdb is a global resource; the temporary tables and stored procedures for all users connected to the system are stored there. tempdb is re-created every time SQL Server is started so the system starts with a clean copy of the database. Because temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down, there is never anything in tempdb to be saved from one session of SQL Server to another.

What is FillFactor?
The FillFactor property exposes the percent of each page used to store index data when the index is created.

How index works?
Clustered index
Nonclustered index
B-tree 

How to transfer table from primary group to secondary group?
It can be done through GUI. Of database design diagram.

What’s the maximum size of a row and the maximum number of columns per table?
8060 bytes and1024 columns per table 

What are live locks
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely 

What is SQL what’s its uses and its component?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. QL is Non-Procedural language . Its allow the user to concentrate on specifying what data is required rather than concentrating on the how to get it. 

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