SQL Interview Questions with Answers: Top SQL Interview Questions

What is normalization? Explain unique proportions of normalization?

Check out the survey Q100139 based on Microsoft knowledge base and of
course, there is significantly !no! guidelines accessible in the net. It'll be a
good thinking to get a have of any RDBMS essentials text book,
especially the one by C. J. Date. Most of the times, it may be okay
if you can explain till third ordinary form.

What is denormalization and when is able to you go for it?

As the and cr indicates, denormalization is the contrary technique of
normalization. It's the controlled introduction of redundancy in to
the database design. It supports develop the query performance as the
number of joins serves to be reduced.

How do you embark on one-to-one, one-to-many and many-to-many
relationships additonally designing tables?

One-to-One relationship can be planned as a single table and
rarely as two tables amongst main and international key relationships.
One-to-Many relationships are intended by splitting the information into
two tables amidst chief key and intercontinental key relationships.
Many-to-Many relationships are designed making use of a junction table with
the keys based on what i read in both the tables forming the composite main key of the
junction table.

It plans to be a desired notion to was reading up a database designing fundamentals
text book.

What's the change between a chief key and a distinct key?

Both number one key and distinct enforce uniqueness of the column on which
they are defined. But by default chief key creates a clustered index
on the column, at which are unusual creates a nonclustered index by
default. Another monumental change is that, number one key does not allow
NULLs, but valuable key offers one NULL only.

What are user defined datatypes and when you might go for them?

User defined datatypes let you give the base SQL Server datatypes by
providing a descriptive name, and format to the database. Take for
example, in your database, there is a column labeled Flight_Num which
appears in different tables. In all such tables it serves to be varchar(8).
In such a state of affairs you were able to implement a user defined datatype called
Flight_num_type of varchar(8) and use it everywhere all your tables.

See sp_addtype, sp_droptype in books online.

What is bit datatype and what is the hints overly can be stored
inside a bit column?

Bit datatype is spent to list boolean data similar to 1 or 0 (true or
false). Untill SQL Server 6.5 bit datatype are able to own either a 1 or 0
and there was no validation for NULL. But from what i read in SQL Server 7.0 onwards,
bit datatype can speak for a third state, that is NULL.

Define candidate key, alternate key, composite key.

A candidate key is one overly can identify every row of a table uniquely.
Generally a candidate key gets the number one key of the table. If the
table has other as opposed to one candidate key, one of them is able to become the
primary key, and the rest are identified alternate keys.

A key made by combining at the very least two or a multitude of columns is called
composite key.

What are defaults? Is there a column to that a default won't be bound?

A default is a expense the present will be able to be used up by a column, if no price level is
supplied to too column additonally putting in data. IDENTITY columns and
timestamp columns won't suffer loan failures bound to them. See CREATE
DEFUALT in books online.

Back to top
SQL Server architecture (top)

What is a condition and how are ACID properties?

A sell is a levelheaded unit of grunt work in which, all the steps must
be performed or none. ACID stands for Atomicity, Consistency,
Isolation, Durability. These are the residences of a transaction. For
more guidelines and trigger of such properties, see SQL Server
books online or any RDBMS basics text book.

Explain many isolation levels

An isolation total sum determines the degree of isolation of info between
concurrent transactions. The default SQL Server isolation quantity is
Read Committed. Here are the a larger number of isolation prices (in the ascending
order of isolation): Read Uncommitted, Read Committed, Repeatable
Read, Serializable. See SQL Server books online for an motive of
the isolation levels. Be definite to saw approximately SET TRANSACTION ISOLATION
LEVEL, that lets you customize the isolation rate at the connection
level.

CREATE INDEX myIndex ON myTable(myColumn)

What sort of Index am able to get came up with ensuing executing the above statement?

Non-clustered index. Important happening to note: By default a clustered
index becomes came up with on the number one key, unless specified otherwise.

What's the maximum size of a row?

8060 bytes. Don't be surprised in on topics as if 'what is the
maximum total sum of columns per table'. Check out SQL Server books
online for the web site titled: "Maximum Capacity Specifications".

Explain Active/Active and Active/Passive assembly configurations

Hopefully you experience have setting up collection servers. But if you
don't, at minimum be familiar in the way clustering functions and the two
clusterning configurations Active/Active and Active/Passive. SQL
Server books online has an adequate amount of info on their topic and there is
a the best grey paper on hand on Microsoft site.

Explain the architecture of SQL Server

This is a truly pertinent issue and you higher be able to solution it
if ponder yourself a DBA. SQL Server books online is the most ideal place
to read over throughout SQL Server architecture. Read up the chapter dedicated
to SQL Server Architecture.

What is lock escalation?

