SQL Server 2008 : Implementing Objects - Viewing and Modifying Data

A view is a database object that represents a saved SELECT statement. Views are also referred to as virtual or logical tables. Views can be queried in the same way as tables, and some types of views can be updated, too. Using views instead of tables can greatly simplify data access and decouple client applications from the underlying tables containing actual data. With appropriate use of views, it is possible to completely change the schema of the database and redesign the tables without breaking any client applications. Think of views as an abstract interface between your physical database tables and the people or applications querying them.

Creating Views

SQL Server 2008 allows you to create views of the following types:

• Standard View This view is based on one or more base tables. The view may include joins, filter restrictions (using the WHERE clause), and row count restrictions (using the TOP and ORDER BY clauses). You cannot use the ORDER BY clause in a view without specifying the TOP clause as well.

• Updateable View A view that is based on a single underlying table can be updated directly. Executing INSERT, UPDATE, DELETE, and MERGE statements on this type of view will affect the data in the underlying table. You can also define an INSTEAD OF INSERT, INSTEAD OF UPDATE, and INSTEAD OF DELETE triggers on any view, which will perform a particular action when you attempt to insert, update, or delete data in the view.

• Indexed View Sometimes it is valuable to create one or more indexes on a view in order to optimize the time it takes to query the view. Indexes can be created on views using standard CREATE INDEX syntax.

• Partitioned View A partitioned view joins data that is spread across a table partitioned horizontally—for example, if you have partitioned a table by OrderDate to store orders from five years ago and earlier in one partition, orders created within the last five years in another partition, and orders created this year in yet another partition. A partitioned view will join all the partitions together into one Orders virtual table containing data from all three partitions.

To create a view, use the CREATE VIEW statement syntax shown in Example 1

Example 1. CREATE VIEW Statement—Syntax

CREATE VIEW [schema_name].view_name[(column_names)] [ WITH ENCRYPTION | SCHEMABINDING ] AS select_statement [ WITH CHECK OPTION ]

Specifying the column_name in a view definition allows you to assign names to computed columns or to rename columns produced by the SELECT statement. This is useful for calculated columns and columns that may have ambiguous names. If you don’t specify explicit column names, the view columns will inherit the same names as the columns in the SELECT statement.

Specifying the WITH ENCRYPTION option encrypts the view definition. This also prevents the view from being used in replication.

Configuring & Implementing...: Using the SCHEMABINDING Option to Lock in a View’s Underlying Schema Views are named SELECT statements and include one or more columns from one or more tables. What will happen if a column or table referenced by a view is dropped from the database? The view will become invalid and will return an error the next time it is queried. To lock the view into the schema objects it relies on, add the WITH SCHEMABINDING option to your CREATE VIEW statement. This option enforces that any table or column referenced by this view cannot be dropped or altered, until the view itself is dropped. This applies only to columns referenced by the view. You can freely add and remove columns from underlying tables, as long as they are not used in the view. Only specify the SCHEMABINDING option when the view references tables from a single database.

Example 2 creates a view based on the Stars table using the SCHEMABINDING option. We then attempt to alter the underlying structure of the base table but receive an error. Figure 14.4 demonstrates how the same view can be created using the graphical view designer in SQL Server Management Studio.

Example 2. Working with Views

CREATE VIEW MyStarsView WITH SCHEMABINDING AS SELECT StarName, StarType FROM dbo.Stars WHERE SolarMass >=1; GO SELECT * FROM MyStarsView; -- Results: -- StarName StarType -- -------- ---------------- -- Deneb White supergiant -- Pollux Orange Giant -- Sun Yellow dwarf ALTER TABLE Stars DROP COLUMN StarType; GO -- Results: --Msg 5074, Level 16, State 1, Line 1 -- The object 'MyStarsView' is dependent on column 'StarType'. -- Msg 5074, Level 16, State 1, Line 1 -- ALTER TABLE DROP COLUMN StarType failed because one or more objects access this column. -- This view is updateable, as it is based upon only one base table UPDATE MyStarsView SET StarType = 'White Supermassive Giant' WHERE StarType = 'White supergiant' GO SELECT * FROM MyStarsView; -- Results: -- StarName StarType -- -------- ------------------------ -- Deneb White Supermassive Giant -- Pollux Orange Giant -- Sun Yellow dwarf

Figure 1. Creating a View Using SQL Server Management Studio

Creating Stored Procedures

