User defined functions in T-SQL

User defined functions are set of codes which returns some value may be in terms of scalar value or table value after performing operations. These codes are reusable and made to perform any operation. It can have multiple input parameters but only one return.

Types of UDF in T-SQL

1. Scalar function:
1. deterministic
2. non-deterministic
2. Inline function:
3. Table valued function:

Scalar Functions: In this type of function it returns only one value of any data type except table. For integer data type we have two types:

1. deterministic: It means output is totally dependent on input
2. non-deterministic: That means output is no dependent on input

           CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS <data-type>
               AS
               BEGIN
                      <statements>
                      Return <variable>
               END

Inline Function: This type of function only returns table type with only single statement. This function does not have BEGIN...END.

       CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS TABLE
               AS
               RETURN (
                      <statements>
               )

Table Valued Function:  This type of function also returns table type but by creating table variable in definition with data type for each column.
              
       CREATE FUNCTION <function-name>(<input-parameters>)
               RETURNS <table-variable> TABLE (<columns with data-type>)
               AS
               BEGIN
                      <statements>
                      RETURN
              END

Pros: Precompiled which makes execution faster and reusable. A function can call another function and stored procedure also can call function. Can return table type.

Cons: We can not execute stored procedure inside function. Can not have output parameter, So can return only one value of any data type.

Stored Procedure in T-SQL

Stored Procedures are block of codes in T-SQL. It is a permanent database object in sql server. These codes are parsed and precompiled which saves execution time.In stored procedure we can multiple input parameters, multiple output parameters but can have only one return integer value.

In types of stored procedures we have main 4 types:

1. System Stored Procedures
2. User defined Stored Procedures
3. Extended Stored Procedures
4. CLR (Common Language Runtime)

In user defined stored procedures, we can categories according to parameters:

1. Stored Procedure with INPUT parameter
2. Stored Procedure without INPUT parameter
3. Stored Procedure with OUTPUT parameter
4. Stored Procedure without OUTPUT parameter
5. Stored Procedure with return 

We can not call store procedures but we have to execute stored procedures using EXEC or EXECUTE statement followed by stored procedure name with schema and parameters.

Syntax:
     CREATE PROC <procedure-name> <parameters>
      AS
      BEGIN
             <statements>
      END

    EXEC <procedure-name><parameters>

Stored procedure either can have BEGIN....END or GO at the end. We can execute stored procedure inside a stored procedure but we can not execute inside the function. We can also make function call inside stored procedure. Stored procedures are reusable and faster in execution compare to individual statements.

While creating stored procedures if we specify default values than missing parameter values will be passed from default value otherwise it will through an error in execution.

As per case of executing dynamic sql inside stored procedure requires executing stored procedure with writing permission.

Triggers in T-SQL

Triggers are set of codes which executes automatically when certain event occurs. Unlike stored procedure triggers do not have parameters to pass. We can have BEGIN and END to specify set of codes which are included in trigger definition. These events are may be DDL statements or DML statements or Login events. We have three types of triggers based on events:

1. DDL triggers
2. DML triggers
3. Logon triggers

DDL triggers: In DDL triggers we have After/For triggers. After/For triggers itself specifies after occurring of DDL statement this trigger will be fired automatically.
    CREATE TRIGGER <trigger-name> ON <Object-name>
      FOR/AFTER CREATE/ALTER/DROP
      AS
      BEGIN
            <statements>
      END

DML triggers: In DML triggers we have AFTER/FOR triggers and INSTEAD OF triggers. AFTER/FOR trigger behave same way as it does in DDL triggers. While in INSTEAD OF trigger it fired instead of particular DML statement. In DML triggers to catch data for particular DML operations we have two table access from SQL Server. These tables are called MAGIC TABLES.

MAGIC TABLES are INSERTED and DELETED. In INSERTED data will pop-up when you run INSERT and UPDATE statement. While in DELETED data will pop-up when you run UPDATE and DELETE statement. 

   CREATE TRIGGER <trigger-name> ON <Object-name>
      FOR/AFTER/INSTEAD OF INSERT/UPDATE/DELETE
      AS
      BEGIN
            <statements>
      END

LOGON triggers: LOGON trigger fires when user login into the Database system.
 
   CREATE TRIGGER <trigger-name> ON ALL SERVER
      FOR/AFTER LOGON
      AS
      BEGIN
            <statements>
      END



Triggers are database objects too that is the reason we can CREATE, ALTER and DROP. We can also Enable or Disable trigger for certain purposes.