Lock escalation is the technique of converting a lot of low total amount locks
(like row locks, web page locks) to better grade locks (like table
locks). Every lock is a memory house too most locks is able to mean,
more memory making populated by locks. To hold off their based on what i read in happening,
SQL Server escalates the a large number of fine-grain locks to a lower amount of coarse-grain
locks. Lock escalation threshold was definable in SQL Server 6.5, but
from SQL Server 7.0 onwards it is dynamically strive by SQL Server.

What's the change between DELETE TABLE and TRUNCATE TABLE commands?

DELETE TABLE is a logged operation, so the deletion of every row gets
logged in the purchase log, that affects it slow. TRUNCATE TABLE
also deletes all the rows in a table, but it will not log the deletion of
each row, rather it logs the deallocation of the information web sites of the
table, that causes it faster. Of course, TRUNCATE TABLE can be rolled
back.

Explain the storage examples of OLAP

Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more
infomation.

What are the new parts introduced in SQL Server 2000 (or the latest
release of SQL Server at the while of your interview)? What changed
between the first version of SQL Server and the contemporary version?

This measure is usually surveyed to see how up to date is your knowledge.
Generally there is a section in the begun of the books online
titled "What's New", that has all this type of information. Of course,
reading just now who is not enough, you may experience tried people conditions to
better solution the questions. Also assess out the section titled
"Backward Compatibility" in books online that talks close to the changes
that undergo taken place in the new version.

What are constraints? Explain several sorts of constraints.

Constraints enable the RDBMS enforce the integrity of the database
automatically, without needing you to form triggers, fundamental or defaults.

Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY

For an rationale of these types of constraints see books online for the pages
titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"

Whar is an index? What are the sorts of indexes? How multiple clustered
indexes can be made on a table? I construct a separate index on each
column of a table. how are the benefits and disadvantages of this
approach?

Indexes in SQL Server are similar to the indexes in books. They help
SQL Server retrieve the info quicker.

Indexes are of two types. Clustered indexes and non-clustered indexes.
When you craete a clustered index on a table, all the rows in the
table are stored in the shape of the clustered index key. So, there
can be just one clustered index per table. Non-clustered indexes have
their own storage separate based on data from the table info storage. Non-clustered
indexes are stored as B-tree homes (so do clustered indexes),
with the leaf grade nodes in posession of the index key and it is row locater.
The row located can be the RID or the Clustered index key, depending
up on the absence or occurrence of clustered index on the table.

If you initiate an index on every column of a table, it improves the
query performance, as the query optimizer can decide on based on information from all the
existing indexes to arrive up providing an small execution plan. At the
same t ime, info revision operations (such as INSERT, UPDATE,
DELETE) might become slow, as each age info fluxes in the table, all
the indexes fancy to be updated. Another disadvantage is that, indexes
need disk space, the !no! indexes you have, other disk space is used.

Back to top
Database administration (top)

What is RAID and how are unusual sorts of RAID configurations?

RAID stands for Redundant Array of Inexpensive Disks, used up to provide
fault tolerance to database servers. There are six RAID numbers 0
through 5 submiting strange value of performance, fault tolerance.
MSDN has a good deal of data right about RAID values and for detailed
information, examine out the RAID advisory board's homepage

What are the steps you serves to take to boost performance of a poor
performing query?

This is a essentially open concluded ask and there could easily be a lot of reasons
behind the faulty performance of a query. But selected whole difficulties that
you are able to jargon roughly ought to be: No indexes, table scans, missing or out
of date statistics, blocking, excess recompilations of stored
procedures, rules and triggers without SET NOCOUNT ON, poorly
written query amidst unnecessarily complicated joins, too much
normalization, excess usage of cursors and temporary tables.

Some of the tools/ways this assistance you troubleshooting performance
problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET
STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance
monitor, Graphical execution initiative in Query Analyzer.

Download the grey paper on performance tuning SQL Server from
Microsoft web site. Don't forget to evaluate out sql-server-performance.com

What are the steps you would take, if you are tasked among securing an
SQL Server?

Again now is one more open finished question. Here are Other details you
could lingo about: Preferring NT authentication, making the most of server, databse
and software roles to control entrance to the data, securing the
physical database files employing NTFS permissions, making use of an unguessable
SA password, restricting physical entrance to the SQL Server, renaming
the Administrator consideration on the SQL Server computer, disabling the
Guest account, enabling auditing, paying off multiprotocol encryption,
setting up SSL, setting up firewalls, isolating SQL Server for the
web server etc.

Read the grey paper on SQL Server security according to Microsoft website.
Also inspect out My SQL Server security proper practices

What is a deadlock and how is a reside lock? How am able to you go about
resolving deadlocks?

