Date: May 9, 2004
SQL Server 2000 Design and Implementation, Part VI -
Creating and Maintaining Database Objects...
This series of articles shall focus on the data store you use for your .NET applications. In particular it shall consider relational database management systems in general for the first article and SQL Server 2000 in particular for the remaining.
Date: May 9, 2004
This series of articles is focussing primarily on the physical data store Microsoft developers are most likely to use for their applications, SQLServer. The main reference for this series of articles is MCSE: SQLServer 2000 Design by Israel and Jones.
Here's a quick overview of what we’ve covered so far as well as what we shall be covering:
Article I: we started with developing a logical data model, before looking at how we go about implementing the physical database in SQLServer 2000.
Article II: creating and maintaining databases, in particular the files that comprise the database and how they can be split into filegroups and why they should be on occasion.
Article III: creating and maintaining tables - looking at what we can store in tables, and how we do so.
Article IV: data integrity - default values, check constraints and rules, primary keys, unique constraints, as well as foreign keys and relationships.
Article V: indexes and the related area of statistics.
Article VI: creating and maintaining the various database objects that exist- views, stored procedures, transactions, user defined functions and triggers. This concludes the look at the physical design per se. The remainder of the articles look at issues relating to data access.
Article VII: accessing data
Article VIII: modifying data
Article IX: importing and exporting data
Article X: locking
Article XI: security
Article XII: analysing and optimising data access
Thus far we've reached article VI: creating and maintaining database objects. The objects we shall be considering are: views, stored procedures, user defined functions and triggers.
A view is essentially a named SELECT statement. It acts as a table but does not contain any data, relying instead on the underlying table. As per a table a view can be queried, and data can be inserted, deleted and modified.
Why use views?
The CREATE VIEW statement is used to create views. You can create a view with the following options:
ENCRYPTION: encrypt the view!
SCHEMABINDING: protects the view definition against any structure modifications to the underlying table. If this option is used then the underlying tables cannot be dropped or altered if it affects the view definition. If you use this option you must explicitly define all the columns in the SELECT statement.
VIEW_METADATA: useful when you use the view through DBLIB, OLEDB or ODBC. To use a view a client needs to first retrieve metadata that describes it in order to use it. With this option enabled SQLServer does not need to query the underlying tables to retrieve this metadata. The main benefit to the client is the ability to create an updateable client side cursor based on the view.
WITH CHECK OPTION: say you have a view that shows you all customers from a particular postal district. The customer moves so you update the customers record to the new postal district in which they reside. Then the customer will disappear from the view. This is entirely logical, but potentially annoying for users of the view. Further by default you can INSERT data via the view that does not match the views restrictions. The view restricts data access but not updates or inserts. To avoid this situation you use the WITH CHECK OPTION option. Then any updates and inserts must also comply with the WHERE condition of the view.
The SELECT statement on which the view is based must follow some restrictions. It cannot:
The code for a view is stored in the syscomments table. You can view the text of a view using sp_helptext (if the view has not been encrypted).
A view can be used in a SELECT statement exactly as you would use a table. This is not the case when you want to update data. By default a view based on one or many tables is updateable meaning you can update, delete and insert data through the view as you would do directly to the table, with some restrictions:
All of these restrictions can be avoided with INSTEAD OF triggers (see later).
A new feature of SQLServer 2000 (developer and enterprise editions) is the ability to index views. When you index a view the view result set is stored in the database and updated dynamically. The main advantage is performance. For complex views of multiple tables the view becomes a kind of super index that spans multiple tables. Indexed views are like Oracles materialized views. Obviously if INSERT and UPDATE performance is more important than SELECT performance indexed views are not a wise choice. The first index created on a view should be a clustered view after which you can create non-clustered indexes as well. See books online for further details on indexed views.
Another new feature to SQLServer 2000 is the partitioned view, which allows parallel processing among a number of servers in a server farm. An important fact about partitioned views is that the table is partitioned horizontally into non-overlapping parts. The servers are connected as linked servers. Partitioned views will also automatically update the right table depending on the value given in the query, assuming a set of other criteria are met (see books online for detail). Also there are some restrictions on INSERT, DELETE and UPDATE statements.
An example from the Northwind database is:
create view "Alphabetical list of products" AS
SELECT Products.*, Categories.CategoryName
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE (((Products.Discontinued)=0))
A stored procedure is a batch of TSQL statements stored under a name and executed as a single unit of work. A stored procedure can be called from another stored procedure, from a client application or from a TSQL batch to perform a predefined action. They carry the following advantages:
When a stored procedure is created it is parsed and then normalised. The normalisation process breaks the query into manageable parts and assembles them into a query tree that is stored in the syscomments table. The first time a stored procedure is executed the query tree is optimised and transformed into a query plan that is stored in memory (in the procedure cache). That query plan is compiled and executed. Subsequent execution of the same procedure will use the query plan that is stored in the procedure cache to enhance performance and avoid executing the previous steps.
To create a stored procedure use CREATE PROCEDURE.
To alter a stored procedure use ALTER PROCEDURE.
The main options are:
If the stored procedure name starts with a single # then the procedure is temporary and local to the connection and usable only to the connection used to create it. If it starts with ## it is temporary but global to all connections, that is usable by all connections. SQLServer automatically deletes a temporary stored procedure when the connection used to create it closes.
You can use any TSQL statement in a stored procedure but some limitations exist:
Stored procedure creation uses a process called deferred name resolution, meaning that if a procedure is referencing a table that does not exist at the procedure creation time, the procedure is created without errors as the object names will be resolved at execution time.
Note that a stored procedure may depend on one or many other stored procedures or user-defined functions. You can check on such dependencies using the sp_depends system stored procedure.
One of the main advantages of using stored procedures is the ability to accept input and output parameters. As for variables, a parameter’s name always begins with an @ and is of a defined data type. A stored procedure can have up to 2100 parameters. Each parameter can also have a default value, used if the calling client does not supply a value. Parameters can be input or output and you can also return a value using the RETURN statement.
sp_recompile table_name forces every stored procedure and trigger using this table to be recompiled, useful if you add a new index to a table or new statistics which render obsolete the current execution plans.
To run a stored procedure from TSQL you use the EXECUTE command though even this is not necessary if the call is the first statement in the batch. If you call a stored procedure that returns a value you assign the call to a variable of suitable type.
We're now going to digress briefly and have a quick look at two facilities you may need to use in your stored procedures and elsewhere – error handling and explicit transactions.
Error handling is one of the weaker areas of TSQL which hopefully will be improved with the next release. SQLServer errors are composed of:
The @@ERROR system function is used to handle errors. If @@ERROR returns 0 the statement has executed without error. Anything else is an error number which can be coded against.
You can also add your own error messages to those provided. All SQLServer messages are stored in the sysmessages table, using error numbers over 50,000. You add the message with the sp_addmessage system procedure and can then raise these messages with the RAISERROR statement.
Here's an example of a stored procedure that includes some basic error handling:
CREATE PROCEDURE add_author
@au_id varchar(11),@au_lname varchar(40),
@au_fname varchar(20),@phone char(12),
@address varchar(40) = NULL,@city varchar(20) = NULL,
@state char(2) = NULL,@zip char(5) = NULL,
@contract bit = NULL
AS
-- Execute the INSERT statement.
INSERT INTO authors
(au_id, au_lname, au_fname, phone, address,
city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
@city,@state,@zip,@contract)
-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
RETURN(0)
END
SQLServer ensures that every single transaction operation is either completed (committed) or rolled back. However, you may wish to involve more than one operation in a transaction. In such situations you may explicitly declare a transaction and determine whether or not the transaction should be committed or rolled back. By testing for the success or failure of a particular operation the programmer can determine if all statements inside the transaction should be marked as completed. As with individual transactions, if the server should fail in the middle of a transaction, SQLServer will automatically undo the transaction when it is restarted.
You should ensure that any transaction started is either committed or rolled back. An open transaction holds resources and can prevent the transaction log from being backed up.
The BEGIN TRANSACTION statement begins a transaction. All statements that occur until a ROLLBACK or COMMIT statement will consume resources and hold locks to ensure that the transaction can be completed. The BEGIN TRANSACTION statement allows for the naming of the transaction thus allowing nesting of transactions, as well as marking of transactions. The WITH MARK keywords mark the transaction log and allow a restore of the server up until this point in time at a later date.
The SAVE TRANSACTION statement allows the programmer to mark a section of work as completed, although it is not committed. This allows additional statements to proceed and be undone without undoing the work to this point.
The ROLLBACK TRANSACTION statement cancels a transaction and undos any work that has been performed with TSQL statement between it and the BEGIN TRANSACTION STATEMENT.
The COMMIT TRANSACTION statement will mark all the work done as complete with the transaction then being considered to be complete.
UDFs enhance views and stored procedures opening the door to new levels of functionality. A UDF is a named set of TSQL statements used like system functions or views. There are two main types:
Functions are created with the CREATE FUNCTION statement, modified with ALTER FUNCTION and dropped with DROP FUNCTION. The different types of functions share some syntax elements as well as having their own. They share ENCRYPTION and SCHEMA BINDING which are as per views.
The function can accept parameters and contain any TSQL statement as per a stored procedure. The return value is specified via the RETURN statement.
e.g.
CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters.
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END
Two types:
inline: can be considered to be a view with parameters. They execute one SELECT statement, like a view, but can include parameters, like a stored procedure.
multistatement: builds the resultset from one or many SELECT statements. Typically the manipulation will involve temporary storage of result sets in temporary tables.
You may encounter functions that start with :: - these are system UDFs
e.g. (Table-valued UDF)
CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar(80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S
INNER JOIN Orders AS O ON (S.ShipperID = O.ShipVia)
WHERE O.Freight > @FreightParm
RETURN
END
A trigger is a special form of stored procedure, bound to a table or to a view, and which fires automatically. Triggers are generally used to enforce referential integrity and business rules. While triggers are functionally similar to CHECK constraints, they have one major difference: triggers are reactive while constraints are proactive. This means that a constraint is fired before the statement is executed, while a trigger is fired after or instead of the firing statement. Two types of trigger exist:
Triggers can be used to go beyond declarative referential integrity and to implement more complex rules than those possibly defined via CHECK constraints. Example situations where triggers are used are: maintaining denormalised data, complex cascading updates, inserts or deletes, and comparing data before and after updates.
A trigger is part of the transaction started by the statement that fired it. Thus, if the trigger fails the firing statement fails and is rolled back.
AFTER triggers can only be created on tables. A table can have any number of AFTER triggers defined for inserts, delete and updates all created via the CREATE TRIGGER syntax. Options are mostly similar to other code modules, e.g. WITH ENCRYPTION and NOT FOR REPLICATION.
The WITH APPEND option allows multiple triggers for the same statement on databases with a compatibility level <=65, though this is the default behaviour for later versions.
Triggers can be nested up to 32 levels. They can be recursive if the database option RECURSIVE_TRIGGERS is turned on.
INSERT, DELETE and UPDATE triggers fire on successful completion of the pertinent statement. They use temporary tables to achieve their functionality (Inserted and Deleted) that can be referenced by the trigger code. So inserts use Inserted, deletes Deleted and updates Inserted and Deleted – the new value in the Inserted temporary table and the old value in the Deleted temporary table.
INSTEAD OF triggers are fired instead of the statement that fires it. They can be created on tables or on views but the table or view can have only one INSTEAD OF trigger per action. They commonly avoid the need to rollback transactions.
Note that an INSTEAD OF trigger cannot be created on a table that has a foreign key with CASCADE DELETE OR CASCADE UPDATE. Similarly an INSTEAD OF DELETE trigger cannot be defined on a table enforcing the ON DELETE CASCADE option on a FOREIGN KEY constraint. Finally an INSTEAD OF UPDATE trigger cannot be defined on a table enforcing the ON UPDATE CASCADE option on a FOREIGN KEY constraint.
When created on a view an INSTEAD OF trigger enhances the updatability of the view. We’ve seen that a view can only update, delete or insert data in one base table at a time. With INSTEAD OF triggers this limitation disappears.
Note that triggers can cause a dramatic performance loss due to the fact that locks are held on modified records while the trigger does its work. The more locks the longer the transaction and the fewer users can be supported by the server. However, triggers are fast ... like stored procedures they are compiled and stored in the data cache. Still, you should both keep triggers short and test the impact of triggers on performance.
A trigger can have more than one AFTER trigger for a defined action, which can lead to potential problems, so take care. Due to this fact it is necessary to be able to change the order of firing of these triggers, achieved via the sp_settriggerorder system stored procedure. You may also need to disable triggers, which is achievable via ALTER TABLE.
See Books Online for syntax examples.
That completes our consideration of views, stored procedures, user defined functions and triggers. In the next article we turn our attention to how we may access the data stored in our databases.
MCSE: SQLServer 2000 Design
Israel and Jones
Sybex
MSDN