Advantage of Stored Procedure?
faster execution,reduce network traffic,Modular
programming,reduced operator eror,enforced consistency
can I update view?
yes,but view had created using only one table than update is
possiable
explain about @@sqlstatus?
It returns the status of the pervious fetch statement in a
cursor
What is the disadvantage of CURSOR?
Each time you fetch a row from the cursor, it results in a
network roundtrip, where as a normal SELECT query makes only one rowundtrip,
however large the resultset is. Cursors are also costly because they require
more resources and temporary storage (results in more IO operations). Furthere,
there are restrictions on the SELECT statements that can be used with some
types of cursors. Most of the times, set based operations can be used instead
of cursors.
What are the Properties of Transaction?
server maintain transaction log,server locks table pages
during transaction,server performs automatic recovery upon restart ,transaction
control statement
What is lock escalation?
Lock escalation is the process of converting a lot of low
level locks (like row locks, page locks) into higher level locks (like table
locks).
How do I get the Duplicate rows from a table?
select * from table name group by column1 having
count(*)>1
What is Checkpoint?
The point at which all data pages that have been changed are
guaranteed to have been written to the database device.
What is Distribution page?
server keeps distribution information for each index on a
separate page in the datebase
How shall I simulate from level 0 to level 3 in Isolation?
using holdlock
Compare Join and SubQuery in performance?
Generally Join queries consume more memory than sub query.
Sub query in turn involve intermediate table creation so affects performance,
because of high I/Os fetches need for processing. If the RAM memory is more,
then the Joins can be used instead of sub queries. Under memory constraints we
can go for sub queries. Sometimes sub queries are flattened to join to improve
the performance. Also making the outer query to compare inner query with equality
option can materialize the subquery.
What is ceiling()?
Smallest integer greater than or equal to specified value
What is the stored procedure to view current lock and
processes respectively?
sp_lock and sp_who
Compare IN and EXISTS?
in allow duplicate values and sub query have oe
column,exists not allow duplicates and inner query can have multi columns
What is Rollback trigger in a Trigger?
You can roll back triggers using either the rollback trigger
statement or the rollback transaction statement (if the trigger is fired as
part of a transaction). However, rollback trigger rolls back only the effect of
the trigger and the statement that caused the trigger to fire; rollback
transaction rolls back the entire transaction
What happen when we delete the table using Trigger?
The delete row will entered into the Deleted table inside
the trigger
What is the Drawbacks of Normalization?
Although most successful databases are normalized to some
degree, there is one substantial drawback of a normalized database: reduced
database performance. The acceptance of reduced performance requires the
knowledge that when a query or transaction request is sent to the database,
there are factors involved, such as CPU usage, memory usage, and input/output
(I/O). To make a long story short, a normalized database requires much more
CPU, memory, and I/O to process transactions and database queries than does a
denormalized database. A normalized database must locate the requested tables
and then join the data from the tables to either get the requested information
or to process the desired data. A more in-depth discussion concerning database
performance occurs in Hour 18, "Managing Database Users."
No comments:
Post a Comment