RDBMS
Relational Data Base Management Systems (RDBMS), that maintain data records and indices in tables. In a relational database, relationships between data items are expressed by means of tables.
Normalization
In relational database design, the process of organizing data to minimize redundancy is called normalization.
Different Normalization Forms
1NF: Eliminate Repeating Groups
Separate table for each set of related attributes and give each table a Primary key
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key
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
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 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 etc.
Trigger
A trigger is a SQL procedure that initiates an action when an event occurs (INSERT, DELETE or UPDATE). A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table.
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.
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. The results of using a view are not permanently stored in the database. When we update a value in the view it will change the Original table also.
Different Types of Join
Mainly 4 kind of join , Cross Join, Inner Join, Outer Join, Self Join
Cross Join
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. Result set is the number of rows in the first table multiplied by the number of rows in the second table.
Inner Join
A join that displays only the rows that have a match in both joined tables is known as inner Join.
Outer Join
A join that includes rows even if they do not have related rows in the joined table is an Outer Join.
- Left Outer Join: In Left Outer Join all rows in the first table. Unmatched rows in the right table do not appear.
- Right Outer Join: In Right Outer Join all rows in the second table. Unmatched rows in the left table are not included.
- Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
Self Join
When one table joins to itself
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. 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.
Clustered Index
Clustered index is unique for any given table and we have only one clustered index on a table.
Non Clustered Index
Many non Clustered Indexes as we can on database.
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.
Eg; sp_addlinkedserver, sp_addlinkedsrvlogin
Cursor
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 one time.
Collation
Collation refers to a set of rules that determine how data is sorted and compared.
Sub-query
Sub query is a SELECT statement that is nested within another T-SQL statement.
