Sunday, December 27, 2009

SQL Server Database Administrator Interview Questions and Answers Series 10

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? 
Design a single table for one-one relationship is the best way. As for one-many relationship, data can be classified into 2 tables, primary-key and foreign-key one. And about many-one relationship, a junction table should be applied, with keys from both primary and foreign type of table forming the composite primary key for junction table.

What are user defined data types and when you should go for them?
User-data types enable extension of Basic SQL Server data. It provides the descriptive name and format to the database. For example, there is a Flight_Num in your column and this column consists of many tables. As there should be varchar(8) in those tables, you may create a user-defined data by creating a Flight_num_type of varchar(8) and apply it for the whole column.

What is Lock Escalation?
Lock escalation is a process in which lower-security level shall be converted into higher-security level (locks). Each lock is a structure of memory. Lower security locks relate to lower structure of memory while higher-security one shall be able to cover greater memory. To avoid the problem of having too many locks for a memory system, one may adopt higher-security locks and therefore, lock escalation process is performed. There is a limit for lock escalation in SQL Server 6.5, however, in SQL Server 7.0 or higher, this limit is removed and lock escalation can be performed dynamically.

Explain the storage models of OLAP
Refer to SQL Server Guide online on MOLAP, ROLAP and HOLAP for more information This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled “What’s New”, which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled “Backward Compatibility” in books online which talks about the changes that have taken place in the new version.

What is a deadlock and what is a live lock? How will you go about resolving deadlocks?
A Deadlock is when two processes attempt to get the lock of each other. accordingly, each process itself shall wait for the other process to release its lock, and this process shall never end unless one process is terminated. SQL Server shall detect any deadlock problem and terminate one user process to resolve it.

A livelock is an automatically activated lock when there is a series of overlapping requests which have been denied. Normally, after four denials, SQL Server shall automatically activate a livelock for the 5th. A livelock is also activated when reading transaction is dominant and forces writing one to wait.

Check out SET DEADLOCK_PRIORITY and “Minimizing Deadlocks” in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.

What are statistics, under what circumstances they go out of date, how do you update them?
Statistics are the indicators of the selectivity of the indexes. When an indexed column has specific values, it has higher selectivity. Such indexes shall be used by query optimizer to determine the appropriate index for executing the query.

You should update your statistics when:
1. There is a significant change in key values of the index
2. A large amount of data in the indexed column is modified, changed or removed or the table is restructured and repopulated by TRUNCATE TABLE

You can update the statistics from previous versions following the command:1. UPDATE STATISTICS
6. sp_autostats,
7. sp_createstats,
8. sp_updatestats

What is the ‘FILLFACTOR’?
“FILLFACTOR” is one of the important arguments that can be used while creating an index. According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth. Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt.

What are Points to Remember while Using the FILL FACTOR Argument?
If fill-factor is set to 100 or 0, the Database Engine fills pages to their capacity while creating indexes.
The server-wide default FILLFACTOR is set to 0.
To modify the server-wide default value, use the sp_configure system stored procedure.
To view the fill-factor value of one or more indexes, use sys.indexes.
To modify or set the fill-factor value for individual indexes, use CREATE INDEX or ALTER INDEX statements.
Creating a clustered index with a FILLFACTOR < 100 may significantly increase the amount of space the data occupies because the Database Engine physically reallocates the data while building the clustered index.

What are Various Limitations of the Views?
1.   ORDER BY clause does not work in View.
2.   Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, then we will have to modify them first.
3.   Index created on view not used often.
4.   Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.
5.   One of the most prominent limitations of the View it is that it does not support COUNT (*); however, it can support COUNT_BIG (*).
6.   UNION Operation is now allowed in Indexed View.
7.   We cannot create an Index on a nested View situation means we cannot create index on a view which is built from another view.
8.   SELF JOIN Not Allowed in Indexed View.
9.   Outer Join Not Allowed in Indexed Views.
10.  Cross Database Queries Not Allowed in Indexed View.

What are Wait Types?
There are three types of wait types, namely, 
Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker or it’s not yet available.
Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
External Waits. External waits occur when an SQL Server worker is waiting for an external event.

How to Stop Log File Growing too Big?
If your Transaction Log file was growing too big and you wanted to manage its size, then instead of truncating transaction log file, you should choose one of the options mentioned below.

1) Convert the Recovery Model to Simple Recovery
If you change your recovery model to Simple Recovery Model, then you will not encounter the extraordinary growth of your log file. However, please note if you have one long running transaction it will for sure grow your log file till the transaction is complete.

2) Start Taking Transaction Log BackupIn this Full Recovery Model, your transaction log will grow until you take a backup of it. You need to take the T-Log Backup at a regular interval. This way, your log would not grow beyond some limits.

What are fact tables and dimension tables in OLAP? or  What is star schema in OLAP?
The dimensions and measures are physically represented by a star schema. Dimension tables revolve around fact table. A fact table contains a column for each measure as well as a column for each dimension. Each dimension column has a foreign-key relationship to the related dimension table, and the dimension columns taken together are the key to the fact table.

What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and
increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers,
the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.

What is the use @@identity in sql?
It returns the last inserted value of an identity column.

What is @@ERROR?
The @@ERROR automatic variable returns the error code of the last Transact- SQL statement. If there
was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raiseerror?
Stored procedures report errors to client applications via the RAISERROR command. RAISERROR
doesn't change the flow of a procedure; it merely displays an error message, sets the @@ERROR
automatic variable, and optionally writes the message to the SQL Server error log and the NT
application event log. 

What are the properties of the Relational tables? 
Relational tables have six properties: 
1. Values are atomic.
2. Column values are of the same kind.
3. Each row is unique.
4. The sequence of columns is insignificant.
5. The sequence of rows is insignificant.
6. Each column must have a unique name.

What is DataWarehousing?
Subject-oriented, meaning that the data in the database is organized so that all the data elements relating to the same real-world event or object are linked together; Time-variant, meaning that the changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time; Non-volatile, meaning that data in the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting; Integrated, meaning that the database contains data from most or all of an organization's operational applications, and that this data is made consistent.

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.


Post a Comment