SQL Server DBA interview
questions
What
are the steps to take to improve performance of a poor performing query?
- Maximum use of
indexes, stored procures should be done.
- Avoid
excessive use of complicated joins and cursors.
- Avoid using
conditional operators using columns of different tables.
- Make use of
computed columns and rewriting the query.
What is a deadlock and what is a live
lock? How will you go about resolving deadlocks?
A deadlock occurs when two or more processes waits for a resource
that is acquired by or is under the control of another process. A live lock is
similar to a deadlock except the process states keeps changing. The result of
such state is that none of the process will be complete.
Deadlock detection finds and resolves deadlocks. A WFG strategy is
followed. WFG is wait for graph. In WFG, processes are represented by nodes
while dependencies are represented by edges. Thus, if process A is waiting for
a resource held by process B, there is an edge in the WFG from the node for
process A to the node for process B. a cycle is this graph is a deadlock. WFG
constantly checks for cycles or when a process is blocked and adds a new edge
to the WFG. When a cycle is found, a
victim is selected and aborted.
What is blocking and how would you troubleshoot it?
Blocking occurs when two or more rows are locked by one SQL
connection and a second connection to the SQL server requires a conflicting on
lock on those rows. This results in the second connection to wait until the
first lock is released.
Troubleshooting blocking:
- SQL scripts
can be written that constantly monitor the state of locking and blocking
on SQL Server
- The common
blocking scenarios must be identified and resolved.
- The scripts
output must be checked constantly,
- The SQL
profilers data must be examined regularly to detect blocking.
Explain
the different types of BACKUPs available in SQL Server.
Complete database backup: This type of backup will backup all the
information in the database. Used most commonly for disaster recovery and takes
the longest time to backup.
Differential databse backup: The database is divided into
partitions that have been modified since last complete backup. Most suitable
for large databases. The most recent differential backup contains the changes
from previous backups.
Transaction log backups: Backups only the changes logged in the
transaction log. The transaction log has all changes logged about a database.
Once the changes are accommodated on the database, the log is truncated or
backed up.
File/File Group backups: used to recover individual files or file
groups. Each filegroup can be individually backed up. This helps in recovery
only the required file or filegroup for disaster recovery.
What is database isolation in SQL
Server?
Isolation in database defines how and when changes made by one
transaction can be visible to other transactions. Different isolation levels
are:
- Serializable
- Repeatable
read
- Read committed
- Read
uncommitted
What is a Schema in SQL Server 2005?
Explain how to create a new Schema in a Database?
A schema is used to create database objects. It can be created
using CREATE SCHEMA statement. The objects created can be moved between
schemas. Multiple database users can share a single default schema.
CREATE SCHEMA sample;
Table creation
Create table sample.sampleinfo
{
id int primary key,
name varchar(20)
}
Explain how to create a Scrollable
Cursor with the SCROLL Option.
Using the SCROLL keyword while declaring a cursor allows fetching
of rows in any sequence
Example:
DECLARE employee_curs SCROLL CURSOR FOR SELECT * FROM employee;
The active set of the cursor is stored can be accessed in any
order without the need of opening and closing the cursor. The Scroll cursors
can be set for select and function cursors but not insert or update statements.
Explain how to create a Dynamic Cursor
with the DYNAMIC Option.
When a cursor is declared as DYNAMIC, the cursor reflects all
changes made to the base tables as the cursor is scrolled around.
Declare cursor_name cursor
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
FOR select_statement
The dynamic option does not support ABSOLUTE FETCH.
What
are database files and filegroups?
Database files are used for mapping the database over some
operating system files. Data and log information are separate. SQL server
database has three types of database files:
Primary: starting point of a database. It also points to other
files in database. Extension: .mdf
Secondary: All data files except primary data file is a part of
secondary files. Extension: .ndf
Log files: All log information used to recover database.
Extension: .ldf
Describe
in brief Databases and SQL Server Databases Architecture.
SQL Server consists of a set of various components which fulfill
data storage and data analysis needs for enterprise applications. Database
architecture: All the data is stored in databases which is organized into
logical components visible to the end users. It’s only the administrator who
needs to actually deal with the physical storage aspect of the databases,
whereas users only deal with database tables.
Every SQL Server instance has primarily 4 system database i.e.
master, model, tempdb and msdb. All other databases are user created databases as
per their needs and requirements.
A single SQL Server instance is capable of handling thousands of
users working on multiple databases.
What is RDBMS?
Relational Data Base Management Systems (RDBMS) are database management systems that maintain
data records and indices in tables. Relationships may be created and maintained across and among the
data and tables. In a relational database, relationships between data items are expressed by means of
tables. Interdependencies among these tables are expressed by data values rather than by pointers.
This allows a high degree of data independence. An RDBMS has the capability to recombine the data
items from different files, providing powerful tools for data usage.
What is normalization?
Database normalization is a data design and organization process applied to data structures based on
rules that help build relational databases. In relational database design, the process of organizing data
to minimize redundancy. Normalization usually involves dividing a database into two or more tables and
defining relationships between the tables. The objective is to isolate data so that additions, deletions,
and modifications of a field can be made in just one table and then propagated through the rest of the
database via the defined relationships.
What are different normalization forms?
1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field
contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a separate table. All
attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct
tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships
There may be practical constrains on information that justify separating logically related many-to-many
relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first
fulfill all the criteria of a 2NF and 1NF database.
What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored
in the server database. Stored procedures accept input parameters so that a single procedure can be
used over the network by several clients using different input data. And when the procedure is
modified, all clients automatically get the new version. Stored procedures reduce network traffic and
improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.
What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE)
occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential
integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed;
the DBMS automatically fires the trigger as a result of a data modification to the associated table.
Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is
stored at the database level. Stored procedures, however, are not event-drive and are not attached to a
specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the
procedure while triggers are implicitly executed. In addition, triggers can also execute stored
procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the
trigger is fired because of data modification it can also cause another data modification, thereby firing
another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as
updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the
view was created with. It should also be noted that as data in the original table changes, so does data
in the view, as views are the way to look at part of the original table. The results of using a view are
not permanently stored in the database. The data accessed through a view is actually constructed using
standard T-SQL select command and can come from one to many different base tables or even other
views.
What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table
to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of
a table, and each index is given a name. The users cannot see the indexes, they are just used to speed
up queries. Effective indexes are one of the best ways to improve performance in a database
application. A table scan happens when there is no index available to help a query. In a table scan SQL
Server examines every row in the table to satisfy the query results. Table scans are sometimes
unavoidable, but on large tables, scans have a terrific impact on performance.
Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this
reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references
to the table itself.
What is the difference between clustered and a non-clustered index?
A clustered index is a special type of index that reorders the way records in the table are physically
stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain
the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match
the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of
the data pages. Instead, the leaf nodes contain index rows.
What are the different index configurations a table can have?
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes
What is cursors?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis,
instead of the typical SQL commands that operate on all the rows in the set at one time.
In order to work with a cursor we need to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor.
In order to work with a cursor we need to perform some steps in the following order:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor
What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE
clause. Truncate will actually remove all the rows from a table and there will be no data in the table
after we run the truncate command.
TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the
page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes
and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition
and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as
Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other
Rowset operations.
When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of
deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to
take these changes into account. UPDATE_STATISTICS updates the indexes on these tables
accordingly.
What types of Joins are possible with Sql Server?
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.
What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE
clause. Truncate will actually remove all the rows from a table and there will be no data in the table
after we run the truncate command.
TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the
page deallocations are recorded in the transaction log.
TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes
and so on remain. The counter used by an identity for new rows is reset to the seed for the column.
You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
TRUNCATE can not be Rolled back.
TRUNCATE is DDL Command.
TRUNCATE Resets identity of the table.
DELETE
DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to remove table definition
and its data, use the DROP TABLE statement.
DELETE Can be used with or without a WHERE clause
DELETE Activates Triggers.
DELETE Can be Rolled back.
DELETE is DML Command.
DELETE does not reset identity of the table.
Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as
Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other
Rowset operations.
When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of
deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to
take these changes into account. UPDATE_STATISTICS updates the indexes on these tables
accordingly.
What types of Joins are possible with Sql Server?
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.
