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 r
ecombine 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 p
rocess 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 t
hat additions, deletions,
and modifications of a field can be made in just one table and then propagated t
hrough 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 separ
ate table.
3NF: Eliminate Columns Not Dependent On Key
If attributes do not contribute to a description of the key, remove them to a se
parate 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 logical
ly related many-to-many
relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Mo
del 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 sing
le procedure can be
used over the network by several clients using different input data. And when th
e procedure is
modified, all clients automatically get the new version. Stored procedures reduc
e 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, DEL
ETE or UPDATE)
occurs. Triggers are stored in and managed by the DBMS.Triggers are used to main
tain the referential integrity of data by changing the data in a systematic fash
ion. A trigger cannot be called or executed;
the DBMS automatically fires the trigger as a result of a data modification to t
he associated table.
Triggers can be viewed as similar to stored procedures in that both consist of p
rocedural logic that is
stored at the database level. Stored procedures, however, are not event-drive an
d are not attached to a
specific table as triggers are. Stored procedures are explicitly executed by inv
oking 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 withi
n itself, so when the
trigger is fired because of data modification it can also cause another data mod
ification, thereby firing
another trigger. A trigger that contains data modification logic within itself i
s 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 retri
eving data, as well as
updating or deleting rows. Rows updated or deleted in the view are updated or de
leted in the table the
view was created with. It should also be noted that as data in the original tabl
e changes, so does data
in the view, as views are the way to look at part of the original table. The res
ults of using a view are
not permanently stored in the database. The data accessed through a view is actu
ally constructed using
standard T-SQL select command and can come from one to many different base table
s or even other
views.
What is Index?
An index is a physical structure containing pointers to the data. Indices are cr
eated in an existing table
to locate rows more quickly and efficiently. It is possible to create an index o
n 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 que
ry. 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 st
orage 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 so
rted 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 th
e table are physically
stored. Therefore table can have only one clustered index. The leaf nodes of a c
lustered index contain
the data pages.
A nonclustered index is a special type of index in which the logical order of th
e index does not match
the physical stored order of the rows on disk. The leaf node of a nonclustered i
ndex 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 o
ne time.
In order to work with a cursor we need to perform some steps in the following or
der:
Declare cursor
Open cursor
Fetch row from the cursor
Process fetched row
Close cursor
Deallocate cursor
What is the use of DBCC commands?
DBCC stands for database consistency checker. We use these commands to check the
consistency of
the databases, i.e., maintenance, validation task and status checks.
E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly
linked.
DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.
DBCC CHECKFILEGROUP - Checks all tables file group for any damage.
What is a Linked Server?
Linked Servers is a concept in SQL Server by which we can add other SQL Server t
o a Group and query
both the SQL Server dbs using T-SQL Statements. With a linked server, you can cr
eate very clean, easy
to follow, SQL statements that allow remote data to be retrieved, joined and com
bined with local data.
Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new
Linked Server.
What is Collation?
Collation refers to a set of rules that determine how data is sorted and compare
d. Character data is
sorted using rules that define the correct character sequence, with options for
specifying casesensitivity,
accent marks, kana character types and character width.
What are different type of Collation Sensitivity?
Case sensitivity
A and a, B and b, etc.
Accent sensitivity
a and á, o and ó, etc.
Kana Sensitivity
When Japanese kana characters Hiragana and Katakana are treated differently, it
is called Kana
sensitive.
Width sensitivity
When a single-byte character (half-width) and the same character when represente
d as a double-byte
character (full-width) are treated differently then it is width sensitive.
What's the difference between a primary key and a unique key?
Both primary key and unique enforce uniqueness of the column on which they are d
efined. But by
default primary key creates a clustered index on the column, where are unique cr
eates a nonclustered
index by default. Another major difference is that, primary key doesn't allow NU
LLs, but unique key
allows one NULL only.How to implement one-to-one, one-to-many and many-to-many r
elationships while
designing tables?
One-to-One relationship can be implemented as a single table and rarely as two t
ables with primary
and foreign key relationships.
One-to-Many relationships are implemented by splitting the data into two tables
with primary key and
foreign key relationships.
Many-to-Many relationships are implemented using a junction table with the keys
from both the tables
forming the composite primary key of the junction table.
What is a NOLOCK?Using the NOLOCK query optimiser hint is generally considered g
ood practice in order to improve
concurrency on a busy system. When the NOLOCK hint is included in a SELECT state
ment, no locks are
taken when data is read. The result is a Dirty Read, which means that another pr
ocess could be
updating the data at the exact time you are reading it. There are no guarantees
that your query will
retrieve the most recent data. The advantage to performance is that your reading
of data will not block
updates from taking place, and updates will not block your reading of data. SELE
CT statements take
Shared (Read) locks. This means that multiple SELECT statements are allowed simu
ltaneous access, but
other processes are blocked from modifying the data. The updates will queue unti
l all the reads have
completed, and reads requested after the update will wait for the updates to com
plete. The result to
your system is delay(blocking).
What is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we prov
ide with a WHERE
clause. Truncate will actually remove all the rows from a table and there will b
e no data in the table
after we run the truncate command.
TRUNCATE
TRUNCATE is faster and uses fewer system and transaction log resources than DELE
TE.
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 s
eed 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 fo
r each deleted row.
If you want to retain the identity counter, use DELETE instead. If you want to r
emove 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 sectio
n where as
Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JO
INs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in J
OINs 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 als
o 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 c
lassified as LEFT
OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.