SQL Server 2000 Views

7.            Views

SQL Views employ a SELECT statement to create a new virtual table that behaves in a similar fashion to the real tables in the database.

Views can be used to hide the complexity of the underlying database structure or to show a subset of data. They are useful in presenting summary or aggregated information to users for a Decision Support or Reporting Application. The view can be redefined if any changes are made to the underlying table structures without affecting any of the existing management reports.

Views are also useful in implementing security and performance requirements. A view can be defined to allow read/write access to a subset of data to which users are otherwise denied access.

A partitioned view allows several tables to be joined together (with the UNION command) and processing to be spread over different databases or servers for parallel processing and improved performance.

SQL Enterprise manager or the CREATE VIEW command is used to create views. Use the ALTER VIEW command to change existing views that have references made to them in stored procedures or triggers.

CREATE VIEW [<database_name>.][<owner>.]view_name

[ (column[,...n])]
[ WITH <view_attribute> [ ,...n ] ]
AS
select_statement
[ WITH CHECK OPTION ]

The following example creates the TITLEVIEW view by joining three tables together. The view can be used in exactly the same manner as a normal table and will update the underlying tables.

CREATE VIEW titleview

AS

SELECT title, au_ord, au_lname, price, ytd_sales, pub_id

   FROM titleauthor

   INNER JOIN authors ON authors.au_id = titleauthor.au_id

   INNER JOIN titles ON titles.title_id = titleauthor.title_id

Care should be taken with the ownership of Views an the underlying tables. In general, it is best to have the database owner (dbo) as the owner of all views and tables. Use the sp_changeobjectowner system stored procedure to change ownership.

Any standard SELECT statement can be used including complex queries with UNION, GROUP BY, and HAVING. An ORDER BY clause however is not allowed unless used in conjunction with the TOP clause.

Views with  aggregate or computed fields in the SELECT syntax cannot be modified.

The sp_depends viewname and sp_helptext viewname system procedures will display the dependent columns and the syntax of the view respectively.

Indexed Views

Views that contain summary information need to retrieve the underlying information each time they are used by the calling application. Creating an index on the View forces SQL Server to retrieve and permanently store the index in the database vastly improving performance.

The SCHEMABINDING option must be used on a View before indexing is permitted:

CREATE VIEW

CREATE INDEX…

Maintaining an index on a View adds an overhead and should not be used on very volatile data that is frequently updated. Careful design of the index can yield fruitful results as the new Index can be used by the query optimiser in any query even if the View itself is not involved.

Check Option

Views are a great way to provide limited access to data for selected users. A View on an Employee table may be defined without any salary details and permissions denied on the original table to simplify security access for this sensitive data.

The CREATE VIEW syntax has a WITH CHECK OPTION that prevents data being added or modified within the view that cannot subsequently be retrieved from the view.

The following example creates a view that only shows authors with contracts and will not allow an author to be added or modified without the contract field having a value of one:

CREATE VIEW authorscontracts AS

   SELECT * FROM authors

   WHERE contract = 1

   WITH CHECK OPTION

Partitioned Views

A special case of Views that UNION several tables of identical structure is known as a partitioned view. These tables can be local, within a single database, or distributed on several databases, perhaps even on different servers.

The data is usually partitioned on some logical basis such as the inclusion of a country code in the table and a check constraint is set on each table so that the query optimiser can determine which tables to look at for a typical query.

The view is then created by UNIONing all the tables and an updateable partitioned View results. The advantage of spreading each table over different databases or servers allows the query to run in parallel on multiple processors or servers and can speed performance on very large databases.

A partitioned view over several databases or servers, with an index, can provide very powerful parallel processing facilities for very large databases.

OPENROWSET

SQL Server can use OleDB/ODBC middleware to connect to external datasources directly from the server. The following example uses an ODBC datasource defined on the server to connect and retrieve data from a FoxPro table:

select * from openrowset( 'MSDASQL',

    'DSN=dsnfoxtastrade',

    'select * from shippers where company_name like ''U%''')

The OPENROWSET command is used for ad hoc queries and is much more flexible when a connection string is used rather than a pre-defined ODBC datasource.

Linked Servers

A linked server can be defined using the Security-Linked Servers option of the SQL Executive or the sp_addlinkedserver system stored procedure. This defines a permanent relationship between the SQL Server and another SQL Server or external datasource.

The following example adds a linked server, called FOXTASTRADE, to the current SQL Server using an existing ODBC datasource:

EXECUTE sp_addlinkedserver

   @server='foxtastrade',

   @srvproduct='foxpro',

   @provider='MSDASQL',

   @provstr='DSN=dsnfoxtastrade'

The MSDASQL is the generic driver to connect to ODBC datasources. More specific drivers can be easily defined e from the Security-Linked Servers option in the SQL Executive.

The OPENQUERY() function can be used to execute a pass through query directly on the linked server and return a result:

SELECT * FROM

   OPENQUERY(foxtastrade,

   'select * from category where category_name like ''B%''')

The sp_serveroption system stored procedure may be required to set the default database options for the linked server (collation sequence, etc).

Distributed queries can also be run on linked server by using the full four part object reference:

select * from linkedserver01.pubs.dbo.authors

Information on the database schema contained inside a linked server can be obtained with the relevant system stored procedure: sp_linkedservers, sp_catalogs, sp_indexes, sp_tables_ex, sp_columns_ex.

Temporary Tables

Temporary tables can be created on the server using a SQL SELECT statement. These temporary tables can be used for reporting purposes or to perform interim calculations as part of a batch process.

Temporary Tables have their name prefixed with # or ## for local and global tables respectively. Local tables are only available for the current user session or perhaps just within the scope of a single stored procedure. Global temporary tables are available to all users of the database and are deleted only when the last session that refers to the table is closed.

The following command sets up a temporary table which all users can access:

select *

   into ##contractauthor

   from authors

   where contract = 1

The SELECT .. INTO .. syntax may also be used to create a new permanent table provided that the SELECT INTO/BULKCOPY database option is set to True:

exec sp_dboption 'pubs', 'select into', TRUE