Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 20, 2003

Indexed Views Basics in SQL Server 2000 - Page 3

By Andrew Novick

Restrictions on Columns in the View

The columns that can be in the SELECT list or WHERE clause of an indexed view are restricted. There are two possibilities based on the query: GROUP BY or no GROUP BY. If there is no group by, the columns may not contain expressions. However, they can contain computed fields in base tables and user-defined functions. On the other hand, if there is a GROUP BY clause, the following restrictions apply:

  • These aggregate functions may not be used: COUNT(*), STDEV, VARIANCE, AVG, MIN, MAX
  • No SUM aggregate function on a NULLable expression.
  • The view must contain a COUNT_BIG(*) expression

If these conditions are satisfied, it is possible to create an index on the view. However, not all columns in the view are eligible for indexing.

Selecting Columns for the Index

There are limitations on which columns from the view can be in the index. The column expressions must be deterministic and may not have floating-point data. The view can have floating point columns (real or float), it is just that no floating point columns can be in the index.

There are three properties returned by the SQL Server built-in function COLUMNPROPERTIES that tell you if columns in a view qualifies to be included in an index. Each of the columns returns a BIT value that is 1 when the column has the property. They are:

  • IsIndexable - Gives the answer: can the column be indexed.
  • IsDeterminstic - Non-deterministic columns cannot be indexed.
  • IsPrecise - 1 when the column is not a floating point type.

In addition, the IS_Nullable column may come in handy when figuring out why a column can't be indexed and there is a SUM aggregation.

To give you a quick rundown about which columns of a view can be included in an index I have put together the user-defined function udf_View_ColumnIndexableTAB. Here is the CREATE FUNCTION script for it.

SET QUOTED_IDENTIFERS ON
SET ANSI_NULLS ON
GO

CREATE  FUNCTION udf_View_ColumnIndexableTAB (

    @view_name_pattern sysname = NULL -- View name or pattern to
                   -- search for. NULL for all
  , @col_name_pattern sysname = NULL -- Column name or pattern to
                   -- search for. NULL for all
) RETURNS TABLE
/*
* Returns a table of the columns in views whose name match the 
* patterns in the parameters and the status of the columns as
* indexable, deterministic, and precise.
*
* Example:
SELECT * FROM udf_View_ColumnIndexableTAB(NULL, NULL)
*
* ) Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published in the UDF of the Week Newsletter Vol 1 Number 19
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS RETURN 
SELECT TOP 100 PERCENT WITH TIES
       C.TABLE_SCHEMA AS [Owner]
     , C.TABLE_NAME AS [VIEW_NAME]
     , COLUMN_NAME 
     , ORDINAL_POSITION 
     , dbo.udf_SQL_DataTypeString (C.DATA_TYPE
                                 , C.CHARACTER_MAXIMUM_LENGTH
                                 , C.NUMERIC_PRECISION
                                 , C.NUMERIC_SCALE) AS DATA_TYPE
     , CASE WHEN 1=COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME)
                          , COLUMN_NAME, 'IsIndexable') 
            THEN 'YES' ELSE 'NO' END as IsIndexable
     , CASE WHEN 1=COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME)
                     , COLUMN_NAME, 'IsDeterministic') 
            THEN 'YES' ELSE 'NO' END as IsDeterministic
     , CASE WHEN 1=COLUMNPROPERTY(OBJECT_ID(C.TABLE_NAME)
                          , COLUMN_NAME, 'IsPrecise') 
            THEN 'YES' ELSE 'NO' END as IsPrecise
     , IS_NULLABLE
    FROM INFORMATION_SCHEMA.[COLUMNS] C
       INNER JOIN INFORMATION_SCHEMA.TABLES T
           ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
            AND C.TABLE_NAME = T.TABLE_NAME
    WHERE T.TABLE_TYPE='VIEW'
      AND (@view_name_pattern is NULL -- all tables
           OR C.Table_Name LIKE @view_name_pattern)
      AND (@col_name_pattern is NULL -- all columns
           OR [Column_Name] Like @col_name_pattern)
    ORDER BY C.TABLE_NAME
           , C.ORDINAL_POSITION

If you want to compile it, you will first need the definition of udf_SQL_DataTypeString, which was recently published in my T-SQL UDF of the Week newsletter. You can get it from the UDF of the Week Archives page.

The function's two parameters are patterns that work with the LIKE operator. The first parameter is on the view name. The second parameter is on the column name. LIKE operator, patterns are used instead of the names to give more flexibility to the UDF. The UDF can be queried to check the index eligibility of columns in our indexable view, OrderDetailsXSB:

-- Are columns in "OrderDetailsXSB" eligible for indexing?
SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE 
     , ISIndexable, IsDeterministic, IsPrecise
    FROM udf_View_ColumnIndexableTAB('OrderDetailsXSB', NULL)
GO
(Results)
COLUMN_NAME    Pos Type         ISIndexable IsDet IsPrecise Is_Nullable
-------------- --- ------------ ----------- ----- --------- -----------
OrderID          1 int          YES         YES   YES       No         
ProductID        2 int          YES         YES   YES       No         
ProductName      3 nvarchar(40) YES         YES   YES       No         
UnitPrice        4 money        YES         YES   YES       No         
Quantity         5 smallint     YES         YES   YES       No         
Discount         6 real         NO          YES   NO        No         

Most of the view's columns are indexable. The column that is not indexable is Discount. It has a floating-point data type, real. Had ExtendedPrice remained in the view it also would not have been indexable because it is an expression.

Now that you are aware of all the restrictions, it is time to create an indexed view. We will use the OrderDetailsXSB view because it satisfies all the restrictions.

Creating the Index

Indexes on views are created in the same way that indexes on tables are with the CREATE INDEX statement. Here is one to create a unique clustered index on OrderDetailsXSB that makes it an indexed view:

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

CREATE UNIQUE CLUSTERED INDEX [IDX_Order_Details_X] 
       ON OrderDetailsXSB (OrderID, ProductID
                         , ProductName, Quantity)
GO

That's all there is to creating it. Once it is created, SQL Server's query optimizer can select to use the index on any query where it is appropriate. As we will see, while it will be used for queries on OrderDetailsXSB it can be used on queries on other views.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date