The user defined functions returns result after using an input parameters with a logic that calculates something .

There are two types of UDF are : scalar and table valued functions.

Scalar UDF returns a single value while the table valued functions returns a table.

 

Trigger is a code just like a stored procedure that runs whenever an event takes place. The event is related to DML (Data manipulation Language ) events and DDL(Data definition Language ) events .

1 – DDL(Data definition Language ) Triggers :

DDL statements means statements like :Create , alter etc . These triggers can be used for Auditing purposes or change management . They can be used for tracking whenever a CREATE table  or ALTER table statement is being run and can help the database administrators to use the logged information through these triggers about the events of Creation , alter or other DDL events.

2 –  DML(Data Manipulation Language ) Triggers :

SQL supports two types of DML triggers : After and instead of.

2.1 – AFTER Trigger :  

After Trigger fires after the event it is associated with it completes.

This trigger works only on permanent tables.

In the trigger’s code you can access the table that are called inserted and deleted that contains which were affected by the modification that caused the trigger to fire. The inserted table contains the image of the new data inserted . Whereas the deleted table holds the old image of the affected rows.

Example : 

IF OBJECT_ID('dbo.Table1_Audit', 'U') IS NOT NULL DROP TABLE dbo.Table1_Audit;
IF OBJECT_ID('dbo.Table1, 'U') IS NOT NULL DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Table1_Audit
(
audit_lsn INT NOT NULL IDENTITY PRIMARY KEY,
dt DATETIME NOT NULL DEFAULT(SYSDATETIME()),
login_name sysname NOT NULL DEFAULT(ORIGINAL_LOGIN()),
keycol INT NOT NULL,
datacol VARCHAR(10) NOT NULL
);

--Now creating trigger

CREATE TRIGGER trg_Table1_insert_audit ON dbo.Table1 AFTER INSERT
AS
SET NOCOUNT ON;
INSERT INTO dbo.Table1_Audit(keycol, datacol)
SELECT keycol, datacol FROM inserted;
GO

--Now insert values into the table1

INSERT INTO dbo.Table1(keycol, datacol) VALUES(1, 'a');

Once the insert statement is run the trigger trg_Table1_insert_audit runs . We can now verify the audit table1 :

SELECT audit_lsn, dt, login_name, keycol, datacol FROM dbo.Table1_Audit;

Now check the table1 :

select * from Table1

2.2 – INSTEAD OF Trigger :  

In the Instead of triggers, the inserted and deleted tables contain the rows that were supposed or about to be affected (Inserted or Deleted) by the modification that caused the trigger to fire.

Example : 

IF OBJECT_ID('dbo.Table1_Audit', 'U') IS NOT NULL DROP TABLE dbo.Table1_Audit;
IF OBJECT_ID('dbo.Table1, 'U') IS NOT NULL DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Table1_Audit
(
audit_lsn INT NOT NULL IDENTITY PRIMARY KEY,
dt DATETIME NOT NULL DEFAULT(SYSDATETIME()),
login_name sysname NOT NULL DEFAULT(ORIGINAL_LOGIN()),
keycol INT NOT NULL,
datacol VARCHAR(10) NOT NULL
);

--Now creating trigger INSTEAD OF

CREATE TRIGGER trg_Table1_insert_audit ON dbo.Table1 instead of insert
AS
SET NOCOUNT ON;
INSERT INTO dbo.Table1_Audit(keycol, datacol)
SELECT keycol, datacol FROM inserted;
GO

–Now insert values into the table1

INSERT INTO dbo.Table1(keycol, datacol) VALUES(1, 'a');

Once the insert statement is run the trigger trg_Table1_insert_audit runs . We can now verify the audit table1 :

SELECT audit_lsn, dt, login_name, keycol, datacol FROM dbo.Table1_Audit;

To make clear the difference , check the Table1 for understanding !

select * from Table1

We declare table variables as we declare other variables in SQL .

Table variables are just like the local temp tables and they reside in tempdb which is the temporary database.

Table variable are only available to the creating session .

One drawback with table variable is that they are only available and visible to the current batch and they are neither visible to other batches not even to the inner batches.

So , use the table variables when there is less amount of rows to work with .

 

The cursors are in contrast with the relational model which is based on the set Theory .

The cursors are slow as compared to the relational based set implementation  as they manipulate and work on the data row by row iteratively which is typically slow.

One of the main reasons of cursors are slow is because of overhead being caused due to : declaration of the cursor , then opening the cursor , looping through the cursor , closing the cursor and then de allocating the cursor .

Use cursors when you have a situation where a not a better solution exists from logical set based implementations  .

A good example where you can cursors is to run it to know the total number  of rows in a table.