What is De-normalization?
De-normalization is the process of
attempting to optimize the performance of a database by adding redundant data.
It is sometimes necessary because current DBMSs implement the relational model
poorly. A true relational DBMS would allow for a fully normalized database at
the logical level, while providing physical storage of data that is tuned for
high performance. De-normalization
is a technique to move from higher to lower normal forms of database modeling
in order to speed up database access.
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;
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.
What is a Linked Server?
Linked Servers is a concept in SQL
Server by which we can add other SQL Server to a Group and query both the SQL
Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to
follow, SQL statements that allow remote data to be retrieved, joined and
combined with local data. Stored Procedure
sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked
Server. 
0 comments
Post a Comment