Deadlock is a circumstances when two processes, every invested in a lock on one
piece of data, try to obtain a lock on the other's piece. Each
process may wait eternally for the opposite to release the lock,
unless one of the user manages is terminated. SQL Server detects
deadlocks and terminates one user's process.

A livelock is one, at which a ask for for an exclusive lock is
repeatedly denied while a string of overlapping shared locks keeps
interfering. SQL Server detects the condition once two denials and
refuses a large amount of shared locks. A livelock furthermore takes place when read
transactions monopolize a table or page, forcing a come up with transaction
to wait indefinitely.

Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL
Server books online. Also look over out the content Q169960 based on information from Microsoft
knowledge base.

What is blocking and how ought to you troubleshoot it?

Blocking takes place when one connection for an software holds a lock
and a instant connection requires a conflicting lock type. This forces
the minute connection to wait, blocked on the first.

Read up the next concerns in SQL Server books online: Understanding
and averting blocking, Coding tiny transactions.

Explain CREATE DATABASE syntax

Many of us are spent to craeting databases from what i read in the Enterprise Manager
or by just now furnishing the command: CREATE DATABAE MyDB. But how if you
have to build a database amid two filegroups, one on impel C and the
other on force D through log on urge E amid an previous size of 600 MB
and provided a increment cog of 15%? That's why making a DBA you as long as be
familiar through the CREATE DATABASE syntax. Check out SQL Server books
online for more and more information.

How to restart SQL Server in single user mode? How to initiate SQL Server
in tiny configuration mode?

SQL Server can be began on command line, making use of the SQLSERVR.EXE.
This EXE has a couple of particularly monumental parameters in that a DBA will be
familiar with. -m is depleted for appearing SQL Server in single user mode
and -f is spent to initiate the SQL Server in pithy confuguration mode.
Check out SQL Server books online for !no! parameters and their
explanations.


As a half of your job, how are the DBCC commands such a you commonly
use for database maintenance?

DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC,
DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there
are a entire mountain of DBCC commands that are essentially useful for DBAs.
Check out SQL Server books online for a larger number of information.

What are statistics, underneath how shape properties go out of date, how
do you update them?

Statistics determine the selectivity of the indexes. If an indexed
column has top notch cost levels next the selectivity of such a index is more,
as opposed to an index providing non-unique values. Query optimizer uses
these indexes in determining whether to decide on an index or not while
executing a query.

Some circumstances below that you must update statistics:
1) If there is major tweak in the key market values in the index
2) If a considerable quantity of information in an indexed column has been heard added,
changed, or removed (that is, if the distribution of key price has
changed), or the table has continued truncated making use of the TRUNCATE TABLE
statement and at that time repopulated
3) Database is upgraded from what i read in a earliest version

Look up SQL Server books online for the coming commands: UPDATE
STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP
STATISTICS, sp_autostats, sp_createstats, sp_updatestats

What are the multitude of ways of moving data/databases between servers
and databases in SQL Server?

There are oodles of opportunities available, you own to decide your option
depending upon your requirements. Some of the opportunities you hold are:
BACKUP/RESTORE, dettaching and attaching databases, replication, DTS,
BCP, logshipping, INSERT...SELECT, SELECT...INTO, generating INSERT
scripts to make data.

Explian weird sorts of BACKUPs avaialabe in SQL Server? Given a
particular scenario, how can you go something like opting for a backup plan?

Types of backups you can initiate in SQL Sever 7.0+ are Full database
backup, differential database backup, arrangement log backup,
filegroup backup. Check out the BACKUP and RESTORE commands in SQL
Server books online. Be willing to spit out the commands in your
interview. Books online additionally has data on detailed
backup/restore architecture and when one providing go for a particular
kind of backup.

What is database replicaion? What are the diverse sorts of
replication you can set up in SQL Server?

Replication is the technique of copying/moving info between databases on
the same or multiple servers. SQL Server emphasizes the next types
of replication scenarios:

* Snapshot replication
* Transactional replication (with immediate updating subscribers,
with queued updating subscribers)
* Merge replication

See SQL Server books online for indepth indemnity on replication. Be
prepared to explain how strange replication realtors function, what
are the number one process tables expended in replication etc.

How to determine the benefits pack by now installed on SQL Server?

The intercontinental variable @@Version stores the fashion total number of the
sqlservr.exe, that is exhausted to determine the treatment pack installed.
To appreciated further in regards to that procedure visit SQL Server assistance packs and
versions.

Back to top
Database programming (top)

What are cursors? Explain several sorts of cursors. What are the
disadvantages of cursors? How can you prevent cursors?

Cursors let row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See
books online for supplementary information.

