Triggers – Basic info

1.A trigger is a special kind of procedure that responds to specific events.

2.A Trigger is a piece of code that you attach to a particular
table.Unlike stored procedures where we have to explicitly invoke the
code , the code is trigger is automatically run whenever the events you
attached the trigger to occur in the table.

3.Indeed you can’t explicitly invoke triggers.The only way to do
this is by performing the required action in the table that they are
assigned.

4.Types of triggers. FOR | AFTER , INSTEAD OF.

5.We cannot have triggers on the tables having columns with text , ntext , image as datatype.

6.Whenever a trigger is fired internally SQL SERVER creates two
table;Inserted and Deleted.These tables are called as Magic
Tables.These magic tables are stored in temporary database.

7.The scope of the tables is only with in the trigger.

8.We cannot access these tables i.e magic tables in the trigger.Only triggers can access them.

9.Both the tables are of same structure.

10.We can use for triggers

a.Referential integrity
b.Functionality similar to a check constraint.
c.Complex view Insertion.

11.What we can attach to triggers to.
a.Insert triggers.
b.Update triggers.
c.Delete triggers.
d.Combination of above three.

I will add one more detailed article on the same topic.

Pawan Kumar
Pawankkmr@hotmail.com

USER DEFINED FUNCTIONS IN SQL SERVER 2005

1.Functions are SQL SERVER objects which are used to write logic for funtionalities other than business logic.

2.Functions by default does not return a value.

3.Functions can’t be used for insert , update or delete.

4.Functions do not have concept of INPUT and OUTPUT Parameters.

5.There are three types of functions available in the user defined functions.

a.Scalar valued functions : They will return single value.

b.Inline table valued functions : They always returns a resultset
but we cannot define a new structure of resultset.It cannot return a
single value.

c.Multivalued table functions: They returns a resultant table.Here we can define a new structure for the table.

6.Each function is used for specific purpose.

7.We can do all the things with stored procedures as well as functions but what we write is purely depends on your requirements.

Examples of scalar valued functions.

Create function
(

@param1 ,@param2 ….param list

)

returns

AS

BEGIN

SQL Statements.
returns variable name –>Datatype of this variable should be same as what we are returning.

END

Eg.
Create function func_avg
( @cat_id int )
returns money
AS
BEGIN

Declare @avg as money
SELECT @avg = agv(UnitPrice) from products where categoryId = @catId
return @avg

END

How to execute : select * from dbo.func_avg(1)

Inline table Value functions

Create function

(

@param1 , @param2 …..param list

)
RETURNS TABLE
AS
RETURN
SQL STATEMENT

Create function func_dtls
(
@catId INT
)
Returns table
as
Return

SELECT prodName , CategoryId , UnitPrice from Products
Where categoryId = @catId

Get result….

Select * from func_dtls(23)

Table valued functions

Create function
(

@param1 , @param2 …..param list

)
Returns @tableName TABLE ()
AS
BEGIN
SQL STATEMENT
RETURN
END

eg.
Create function func_temp
(

)
returns @newTable TABLE
(

cust_id int indentity ( 1,1 ),
cname varchar(60),
countryName varchar(30)

)

AS
BEGIN

INSERT @newTable
SELECT CompanyName , Country from customers
Return

END

Pawan Kumar
Pawankkmr@hotmail.com

FORMATS OF EXECUTION PLAN & USAGE PERMISSION

2.SQL SERVER offers three formats for execution plans. They are

a.Graphical Plans

b.Text Plans

c. XML Plans

2.Graphical Plans are easy to read but the detailed data for the
plan is masked. Both Estimated and Actual execution plans can be viewed
in graphical format.

3.Text plans provides three text plan formats:

a.SHOWPLAN_ALL : a reasonably complete set of data showing the
Estimated execution plan for the query.It will not give u actual
execution plan.

b.SHOWPLAN_TEXT : provides a very limited set of data for use with
tools like osql.exe . It will only give you estimated execution plan.

c.STATISTICS PROFILE: similar to SHOWPLAN_ALL except it represents the data for the Actual execution plan

4.XML plans present the most complete set of data available on a
plan, all on display in the structured XML format. There are two
varieties of XML plan:

a.SHOWPLAN_XML : The plan generated by the optimizer prior to
execution.SQL Server returns detailed information about how the
statements are going to be executed in the form of a well-defined XML
document.

b.STATISTICS_XML : The XML format of the Actual execution plan.

Permissions Required to View Execution Plans : GRANT SHOWPLAN TO [user]

Examples are given below.

SET SHOWPLAN_ALL ;

SET STATISTICS PROFILE ON

SET SHOWPLAN_XML ON

Pawan Kumar
Pawankkmr@hotmail.com

EXECUTION PLANS AND THE MEMORY

Why stored procedures takes the same amount of time after some hours as it takes at
the first go.

It should take less time than the time taken by SQL SERVER to
execute it for the first time. The answer is this is depends on the
age. This has been taken care of LAZY WRITER.

1.It is too costly for the SQL SERVER to generate execution plan
every time it executes a query. Hence SQL SERVER will keeps the same in
the memory and reuse the same when ever possible.They are stored in
part of the SQL SERVER memory called plan cache.

2.When a query is given to the SQL SERVER , an estimated execution
plan is created by the query optimizer.Then the optimizer compares both
the plan estimated as well as the plan from the plan cache.If both are
same then the optimizer will reuse the existing plan because it is
already been used by the query engine.This will reduce overheads of
creating actual execution plans.

3.Execution plans are not saved in memory forever.

4.SQL SERVER removes the execution plan if one of the following criteria is met.
a) If the SQL SERVER wants to use the memory which is used by our execution plan.
b) The age of our plan is 0.Age formula that multiplies the estimated
cost of the plan by the number of times it has been used .The
lazywriter process, an internal process that works to free all types of
cache (including plan cache), periodically scans the objects in the
cache and decreases this value by one each time.
c) If the plan is not used by the existing connections.

5.To clear the procedure cache the query used is : DBCC FREEPROCCACHE

6.Following actions can lead to recompilation of an execution plan:
a)If you change the index which is used by the query.
b)Chaging the structure of the table.
c)Dropping the index used by the query.
e)Calling the function sp_recompile explicitly or it has been written inside the stored procedure.
f)Due to large number of deletes or inserts.

7.One can easily get the information about execution plans on our
system.The tables used for the same are sys.dm_exec_cached_plans cp ,
sys.dm_exec_sql_text , sys.dm_exec_query_plan

Pawan Kumar
Pawankkmr@hotmail.com