functionalities
Transaction
is a larger unit of database processing that contains one or more database access operations like insertion ,deletion, retrieval and modification operations.
COMMIT
makes all changes under the transaction persistent. It commits the changes to the database.
ROLLBACK
It rolls back the changes to the database.
views
A view is nothing but a table, not a part of the physical schema.
it is a dynamic, virtual table computed or collated from data in the database.
a view consists of a stored query accessible as a virtual table.
using a views aren't always good for performance.
in general its better to use Stored procedures
Triggers
The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed.
SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed.
Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation.
CREATE TRIGGER TrigA BEFORE/AFTER DELETE/INSERT/UPDATE ON X_doc_table
INSERT INTO TLog VALUES ('doc deleted');
Triggers or stored procedures? It depends on the the situation but it is practical that if you have no way to get the work done with stored procedure, think about triggers.
Indexes
There are some general rules which describe when to use indexes.
When dealing with relatively small tables, indexes do not improve performance.
Use indexes when most of your database queries retrieve relatively small datasets, because if your queries retrieve most of the data most of the time, the indexes will actually slow the data retrieval.
Use indexes for columns that have many different values (there are not many repeated values within the column).
Although indexes improve search performance, they slow the updates, and this might be something worth considering.
syntax:
CREATE INDEX index_name ON the_table(the_column/s)
The most obvious use for an index is in finding a record or set of records matching a WHERE clause.
Indexes can aid queries looking for values inside of a range, as well as queries looking for a specific value.
Cursor
There are times when you want to loop through a series of rows and perform processing for each row. In this case you can use a cursor.
note that cursors are the SLOWEST way to access data inside SQL Server.
DECLARE @TempCatID int
DECLARE myCURSOR CURSOR READ_ONLY
FOR
SELECT CatID
FROM Categories <- populates the result set
OPEN myCURSOR
FETCH NEXT FROM myCURSOR INTO @TempCatID <- returns a row from the result set into the variable
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC MyStoredP(@TempCatID)
END
CLOSE myCURSOR
DEALLOCATE myCURSOR <- releases the resources associated with a cursor
The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.