Disadvantages of cursors: Each cycle you fetch a row of the cursor,
it possible results in a network roundtrip, at which as a conventional SELECT query
makes just one rowundtrip, but then larger the resultset is. Cursors are
also steep when properties motivate larger amount of equipment and temporary storage
(results in a good amount IO operations). Furthere, there are restrictions on
the SELECT statements which can be expended in on a little sorts of cursors.

Most of the times, set rooted operations can be expended rather of
cursors. Here is an example:

If you experience to offer a flat hike to your workforces paying off the following
criteria:

Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike

In that state a good number of developers have a propensity to use a cursor, determine each
employee's salary and update his salary according to the above
formula. But the same can be reached by numerous update statements or
can be combined in a single UPDATE statement as substantiated below:

UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END

Another problem in that developers have a tendency to use cursors: You seek to
call a stored process when a column in a individual row meets
certain condition. You do not experience to use cursors for this. This can be
achieved making the most of WHILE loop, as extensively as there is a different key to
identify every row. For tendencies of paying WHILE loop for row by row
processing, take a look at out the 'My code library' section of my site or
search for WHILE.

Write decreased the overall syntax for a SELECT statements covering all the
options.

Here's the easy syntax: (Also checkout SELECT in books online for
advanced syntax).

SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by__expression]
[HAVING search_condition]
[ORDER BY order__expression [ASC | DESC] ]

What is a join and explain unusual kinds of joins.

Joins are used up in problems to explain how strange tables are related.
Joins moreover let you specific info of a table depending upon information 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.

For a great deal more tips see web sites based on data from books online titled: "Join
Fundamentals" and "Using Joins".

Can you undergo a nested transaction?

Yes, really much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and
@@TRANCOUNT


What is an long stored procedure? Can you instantiate a COM object
by paying for T-SQL?

An long stored course is a function during a DLL (written in a
programming terminology as C, C++ paying for Open Data Services (ODS) API)
that can be referred to as based on information from T-SQL, simply the way we employ standard stored
procedures paying for the EXEC statement. See books online to decide how to
create long stored rules and how to add them to SQL Server.

Yes, you can instantiate a COM (written in languages want VB, VC++)
object based on what i read in T-SQL by employing sp_OACreate stored procedure. Also see
books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty,
sp_OADestroy. For an model of producing a COM object in VB and
calling it from what i read in T-SQL, see 'My code library' section of currently site.

What is the method function to get the the most recent user's user id?

USER_ID(). Also assess out a greater number of method runs want USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What are triggers? How multi triggers you can experience on a table? How to
invoke a rationale on demand?

Triggers are special type of stored plan overly get executed
automatically when an INSERT, UPDATE or DELETE utilization takes place
on a table.

In SQL Server 6.5 you serves to define merely 3 triggers per table, one for
INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0
onwards, the present restriction is gone, and you might compose multiple
triggers per every action. But in 7.0 there is no way to control the
order in that the triggers fire. In SQL Server 2000 you am able to specify
which instigate fires first and foremost or fires go on making use of sp_settriggerorder

Triggers can not be invoked on demand. They get triggered easily when an
associated action (INSERT, UPDATE, DELETE) takes place on the table on
which properties are defined.

Triggers are normally used up to craft market rules, auditing.
Triggers can too be spent to give the referential integrity checks,
but wherever possible, use constraints for presently purpose, alternatively of
triggers, as constraints are still faster.

Till SQL Server 7.0, triggers fire one and only in the wake of the info modification
operation happens. So in a way, properties are identified give triggers. But in
SQL Server 2000 you would engender pre triggers also. Search SQL Server
2000 books online for INSTEAD OF triggers.

Also assess out books online for 'inserted table', 'deleted table' and
COLUMNS_UPDATED()

There is a lead to defined for INSERT operations on a table, in an
OLTP system. The reason is written to instantiate a COM object and
pass the newly insterted rows to it for select individualized processing. What
do you agree of this moment implementation? Can the be calculated better?

Instantiating COM objects is a second consuming technique and as of you
are working at it based on what i read in within the duration of a trigger, it slows low the info insertion
process. Same is the circumstances providing sending emails based on triggers. This
scenario can be higher designed by logging all the pertinent data
into a separate table, and hold a job that periodically checks this
table and performs the needful.

What is a self join? Explain it in on an example.

Self join is only such as any more and more join, apart from such a two instances of
the same table can be joined in the query. Here is an example:
Employees table that contains rows for standard workforces as far as
managers. So, to submit out the managers of all the employees, you need
a self join.

CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)

INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'

SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid

Here's an advanced query paying off a LEFT OUTER JOIN the current a good deal takings the
employees without managers (super bosses)

SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid