Using SQL Views

SQL views can have different roles in your database system. Their basic role is to customize table data, but there are more complex roles. You can use standard SQL views to implement security and to ease export and import of data, or you can use the other types of views to achieve performance improvement.

Export and Import

Because they can transform and join data from one or more tables, standard SQL views are useful for exporting data out of your database system. Standard SQL views alone are not convenient for importing data, since you can insert data only in one base table at the time. Fortunately, you can add an Instead-of trigger to the view and then you will be able to modify multiple base tables (see "Triggers on Views" in Chapter 9).

Security Implementation

Standard SQL views are the preferred means of setting security when users are accessing the database through generic tools for accessing and editing database information (such as Access or Excel). Different users could have permissions to use different views with different filters on tables:

      CREATE VIEW dbo.vInventoryTrigonTower      AS      SELECT *      FROM dbo.vInventory      WHERE LocationId = 2 

Using this technique, the developer can also "filter" which columns are accessible by certain users. For example, you could allow only users from the accounting or human resources departments to view and edit salary data in a table containing employee information.

Reduce Complexity

Views are a very nice way to simplify queries. As shown in Figure 8-8, I've created a util.vSpaceUsed view, which lists all database tables and their sizes. Its results are very similar to the results of sp_spaceused. In the past, I used this procedure to get the size of tables. As you may remember, Microsoft recommends that developers use system procedures, instead of querying system tables directly, since the structure of tables might change between SQL Server versions. Unfortunately, sp_spaceused returns data about a single object only, and I often need a recordset that contains all tables. So, instead of calling sp_spaceused in a loop to collect data in the temporary table, I created a view that returns all data that I might be interested in:

      create view util.vSpaceUsed      as      select  distinct TOP 100 PERCENT             db_name()            as TABLE_CATALOG          , user_name(obj.uid)    as TABLE_SCHEMA          , obj.name              as TABLE_NAME          , case obj.xtype              when 'U' then 'BASE TABLE'              when 'V then 'VIEW      end                         as TABLE_TYPE      , obj.ID                    as TABLE_ID      , Coalesce((select sum(reserved)                 from sysindexes il                 where il.id = obj.id                 and il.indid in (0, 1, 255))            *     (select d.low from master.dbo.spt_values d                 where d.number = 1 and d.type = 'E')         ,0)                      as RESERVED      , Coalesce((select Sum (reserved) - sum(used)                 from sysindexes i2                 where i2.indid in (0, 1, 255)                 and id = obj.id)            *    (select d.low from master.dbo.spt_values d                  where d.number = 1 and d.type = 'E')         ,0)                      as UNUSED      , case obj.xtype          when 'U' then Coalesce((select iB.rows                                  from sysindexes i3                                  where i3.indid < 2                                  and i3.id = obj.id), 0)          when 'V then NULL         end                       as [ROWS]       Coalesce     (    (    (select sum(dpages)       from sysindexes                 where indid < 2 and id = obj.id              ) + (select isnull(sum(used), 0) from sysindexes                  where indid = 255 and id = obj.id                  )         ) * (select d.low from master.dbo.spt_values d                where d.number = 1 and d.type = 'E'           ) , 0)                  as [DATA]   , Coalesce(      ((select sum(reserved)      from sysindexes il      where il.id = obj.id      and il.indid in (0, 1, 255)      ) - ( (select sum(dpages) from sysindexes              where indid < 2 and id = obj.id          ) + (select isnull(sum(used), 0) from sysindexes                where indid = 255 and id = obj.id)        ) )      * (select d.low from master.dbo.spt_values d          where d.number = 1 and d.type = 'E')          ,0)                      as [INDEX]      from sysobjects obj      where obj.xtype in ('U', 'V')      and   permissions(obj.id) != 0      order by db_name(), user_name(obj.uid), obj.name 

Figure 8-8: Using util.vSpaceUsed

The view has a very complex structure, but its use is very simple. You just need to reference it in a SQL statement (see Figure 8-8).

Performance Improvement

Views are often used as a mechanism for improving system performance. When an index is added to the view, SQL Server typically does not have to query and join underlying tables—the request will be satisfied using data from the indexed view. Unfortunately, this feature can also degrade overall performance if the system is mostly modifying underlying tables.

Distributed partitioned views can divide the execution of workload among servers and provide an exciting new way to linearly scale out the performance of the system. Unfortunately, this is not a magic bullet, either—if typical queries need data from multiple servers, performance may be degraded.