1. SQL Objects
a. Stored Procedures : a bunch of SQL statement which can be stored under one name.
Adventages :
- can be used for modularized programing
- Can be used for security purpose
- Can be in a compiled state so execution will be faster
- 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 Proc1AS
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 vwNameAS
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