SQL Blog (Basic)

,

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 Procedure which will return a table
  • 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

  • => Something extra
    • 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 

     


     

     

    Rate

    Share

    Share

    Rate