Thursday, January 27, 2011

An Inside View

I recently had to create a number of views which forced me to brush up on “view” internals, I’m more of a stored proc. kind of guy.  I had forgotten many of the rules and nuisance's involved in creating and maintaining views so I’ve decided to record them in this post for my reference.
I will use the below two tables for demonstration:
USE tempdb; GO CREATE TABLE People( PersonID   INT PRIMARY KEY, FirstName  NVARCHAR(50), MiddleName NVARCHAR(50), LastName   NVARCHAR(50) NOT NULL
);
GO CREATE TABLE Credit( CreditID   INT IDENTITY PRIMARY KEY, PersonID   INT  REFERENCES People(PersonID), Limit      MONEY ); GO INSERT People SELECT DISTINCT BusinessEntityID,
     
FirstName,
     
MiddleName,
     
LastName FROM AdventureWorks2008.Person.Person p JOIN AdventureWorks2008.Sales.SalesOrderHeader s ON p.BusinessEntityID = s.CustomerID; GO INSERT Credit SELECT DISTINCT p.BusinessEntityID,
     
MAX(CEILING(TotalDue)) FROM AdventureWorks2008.Person.Person p JOIN AdventureWorks2008.Sales.SalesOrderHeader s ON p.BusinessEntityID = s.CustomerID GROUP BY p.BusinessEntityID;

First rule to point out is the column limitation involved with views.  A view is limited to ONLY 1,024 columns.  In many cases a collection of 1,024 columns would be considered a database, but for those who love to push the upper limit have at it as I am not going to test this, not that I’ll admit.
Another key point to keep in mind is that when creating a view all derived columns must have column names supplied, if the column name is not specified and the column is not derived the column name is the same as the name in the SELECT statement.  For example, the below error occurs when attempting to create a view that uses a derived column and does not supply a column alias:
image
Column names can be added in the query body, in this case after the derived column concatenating the three name columns, “AS FullName”, or the column names can be specified immediately after the view name, these must be in the same ordinal position as the columns defined in the SELECT statement defined in the view:
image
Some quick and easy rules to consider:
A view CAN NOT:
  • Contain the INTO keyword
  • The OPTION clause
  • COMPUTE or COMPUTE BY clauses, these are deprecated anyway so STAY AWAY.
  • Reference a table variable or temporary table
  • Exceed nesting level of 32
  • The ORDER BY clause can only be used if the TOP clause is included
I can’t help but mention the rule of using ORDER BY in a view.  Prior to SQL 2005 the “work around” to using ORDER BY in a view was to use TOP as this:
CREATE VIEW …
AS
SELECT TOP 100 PERCENT..
ORDER BY
Believe it or not this worked and ordering was honored when the view was SELECTed!  (Don’t hate the player..  Hate the game)  In SQL 2005, and up, this method can still be included within the body of a view, but the ordering is not honored when the view is called from a SELECT statement so the order must be specified from the calling query. 

Transactions Using Views

Views can server as a means to INSERT, UPDATE, OR DELETE to one of the underlying base tables.  There are obviously some limitations with this functionality:
  1. The INSERT, UPDATE, or DELETE statement can only reference columns from one base table. CREATE VIEW Updateable AS
    SELECT
    p.PersonID,
         
    FirstName,
         
    MiddleName,
         
    LastName,
         
    FirstName + ' ' +
         
    ISNULL(MiddleName, '') +
         
    ' ' + LastName AS FullName,
         
    Limit FROM People p JOIN Credit c ON p.PersonID = c.PersonID;
    --This fails as the UPDATE references
    --columns from two tables, People(LastName) and Credit
    (Limit) UPDATE Updateable SET LastName = 'Dye',
       
    Limit = 1000000 WHERE PersonID = 11000

    --This UPDATE statement succeeds since
    --only the People(LastName) is referenced
    UPDATE Updateable SET LastName = 'Dye' WHERE PersonID = 11000
  2. Columns being modified must directly reference the underlying table, the column can not be derived, such as the FullName column in the above view
  3. Columns being modified are not affected by GROUP BY, HAVING, or DISTINCT
  4. The TOP clause is not used in the SELECT statement along with the WITH CHECK OPTION clause
  5. The INSERT, UPDATE, or DELETE can not violate any constraint on the affected base table, NULL, primary key, foreign key, etc.
To handle data modifications of a view INSTEAD OF triggers can be placed on a view to handle INSERT, UPDATE, and/or DELETE. 

Indexed Views

A view that uses WITH SCHEMABINDING can have a clustered unique index,  WITH SCHEMABINDING binds the view to the underlying table(s) which means that the referenced table(s) can not be modified in any way that would affect the view definition.  This can be used to improve the performance of the view, but keep in mind that the index has the same overhead as would any unique clustered index.  The limitations of creating an indexed view:
  1. The column(s) referenced in the index must be unique
  2. A view that contains an OUTER JOIN can not be indexed

    CREATE VIEW IndexedViewOuter WITH SCHEMABINDING AS
    SELECT
    p.PersonID,
         
    FirstName,
         
    MiddleName,
         
    LastName,
         
    FirstName + ' ' +
         
    ISNULL(MiddleName, '') +
         
    ' ' + LastName AS FullName,
         
    Limit FROM dbo.People p RIGHT OUTER JOIN dbo.Credit c ON p.PersonID = c.PersonID --This will fails as the view 
    --uses an outer join, as well as the referenced column are not unique
    CREATE UNIQUE CLUSTERED INDEX ix_indexedview ON IndexedViewOuter(LastName, FirstName, MiddleName)
  3. The query can not use "SELECT *"
A detailed technical article regarding indexed views can be found here.
If the WITH SCHEMABINDING option is used then the ability to create a view using “SELECT * ” is prohibited, which is not necessarily a bad thing.  It is poor practice to use a SELECT all in a view as this can cause issues if the table structure should change:
--Create a table and insert values into it CREATE TABLE tt (
   
test1 INT PRIMARY KEY,
   
test2 INT
   
) GO INSERT INTO tt (test1, test2) VALUES (1,2) GO --Create the view select all columns with * CREATE VIEW vw_tt AS
SELECT
*,
     
GETDATE() AS 'My Date' FROM tt
GO
--Select * from the view SELECT * FROM vw_tt
GO
image
--Alter the table adding another column ALTER TABLE tt ADD test3 INT GO --Select from the view the GETDATE() is NULL now SELECT * FROM vw_tt

image
Since the underlying table has changed and the definition of the view used SELECT * along with a derived column of GETDATE() the result is that the derived column is returning the value of the newly added column.  This can be remedied using the system stored procedure sp_refreshview:
--Refresh the view sp_REFRESHVIEW vw_tt --Select from the view after it has been refreshed
--All columns appear now
SELECT * FROM vw_tt

image --Drop all objects DROP TABLE tt DROP VIEW vw_tt
MSDN’s online documentation for views can be found here and was the primary source for this post.

No comments:

Post a Comment