SQL SERVER 2005

1
(No Transcript)
2
Topics

• Views
• Stored Procedures
• User Defined Functions
• Triggers

3
Views

• A view is a virtual table that consists of
  columns from one or more tables
• Implements a security mechanism
• Complex queries can be stored in the
• form as a view, and data from the
• view can be extracted
• using simple queries

4
T-SQL View
5

• SQL Server stores information on the view in the
  following system tables
• SYSOBJECTS stores the name of the view.
• SYSCOLUMNS stores the names of the columns
  defined in the view.
• SYSDEPENDS stores information on the view
  dependencies.
• SYSCOMMENTS stores the text of the view
  definition.
• Views can have up to 1024 columns.
• WITH ENCRYPTION encrypts the text for the view
  in the SYSCOMMENTS tables

6
The restrictions imposed on views are as follows

• A view can be created only in the current
  database.
• The name of a view must not be the same as that
  of the base table they must follow the rules for
  identifiers.
• A view can be created only if there is a SELECT
  permission on its base table.
• A SELECT INTO statement cannot be used in view
  declaration statement.
• The CREATE VIEW statement cannot be combined with
  other SQL statements in a single batch.

7

• SCHEMABINDING Binds views to underlying tables.
• The view may have to be modified or dropped
  to remove dependency on table
• If a view is not created with schemabinding
  clause sp_refreshview should be run when
  underlying table changes.

8

• WITH CHECK OPTION is an optional clause on the
  CREATE VIEW statement that specifies the level of
  checking to be done when inserting or updating
  data through a view. If the option is specified,
  every row that is inserted or updated through the
  view must conform to the definition of that view

9
Alter Views
10
Drop Views

• When a view is dropped, it has no effect on the
  underlying tables.
• Dropping a view removes its definition and all
  the permissions assigned to it.
• However, dropping a table that references a view
  does not drop the view automatically. You must
  drop it explicitly.

11
Rename View

• You can rename a view without having to drop it.
  This ensures that the permissions on the view are
  not lost

12
Modifying Data using Views

• A view may be derived from multiple underlying
  tables
• A single data modification statement that
  affected both the underlying tables is not
  permitted.
• You cannot modify the following of Columns using
  a view
• Columns that are based on computed values. E.g.
  sum, avg
• Columns that are based on row aggregate
  functions. E.g. group by, having
• Columns based on built-in functions like numeric,
  string functions.

13
Optimizing performance using Views

• Indexed Views
• You can significantly improve performance by
  creating a unique clustered index on a view that
  involves complex processing of large quantities
  of data, such as aggregating or joining many rows
  
• Aggregations can be precompiled and stored in the
  index to minimize expensive computations during
  query execution
• Unique clustered index is created on the view,
  the view's result set is materialized immediately
  and persisted in physical storage in the
  database, saving the overhead of performing this
  costly operation at execution time.
• When to Use Indexed Views
• Because indexed views are more complex to
  maintain than indexes on base tables, you should
  use them only when the improved speed in
  retrieving the results outweighs the increased
  overhead of data modifications.
• Indexing views is not a good idea in a
  high-volume OLTP system.
• Indexed views work best when the data is
  relatively static, and you need to process many
  rows or the view will be referenced by many
  queries.

14
Indexed Views in SQL Server 2005

• SQL Server 2005 contains many improvements for
  indexed views compared with SQL Server 2000.
• Scalar aggregates, including SUM and COUNT_BIG
  without GROUP BY.
• Scalar expressions and user-defined functions
  (UDFs)
• Common Language Runtime (CLR) types.
• User-defined types (UDTs)
• UDFs based on the CLR
• Database Tuning Advisor - recommends indexed
  views in addition to recommending indexes on base
  tables, and table and index partitioning
  strategies

15
Requirements for Indexed Views

• Set the ANSI_NULLS option to ON when you create
  the tables referenced by the view
• Set the ANSI_NULLS and QUOTED_IDENTIFIER options
  to ON prior to creating the view
• The view must only reference base tables, not any
  other views
• Base tables referenced by the view must be in the
  same database as the view and must have the same
  owner
• Create the view and any user-defined functions
  referenced in the view with the SCHEMABINDING
  option. This means that the underlying tables or
  other database objects cannot be altered or
  dropped as long as the view or function exists.
• Reference all table names and user-defined
  functions with two-part names onlyfor example,
  "dbo.Customers" for the Customers table.
• Any functions used by the view must be
  deterministic, meaning that the function must
  always return the same result anytime it's called
  with the same set of input values.
• A unique clustered index must be created before
  any other indexes can be created on the view.
• Additional disk space will be required to hold
  the data defined by the indexed view.

16
The following Transact-SQL syntax elements are
illegal in an indexed view

• The syntax to specify all columns. Column names
  must be explicitly stated.
• Repeated columnsfor example, SELECT Col1, Col2,
  Col1 AS Col. However, you can re-use a column if
  it's part of a different expressionfor example,
  SELECT Col1, AVG(Col1), Col1 Col2 AS Total
