|
Part 1 | Part 2 | Part 3 | Part 4 | part 5 |
part 6 | part 7 | part 8 | part
9
QUESTION
- Difference between DELETE and TRUNCATE commands in SQL
Server.
ANSWER -
DELETE TABLE is a logged operation, it is a slow
process.
TRUNCATE TABLE
deletes all the rows, but it won't log the deletion,
conquently it is fast process.
TRUNCATE TABLE
can't be rolled back.
QUESTION -
What are constraints in SQL
Server?
ANSWER -
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.
QUESTION -
What is an index?
ANSWER -
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.
Disadvantages of
the Indexes are
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.
QUESTION -
What is RAID?
ANSWER -
RAID stands for Redundant Array of Inexpensive
Disks.
It provide
fault tolerance to database servers.
There are six RAID
levels 0 through 5 offering different levels of performance,
fault tolerance.
QUESTION -
Reasons of poor performance of
query.
ANSWER -
No indexes
Excess
recompilations of stored procedures.
Procedures and
triggers without SET
NOCOUNT
ON.
Poorly written
query with unnecessarily complicated joins,
Highly
normalized database design.
Excess usage of
cursors and temporary tables.
QUESTION -
Ways to troubleshoot performance problems in SQL
Server.
ANSWER -
SET SHOWPLAN_ALL ON
SET SHOWPLAN_TEXT
ON
SET STATISTICS IO
ON
SQL Server
Profiler
Windows NT /2000
Performance monitor
Graphical execution
plan in Query Analyzer
QUESTION -
Steps to secure an SQL Server.
ANSWER
- Use NT authentication.
Use server database
and application roles to control access to the
data.
Secure the physical
database files using NTFS permissions.
Use an ungues sable
SA password.
Restrict physical
access to the SQL Server.
Rename the
Administrator account on the SQL Server
computer.
Disable the Guest
account.
Enable
auditing.
Use multiprotocol
encryption.
Set up
SSL.
Set up
firewalls.
Isolate SQL Server
from the web server etc.
QUESTION -
What is a deadlock and what is a live
lock?
ANSWER -
When two processes, each having a lock on one piece
of data, attempt to acquire a lock on the other's
piece. Each
process would wait indefinitely for the other to release
the lock unless one of the user processes is
terminated. SQL Server detects
deadlocks and terminates one user's process.
A livelock is one,
where a request for an exclusive lock is repeatedly
denied because a series of overlapping shared locks keeps
interfering.A livelock also
occurs when read transactions monopolize a table or page,
forcing a write transaction to wait indefinitely.
QUESTION -
What is blocking?
ANSWER -
When one connection from an application holds a lock
and a second connection requires a conflicting lock
type.
Ways of moving
data/databases between servers and databases in SQL
Server?
ANSWER -
BACKUP/RESTORE,
Dettach/attach
of databases,
Replication, DTS,
BCP, logshipping,
INSERT...SELECT,
SELECT...INTO, creating INSERT scripts to generate data.
QUESTION -
Explian different types of BACKUPs avaialabe in SQL
Server?
ANSWER -
Full database backup.
Differential
database backup.
Transaction log
backup.
Filegroup backup.
QUESTION -
What is database replicaion?
ANSWER -
The process of copying/moving data between databases
on the same or different
servers.
Snapshot
replication,
Transactional
replication,
Merge
replication.
Part 1 | Part 2 | Part 3 | Part 4 | part 5 |
part 6 | part 7 | part 8 | part
9
|