Stored procedures are Transact-SQL statements that perform one or more actions and are saved in the database with a name. Stored procedures, used widely to encapsulate the logic of your database system, can accept parameters and return values. Stored procedures are the only database object that can update data by executing DML statements. For example, you may write a stored procedure named AddCustomer that accepts a CustomerName, EMailAddress, and PhoneNumber parameter. The logic within this stored procedure can check that the potential customer’s details are valid, insert a new row into the Customers table using parameter values supplied, and then return the CustomerID of the newly created customer.

To create a stored procedure, use the CREATE PROCEDURE statement syntax shown in Example 3. The CREATE PROCEDURE keywords can be shortened to CREATE PROC. To change the definition or options of a stored procedure, use the ALTER PROCEDURE or ALTER PROC statement.

Example 3. CREATE PROCEDURE Statement—Syntax

CREATE PROCEDURE [schema_name].stored_procedure_name[; procedure_ number] [@parameter1_name parameter1_data_type [=default_parameter_value] [OUT | OUTPUT] [READONLY] [@parameter2_name parameter2_data_type...] [WITH ENCRYPTION | RECOMPILE | EXECUTE AS] AS [BEGIN] transact_sql_statements [END]

Stored procedures can be grouped into logical named groups. Each procedure within a group will have a unique procedure_number, while the entire group can be referred to using the procedure_name. The entire procedure group can be dropped at once using the DROP PROCEDURE statement. To use a single procedure, you can omit the procedure_number. In this case procedure_name will always be used to refer to it.

Parameters are named variables passed into the procedure. Parameter names always start with an @, and a data type must be specified for each parameter. You can also use the default_parameter_value to assign a default value to a parameter if the procedure was called without this parameter being supplied. The most common use of procedure parameters is to pass values to the stored procedure, so that it can use these values within the Transact-SQL statements that comprise it. Sometimes you must return values back to the caller of your stored procedure. To do this, mark each parameter you wish to return to the caller as OUTPUT or OUT (the two are equivalent). If the parameter is not to be updated within the stored procedure, you can specify it as READONLY.

Similar to defining views, specifying the WITH ENCRYPTION option encrypts the stored procedure definition. Specify the WITH RECOMPILE option to instruct the database engine never to cache the execution plan for this stored procedure. Instead, the optimal execution plan will be calculated every time the procedure is called. The EXECUTE AS option allows you to run the procedure as an alternative set of user credentials, different from those of the caller.

Example 4 creates and executes a stored procedure to add a new row into the Stars table. The procedure accepts parameters for the star name, star type, solar mass, and description; and returns the ID of the newly created star.

Example 4. Creating and Executing a Stored Procedure

CREATE PROC AddNewStar @ID int OUT, @StarName varchar(50), @SolarMass decimal(10,2), @StarType varchar(50), @Description ntext = 'No description provided.' AS BEGIN DECLARE @NextStarID int SET @NextStarID = (SELECT MAX(StarID) FROM Stars) SET @NextStarID = @NextStarID + 1 INSERT dbo.Stars(StarID, StarName, SolarMass, StarType, Description) VALUES(@NextStarID, @StarName, @SolarMass, @StarType, @Description) SET @ID = @NextStarID END; DECLARE @NewStarID int EXECUTE AddNewStar @NewStarID OUT, 'Sigma Octantis', 5.6, 'Giant' SELECT @NewStarID as NewStarID SELECT * FROM Stars -- Results: -- (1 row(s) affected) -- NewStarID -- ----------- -- 4 -- (1 row(s) affected) -- StarID StarName SolarMass StarType Description -- ------ -------- --------- -------- ------------ -- 3 Deneb 6.00 White Supermassive Giant Deneb is the... -- 1 Pollux 1.86 Orange Giant Pollux,also... -- 4 Sigma 5.60 Giant No description... Octantis -- 2 Sun 1.00 Yellow dwarf No description... --(4 row(s) affected)

Creating Functions

Functions, like stored procedures, are saved Transact-SQL statements. Unlike stored procedures, functions cannot perform actions by executing DML statements. Functions always return a single value or a single table-valued expression. They are used by database developers to encapsulate and reuse calculations. For example, you may create a function to calculate the tax amount given a particular salary or to determine whether an e-mail address that has been provided is valid.

It is possible for a function to take no parameters, but often functions accept multiple input parameters and use the parameter values in the calculation which the particular function represents. Unlike stored procedures, functions do not support output parameters. The following types of functions are available within SQL Server 2008

• Scalar functions These functions return a single value of any data type.

• Single statement table-valued functions These functions execute a single SELECT statement and return the result of this statement as a table-valued expression.

• Multiple statement table-valued functions These functions return several table-valued expressions created by one or more SELECT statements.

• Built-in Functions SQL Server provides many built-in functions to perform common tasks. For example, the GETDATE() built-in function returns today’s date and time. The AVG() function returns the average value across a column.

You can use the CREATE FUNCTION statement to create new functions using the syntax shown in Example 5. You can use the ALTER FUNCTION statement to change the function’s definition.

Example 5. CREATE FUNCTION Statement—Syntax

CREATE FUNCTION [schema_name].function_name ( [@parameter1_name parameter1_data_type [=default_parameter_value], [@parameter2_name parameter2_data_type...] ) RETURNS data_type AS transact_sql_statements

Example 6 demonstrates how to create and use scalar and table-valued functions.

Example 6. Working with Functions

CREATE FUNCTION ConvertKilogramsToPounds (@Kilograms decimal(18,2)) RETURNS decimal(18,2) AS BEGIN DECLARE @Pounds decimal(18,2) SET @Pounds = @Kilograms * 2.21 RETURN (@Pounds) END PRINT dbo.ConvertKilogramsToPounds(5) -- Results: -- 11.05

Creating Triggers

Triggers are stored procedures that are bound to a table or view. They run when a DML statement is executed on the table or view. You can specify triggers as FOR UPDATE, FOR INSERT, and FOR DELETE. These triggers will execute immediately after INSERT, UPDATE, or DELETE operations. You can also create INSTEAD O F UPDATE, INSTEAD OF INSERT, and INSTEAD OF DELETE triggers. These triggers will execute without the data being actually inserted, updated, or deleted.

A trigger can query tables and views, execute DML statements, and include complex Transact-SQL logic. The trigger and DML statement that caused the trigger to fire occur within the context of a single transaction. It is possible to roll back INSERT, UPDATE, and DELETE statements from within a trigger. This is useful for complex data validation purposes. You can use triggers to manually cascade changes through related tables; to guard against malicious or incorrect insert, update, and delete operations; and to enforce other restrictions that are more complex than those defined by using CHECK constraints.

Warning

Triggers should be used sparingly because they have severe performance implications. In addition,, triggers can be difficult to maintain.

Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use a SELECT statement from another table to compare to the inserted or updated data and to perform additional actions, such as modifying the data, or displaying a user-defined error message. Triggers can evaluate the state of a table before and after a data modification and take actions based on that difference. Multiple triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple different actions to take place in response to the same modification statement. Triggers also allow the use of custom error messages.

Triggers can be specified as FOR, AFTER, or INSTEAD OF. The trigger action will fire during the DML statement, after the DML statements, or in place of the DML statement, respectively. Triggers can be specified for UPDATE, INSERT, DELETE, or any combination of these.

How do you know what data the user is attempting to insert, update, or delete within a trigger? The trigger can access special tables called INSERTED and DELETED. These virtual tables exist only while the trigger is executing. The INSERTED table contains the new values you are attempting to insert into the table, or new values of the row when you are attempting to update data. The DELETED table contains the row you are attempting to delete or old values of the row when you are attempting to update data. Make use of these tables by querying them to determine old and new values of the data being affected. To cancel the DML statement from within a trigger and roll it back, use the ROLLBACK TRANSACTION statement.

Example 7 demonstrates how to create triggers, and the effect they take after a DML statement is executed on the table to which the trigger is bound.

Example 7. Creating a Trigger on the Stars Table

CREATE TABLE StarHistory (StarHistoryId int IDENTITY PRIMARY KEY, StarName varchar(50), OldType ntext, NewType ntext, DateChanged DateTime); GO CREATE TRIGGER UpdateStarHistory on dbo.Stars AFTER INSERT, UPDATE AS BEGIN INSERT StarHistory (StarName, OldType, NewType, DateChanged) SELECT INSERTED.StarName, DELETED.StarType, INSERTED.StarType, GETDATE() FROM INSERTED LEFT JOIN DELETED on INSERTED.StarID = DELETED.StarID END GO UPDATE Stars SET StarType = 'Burnt out' WHERE StarName = 'Sun'; GO SELECT * FROM StarHistory -- Results: -- StarHistoryId StarName OldType NewType DateChanged -- ------------- ---------- --------------- ----------- --------------- -- 1 Sun Yellow dwarf Burnt out 2009-01-21 11:56:29.530

Tip

You don’t need to be able to write a trigger for the exam. Make sure that you understand the concepts behind triggers and why you may wish to use them. Remember that triggers can be defined on views as well. Creating INSTEAD OF triggers on a view that is not updateable will allow you to perform actions when a user attempts to insert, update, or delete data in the view.