• Derived tables and sub queries
• ROWSET.
• UNION.
• OUTER JOINS OR SELF JOINS.
• TOP AND ORDER BY.
• DISTINCT.
• COUNT(). USE COUNT_BIG() INSTEAD, which returns
  a big int data type is allowed.
• The following aggregate functions AVG, MAX, MIN,
  STDEV, STDEVP, VAR.
• The definition of indexed view must be
  deterministic
• CREATE TABLE T(a int, b real, c as getdate(), d
  as ab)
• CREATE VIEW VT WITH SCHEMABINDING AS SELECT a, b,
  c, d FROM dbo.T
• SELECT object_id('VT'), COLUMNPROPERTY(object_id('
  VT'),'d','IsPrecise')

17
(No Transcript)
18
Examples
19
TSQL Stored Procedures

• Precompiled execution. SQL Server compiles each
  stored procedure once and then reutilizes the
  execution plan. This results in tremendous
  performance boosts when stored procedures are
  called repeatedly.
• Reduced client/server traffic. Stored procedures
  can reduce long SQL queries to a single line that
  is transmitted over the wire hence reduce client
  server traffic.
• Efficient reuse of code and programming
  abstraction.
• Enhanced security controls. You can grant users
  permission to execute a stored procedure.

20
Create / Alter Syntax
21
Rename Stored Procedure

• Drop Procedure

22

• Execute Stored Procedure
• EXECUTE procedure_name
• Parameterized Procedures

23
Error Handling in Stored Procedure

• _at__at_ERROR - This function is used to implement
  error handling code. It contains the error ID
  produced by the last SQL statement executed
  during a clients connection. When a statement
  executes successfully, _at__at_ERROR contains 0. To
  determine if a statement executes successfully,
  an IF statement is used to check the value of the
  function immediately after the target statement
  executes. It is imperative that _at__at_ERROR be
  checked immediately after the target statement,
  because its value is reset when the next
  statement executes successfully

24

• RAISERROR- The RAISERROR statement is used to
  produce an ad hoc error message or to retrieve a
  custom message that is stored in the sysmessages
  table.

25
Try..Catch Block

• Implements error handling for Transact-SQL that
  is similar to the exception handling in the
  programming languages. A group of Transact-SQL
  statements can be enclosed in a TRY block. If an
  error occurs in the TRY block, control is passed
  to another group of statements that is enclosed
  in a CATCH block.
• TRYCATCH constructs can be nested. Either a TRY
  block or a CATCH block can contain nested
  TRYCATCH constructs.
• A TRY block must be immediately followed by an
  associated CATCH block. Including any other
  statements between the END TRY and BEGIN CATCH
  statements generates a syntax error.
• A TRYCATCH construct cannot span multiple blocks
  of Transact-SQL statements. For example, a
  TRYCATCH construct cannot span two BEGINEND
  blocks of Transact-SQL statements

26

• In the scope of a CATCH block, the following
  system functions can be used to obtain
  information about the error that caused the CATCH
  block to be executed
• ERROR_NUMBER() returns the number of the error.
• ERROR_SEVERITY() returns the severity.
• ERROR_STATE() returns the error state number.
• ERROR_PROCEDURE() returns the name of the stored
  procedure or trigger where the error occurred.
• ERROR_LINE() returns the line number inside the
  routine that caused the error.
• ERROR_MESSAGE() returns the complete text of the
  error message. The text includes the values
  supplied for any substitutable parameters, such
  as lengths, object names, or times.

27
(No Transcript)
28
User defined functions (UDF)

• Acts like a function in programming language. Can
  be parameterized and called any number of times.
• Faster execution, Reduces network traffic.
• The ability for a function to act like a table
  (for Inline table and Multi-statement table
  functions) gives developers the ability to break
  out complex logic into shorter and shorter code
  blocks.
• Three types of UDFs
• Scalar UDFs
• Inline Table valued UDFs
• Multi-statement table valued UDFs

29
Scalar UDFs

• UDF returns a scalar data type. Text, ntext,
  image, timestamp are not supported.

30
(No Transcript)
31
Inline Table Valued UDFs

• An Inline Table-Value user-defined function
  returns a table data type. Its an alternative to
  a view as the user-defined function can pass
  parameters into a T-SQL select command and in
  essence provide us with a parameterized,
  non-updateable view of the underlying tables

32
Multi-Statement Table valued UDFs

• A Multi-Statement Table-Value user-defined
  function returns a table and is also an
  exceptional alternative to a view.
• The ability to pass parameters into a T-SQL
  select command or a group of them gives us the
  capability to create a parameterized,
  non-updateable view of the data in the underlying
  tables.
• Within the create function command you must
  define the table structure that is being
  returned.
• After creating this type of user-defined
  function, you can use it in the FROM clause of a
  T-SQL command unlike the behaviour found when
  using a stored procedure which can also return
  record sets.

33
(No Transcript)
34
Limitations of UDFs

• UDF Prohibit Usage of Non-Deterministic Built-in
  Functions. However it is allowed in SQL Server
  2008.
• UDF cannot Call Stored Procedure
• UDF have only access to Extended Stored
  Procedure.
• UDFs cannot make use of dynamic SQL or temporary
  tables within the code. Table variables are
  allowed though.
• UDF can not Return XML.
• UDF does not support SET options.
• UDF does not Support Error Handling
• TRY/CATCH,RAISEERROR or _at__at_ERROR are not allowed
  in UDFs.
• UDF is allowed to modify the physical state of a
  database using INSERT, UPDATE or DELETE
  statements.
• UDF can be called through a SQL statement without
  using the EXECUTE statement.
• A UDF (any of the three variations - scalar,
  inline or multi-statement) cannot be used to
  return multiple result sets.

35
Triggers in SQL 2005

• A trigger is a database object that is attached
  to a table.
• The main difference between a trigger and a
  stored procedure is that the former is attached
  to a table and is only fired when an INSERT,
  UPDATE or DELETE occurs
• Guards against malicious inserts and updates.
• Three types of Triggers in SQL 2005
• Instead of Triggers
• After Triggers
• Data Definition Language Triggers
• DML triggers use the deleted and inserted logical
  (conceptual) tables.
• Triggers can allow cross table references,
  however check constraints allow column level
  constraints.

36

• SQL Server 2000 provides four different ways to
  determine the affects of the DML statements.
• The INSERTED and
• DELETED tables, popularly known as MAGIC TABLES
• update ()
• columns_updated()
• Magic Table does not contain the information
  about the columns of the data-type text, ntext,
  or image. Attempting to access these columns will
  cause an error.

37

• update() function is used to find whether a
  particular column has been updated or not. This
  function is generally used for data checks.
  Returns a Boolean value.

38

• Columns_Update() function returns a varbinary
  data type representation of the columns updated.
  This function return a hexadecimal values from
  which we can determine which columns in the table
  have been updated.
• COLUMNS_UPDATED tests for UPDATE or INSERT
  actions performed on multiple columns To test for
  UPDATE or INSERT attempts on one column, use
  UPDATE().

39
AFTER Triggers

• Triggers that run after an update, insert, or
  delete can be used in several ways
• Triggers can update, insert, or delete data in
  the same or other tables. This is useful to
  maintain relationships between data or to keep
  audit trail information.
• Triggers can check data against values of data in
  the rest of the table or in other tables.
• Triggers can use user-defined functions to
  activate non-database operations. This is useful,
  for example, for issuing alerts or updating
  information outside the database.
• Can be specified only on tables not on views.
• AFTER trigger is a trigger that gets executed
  automatically before the transaction is committed
  or rolled back.
• settriggerorder priority can set for AFTER
  triggers .

40

• 
  
  
  

A table can have several AFTER triggers for each
of the three triggering actions i.e., INSERT,
DELETE and UPDATE If a table has multiple AFTER
triggers, then you can specify which trigger
should be executed first and which trigger should
be executed last using the stored procedure
sp_settriggerorder
41

• Like stored procedures and views, triggers can
  also be encrypted. The trigger definition is then
  stored in an unreadable form. Once encrypted, the
  definition of the trigger cannot be decrypted and
  cannot be viewed by anyone, including the owner
  of the trigger or the system administrator.

42
(No Transcript)
43
INSTEAD OF Triggers

• INSTEAD OF triggers facilitates updating Views.
• A view or table can have only one INSTEAD OF
  trigger for each INSERT, UPDATE and DELETE events
  

44
DDL Triggers in 2005

• DDL triggers are fired on DDL events like Create,
  Alter, Drop.
• schema_name cannot be specified for DDL or logon
  triggers.
• ALL
• Indicates that all triggers defined at the scope
  of the ON clause are disabled
• DATABASE
• For a DDL trigger, indicates that trigger_name
  was created or modified to execute with database
  scope
• ALL SERVER
• For a DDL trigger, indicates that trigger_name
  was created or modified to execute with server
  scope.

45
(No Transcript)
46
Why Triggers?

• If the database is de-normalized and requires an
  automated way to update redundant data contained
  in multiple tables
• If customized messages and complex error handling
  are required
• If a value in one table must be validated against
  a non-identical value in another table.
• Triggers are a powerful tool that can be used to
  enforce the business rules automatically when the
  data is modified. Triggers can also be used to
  maintain the data integrity. But they are not to
  maintain data integrity. Triggers should be used
  to maintain the data integrity only if you are
  unable to enforce the data integrity using
  CONSTRAINTS, RULES and DEFAULTS.
• Triggers cannot be created on the temporary
  tables.

47
More on Triggers

• DISABLE/ ENABLE TRIGGER Trigger_Name
• ON ALL SERVER
• DISABLE TRIGGER Person.uAddress ON
  Person.Address
• DISABLE TRIGGER safety ON DATABASE
• DROP TRIGGER Trigger_Name ON ALL SERVER
• DISABLE Trigger ALL ON ALL SERVER
• Like stored procedures triggers can also be
  encrypted.
• Triggers can be nested up to 32 levels.

48
THANK YOU