User Defined Functions allows
modular programming you can create the function once, store it in the database,
and call it any number of times in your program. User Defined Functions can be
modified independently of the program source code. Similar to Stored
Procedures, Transact-SQL User Defined Functions reduce the compilation cost of
Transact-SQL code by caching the plans and reusing them for repeated
executions. This means the user-defined function does not need to be reparsed
and re-optimized with each use resulting in much faster execution times. CLR
functions offer significant performance advantage over Transact-SQL functions
for computational tasks, string manipulation, and business logic. Transact-SQL
functions are better suited for data-access intensive logic. An operation
that filters data based on some complex constraint that cannot be expressed in
a single scalar expression can be expressed as a function. The function can
then invoked in the WHERE clause to reduce the number or rows sent to the
client.
Stored Procedures IN SQL
Stored Procedures are Precompiled
means SQL Server compiles each Stored Procedure once and then re
utilizes the execution plan. This results in great performance improvements
when Stored Procedures are called frequently. It Reduced load on the network
bandwidth. Stored Procedures can be used by multiple users and client programs.
If you utilize them in a planned manner then you'll find the development cycle
requires less time. You can grant users permission to execute a Stored
Procedure independently of underlying table permissions.
Triggers in SQL Server
Triggers are database object. Basically these are special type of
stored procedure that are automatically fired/executed when a DDL or DML
command statement related with the trigger is executed. Triggers are used to
assess/evaluate data before or after data modification using DDL and DML
statements. These are also used to preserve data integrity, to control server
operations, to audit a server and to implement business logic or business rule.
We have two types of triggers
· DDL Triggers:
Triggers Created on DDL Statements i.e. CREATE, ALTER, DROP and system defined stored procedures that
perform DDL-like operations .user can use only FOR and AFTER clause in
DDL triggers not INSTEAD OF clause that means user can make only After Trigger
on DDL statements. DDL trigger can be used to observe and control actions
performed on the server, and to audit these operations. DDL triggers can be
used to manage administrator tasks such as auditing and regulating database
operations
· DML Triggers: DML triggers are invoked when any
DML commands like INSERT, DELETE, and UPDATE happen on the data of a table and
or view. DML triggers are powerful objects for maintaining database
integrity and consistency. These type of triggers evaluate data before it has
been committed to the database.
During this evaluation following actions are
performed.
o Compare
before and after versions of data
o Roll back
invalid modification
o Read from
other tables, those in other database
o Modify other
tables, including those in other database.
o Execute local
and remote stored procedures.
We cannot use following commands in DML
trigger
o ALTER
DATABASE
o CREATE
DATABASE
o DISK DATABASE
o LOAD DATABASE
o RESTORE
DATABASE
We have two type of DML
triggers
o
After
Triggers: Triggers used for using FOR and AFTER CLAUSE An AFTER trigger
is the original mechanism that SQL Server created to provide an automated
response to data modifications. AFTER triggers fire after the data modification
statement completes but before the statement's work is committed to the
databases. The trigger has the capability to roll back its actions as well as
the actions of the modification statement that invoked it.
o
Instead
of Trigger: Triggers used in INSTEAD OF CLAUSE called instead of Triggers. Provides an alternative to the
AFTER trigger that was heavily utilized in prior versions of SQL Server. It
performs its actions instead of the action that fired it. This is much
different from the AFTER trigger, which performs its actions after the
statement that caused it to fire has completed. This means you can have an
INSTEAD OF update trigger on a table that successfully completes but does not
include the actual update to the table.
We can define an
INSTEAD OF trigger on a view (something that will not work with AFTER triggers)
and this is the basis of the Distributed Partitioned Views that are used so
split data across a cluster of SQL Servers. We can use INSTEAD OF triggers to
simplify the process of updating multiple tables for application developers.
Trigger Syntax
- trigger_name: This is the name of the trigger. It should conform to the rules for identifiers in Sql Server.
- Table or view: This is the table or the view on which the trigger is to be created.
- ENCRYPTION: This option is optional. If this option is specified, original text of the CREATE TRIGGER statement will be encrypted.
- EXECUTE AS: This option is optional. This option specifies, the security context under which the trigger is executed.
- FOR or AFTER: FOR or AFTER specifies that the trigger is After Trigger. AFTER is the default, if FOR is the only keyword specified. AFTER triggers cannot be defined on views.
- INSTEAD OF: INSTEAD OF specifies that the trigger is Instead Of Trigger.
- CREATE or ALTER or DROP or INSERT or UPDATE or DELETE: These keywords specify on which action the trigger should be fired. One of these keywords or any combination of these keywords in any order can be used.
- NOT FOR REPLICATION: Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.
- AS: After this we specifies the actions and condition that the trigger perform.
- sql_statement: These are the trigger conditions and actions. The trigger actions specified in the T-SQL statements.
What is a Cursor ?
A cursor is a pointer to this context area.
PL/SQL controls the context area through a cursor. A cursor holds the rows (one
or more) returned by a SQL statement. The set of rows the cursor holds is
referred to as the active set .
Or
We have
basically two type of cursor in SQL
·
Implicit cursors
These are created by default when DML statements like,
INSERT, UPDATE, and DELETE statements are executed. They are also created when
a SELECT statement that returns just one row is executed.
·
Explicit cursors
They must be
created when you are executing a SELECT statement that returns more than one
row. Even though the cursor stores multiple records, only one record can be
processed at a time, which is called as current row. When you fetch a row the
current row position moves to next row.
Both
implicit and explicit cursors have the same functionality, but they differ in
the way they are accessed.
Life Cycle/Syntax of Cursor
Life Cycle/Syntax of Cursor
1.
Declare Cursor
A cursor is declared by
defining the SQL statement that returns a result set.
DECLARE cursor_name CURSOR
[LOCAL |
GLOBAL] --define cursor scope
[FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
[STATIC
| KEYSET | DYNAMIC | FAST_FORWARD] --basic type of
cursor
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
FOR select_statement --define
SQL Select statement
FOR UPDATE
[col1,col2,...coln] --define columns that need to be
updated
2.
Open
A Cursor is opened and
populated by executing the SQL statement defined by the cursor.
OPEN [GLOBAL] cursor_name --by default it is local
3.
Fetch
When cursor is opened, rows
can be fetched from the cursor one by one or in a block to do data
manipulation.
FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name
INTO @Variable_name[a,b,c.....n]
4.
Close
After data manipulation, we
should close the cursor explicitly.
CLOSE cursor_name
We car reopen a cursor after closing it.
5.
Deallocate
Finally, we need to delete the
cursor definition and released all the system resources associated with the
cursor.
DEALLOCATE
cursor_name
Example:
SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
DECLARE cur_emp CURSOR
STATIC FOR
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS
> 0
BEGIN
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
WHILE @@Fetch_status
= 0
BEGIN
PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF
Statements
operations supported by Cursor are
- CLOSE
- CREATE PROCEDURE
- DEALLOCATE
- DECLARE CURSOR
- DECLARE @local_variable
- DELETE
- FETCH
- OPEN
- UPDATE
- SET
System
functions and system stored procedures also support cursors
- @@CURSOR_ROWS
- CURSOR_STATUS
- @@FETCH_STATUS
- sp_cursor_list
- sp_describe_cursor
- sp_describe_cursor_columns
- sp_describe_cursor_tables
Common Table Expression (CTE)
The common
table expression (CTE)
is a temporary named result set that you can reference within a SELECT, INSERT,
UPDATE, or DELETE statement. It can be assumed as a temporary result set that is defined
within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or
CREATE VIEW statement. This is similar to resulting a table in that is not
stored as an object and lasts only for the interval of the query. Again it
offers the advantages of higher readability and ease in maintenance of complex
queries. The query can be divided into separate, simple, logical building
blocks.
Use OF CTE
·
Create a recursive query.
·
It is a Substitute of a view when the general use of
a view is not required. Or we can say. You do not have to store the definition
in metadata.
·
Enable grouping by a column.
·
Reference the resulting table multiple times in the
same statement.
Example:
With EMPCTE(Emp_Id,Address, Name, Salery) --Col. names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Salery from
Emp_Master A
INNER JOIN
EMP_Details E ON E.Emp_Id
= A.Emp_Id
)
SELECT * FROM EMPCTE --SELECT or USE CTE temporary Table
WHERE EMPCTE.Salery > 5000
ORDER BY EMPCTE.NAME
·
expression_name
·
column_name
·
CTE_query_definition
- It must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
- Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
- A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
- Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
- We cannot use ORDER BY ,INTO,OPTION ,FOR BROWSE clauses in the CTE_query_definition
- When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
- Tables on remote servers can be referenced in the CTE.
- When executing a CTE, any hints that reference a CTE may
conflict with other hints that are discovered when the CTE accesses its
underlying tables, in the same manner as hints that reference views in
queries. When this occurs, the query returns an error.
Subscribe to:
Posts (Atom)