1. SQL Objects

a. Stored Procedures : a bunch of SQL statement which can be stored under one name.

Adventages :

1. can be used for modularized programing
2. Can be used for security purpose
3. Can be in a compiled state so execution will be faster
4. Reduces network traffic

Types of Stored Procedures : There are mainly tree types of strored procedures in the SQL server.

• System defined in built stored procedures
• User defined stored procedures
• Extended stored procedures

 => System stored procedures

• They are starting with SP_ 
• e.g. SP_Helptext, sp_executesql, etc.
• They are stored in master db
• We can create UDSP with the same name but it will never be executed even if we execute like DBName.SchemaName.ProcName. SQL server internally first checks it in master db and if it finds an SP with the same name, it will execute that SP 

=> User Defined stored procedure

• Created by User
• Nasted SPs will be upto 32 levels
• Execute input and output parameter, also excepts UDT
• You can not  have Go,  USE DB, Create Proc / View / Function inside the body of stored procedure

=> Extended stored procedure

• starting with SP_
• e.g xp_sendMail etc.

It is for all System level functions

• local temporary stored procedure

               starting with #, local scope

• Global Temparory stored procedure

              starting with ##, for global SPs the scope will be available untill the server UP

/* Create procedure with OUTPUT Parameter */

CREATE PROC Proc1 (@a int, @b int, @c int OUT)
AS
BEGIN
  BEGIN TRY
     SET @c = @a + @b
  END TRY
  BEGIN CATCH
     EXEC GenericErrorProc
  END CATCH
END
-- Usage of the procDECLARE @c out
EXEC Proc1 1,2,@c out
SELECT @c

CREATE PROC Proc1

AS
BEGIN
      BEGIN TRY
            SELECT A,B FROM TableName
      END TRY
      BEGIN CATCH
          EXEC GenericErrorProc
     END CATCH
END
-- Exec usage of SP
CREATE TABLE #TEMP (a int, b int)
INSERT INTO #TEMP(A) EXEC Proc1

• If you execute an SP at the same time of SQL server starts... you have to use following option

     SP_PROCOPTION ('SPName','startup','true')

If you create an SP with ‘WITH RECOMPILE’ option, every time it will compile and execute. Preferred only when you have a variety of different input parameters, otherwise hits perf

WITH ENCRYPTION will encrypt the code. Not even sys admin will be able to see. However they can alter the SP

b. Functions : A bunch of SQL statements which can be stored under one name, there are different types of functions

• Scalar function, returns the single value (datatype)
• Table value function

CREATE FUNCTION Fun1 (@a int)

RETURNS int
AS
BEGIN
   DECLARE @B int
   SET @B = @A
   RETURN(@b)
END

•  returns a table (only 1)

CREATE FUNCTION Fun2 (@a int)

RETURNS @temp table (B int)
AS
BEGIN
   INSERT INTO @temp (B) VALUES (@A)
   RETURN
END

•  Aggregation function
• System function
• Mathematical function, date function etc.

Difference between stored procedure and function

• A scalar valued function can be used in SELECT and WHERE clause and a table-valued function can be used in FROM clause. SP cannot be used anywhere
• Function won't execpt OUTPUT param, SP does
• you can not call SP, cannot create #Temp table, cannot use DML & DDL statement inside functions, SP will allow.
• Function can be used as User defined Data types, SP canonot
• you cannot return text,image, timestamp from function

c. Trigger : A special type of SP which are defined to execute automatically when UPDATE / DELETE / INSERT statement issuged against a table / view. There are mainly three types of the Trigger in SQL server.

• Before trigger : getting executed before the actual operation

CREATE Trigger TriggerName ON TableName BEFORE INSERT

AS

BEGIN

    SELECT 100

END

After Trigger : getting executed after an actual DML Operation happen 

CREATE Trigger TriggerName ON TableName AFTER INSERT

AS

BEGIN

   SELECT 200

END

Instead Of Trigger : Getting executed instead of actual DML Operation, only one instead of trigger is possible for each DML operation

CREATE Trigger TriggerName ON TableName INSTEAD OF INSERT

AS

BEGIN

   INSERT INTO Table2 (a) SELEct a from inserted

END

 => Someting extra

• Debuging is very difficult
• Generally it should be avoided
• Auditing can be handeled by CDC
• If multiple before and after triggers are available for a single DML operation, then order will be same as it got created. However, sp_settrigggerorder can be used to set the first and  last order of order execution

 d. View : A virtual entity of DB which looks like table but does not store records physically.

• Its something like window over a table
• complex query can be covered into view and can be used in FORM clause
• A table cannot be dropped even though view is existing which is using this table. However view created with SCHEMABINDING option won’t allow dropping a table
• All DML operations are possible on View but again it has some limitations. It should violate anything.
• Won’t allow SELECT INTO, GROUP BY, ORDER BY, HAVING
• Index can be created on view. Only non-clustered. If we do this, view will start storing the data physically

CREATE VIEW vwName

AS
SELECT QUERY

e. Cursor : Allows row by row processing, control  will be user rather than SQL server

• Different options can be applied while creating a cursor.
• local / global
• static / dynamic / fast - forward / keytype driven
• Forward_only / scroll
• Generally it should avoided as it is affecting a performance a big time
• if another SP is being called within cursor, it will fail. so while loop should be used instead of cursor

DECLARE cur CURSOR

FOR SELECT a,b FROM TableName

OPEN cur

FETCH NEXT INTO @a, @b

WHILE @@FETCH_STATUS = 0

BEGIN

      -- Your logic    

      FETCH NEXT INTO @a, @b

END

CLOSE cur

DEALLOCATE cur

• in some of the cases, cursor will be faster then normal join