SQL Server T-SQL User-Defined Function of the Week

Find out Which View Columns can be Indexed

Volume 1 Number 19   March 25, 2003

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Visit http://www.NovickSoftware.com for additional material
about SQL Server, Visual Basic, ASP, XML, and security.

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Indexed views are the topic of an article that I've written. It 
was published last week on DatabaseJournal.  You can see it at:
http://www.databasejournal.com/features/mssql/article.php/2119721
You probably got the special notice that I sent last week when
it was published.

There was a correction to the article posted on Friday. It 
relates to how indexed views work with Standard Edition of 
SQL Server. They work in Standard Edition but will not always be 
used in the same way as in Enterprise Edition. Details are in 
the article.

This week's UDF of the Week was published as part of that
article.  It's part of a theme that will appear over the next few
weeks regarding indexed views and the restrictions placed on 
UDFs so that indexed views can work correctly.

The existence of indexed views seems to have been the driving 
force behind many of the decisions that the SQL Server 
development team made when they created UDFs.  SQL Server 2000
goes a long way to insuring that UDFs can't have side effects
and that they can be used in such a way that they produce 
exactly the same result every time that they are executed.

Towards that goal there are many restrictions on indexed views. 
Among them are restrictions on the columns that can be in an 
index.  Columns that are not based on deterministic information
can't be indexed. Functions, such as getdate(), that return 
different values each time they are called are not deterministic.
It's also not possible to include floating point date types 
(float and real) in an index.  That's because the exact floating
point representation of a number might be different depending on
the machine architecture (Pentium vs Itanium vs Alpha) that
the results are computed on.  There were obviously thinking ahead
to 64-bit SQL Server.

The COLUMNPROPERTY built-in function provides the information 
you need to decide if a column can be included in an index.  It's
the source of the IsIndexable, IsDeterministic, and IsPrecise
columns from udf_View_ColumnIndexableTAB.

Here's the create function script:
/-------- Copy from below this line ----------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE   FUNCTION dbo.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 T-SQL 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

GRANT SELECT ON dbo.dbo.udf_View_ColumnIndexableTAB to [PUBLIC]
GO
\-------- Stop copying above this line -------------------------/

The example below is done in the PUBS sample database.  To try 
udf_View_ColumnIndexableTAB in PUBS you must first create the 
UDF of the Week from last week udf_SQL_DataTypeString.  Then
run the CREATE FUNCITON script above.

Running udf_View_ColumnIndexableTAB on all the columns in PUBS 
shows that none are indexable.  That's because the views in 
pubs were created without SCHEMABINDING.  To advance the 
discussion I've create the following script for an indexable 
version of the titleview view from pubs.  Here's the script:
/-------- Copy from below this line ----------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO
CREATE  VIEW titleviewSB WITH SCHEMABINDING AS
select t.title, ta.au_ord, a.au_lname, t.price, t.ytd_sales
     , t.pub_id
from dbo.authors a, dbo.titles t, dbo.titleauthor ta
where a.au_id = ta.au_id
   AND t.title_id = ta.title_id
GO
GRANT  REFERENCES ,  SELECT ,  UPDATE ,  INSERT ,  DELETE  
          ON [dbo].[titleviewSB]  TO [PUBLIC]
GO
\-------- Stop copying above this line -------------------------/

There are a few changes made to the original titleview view
in order to make it indexable.  For starters base tables must be
referenced with a two part name.  The WITH SCHEMABINING clause 
that is added to the view header is mandatory for any view that 
is going to be indexed. Also, make sure the QUOTED_IDENTIFIER and 
ANSI_NULLS SET options are both set to ON.  Incidentally, I added 
aliases to all tables in the view but that's just for my 
convenience.

Now lets try out the UDF of the week on both the original 
titleview view and titleviewSB, which is indexable:
/-------- Copy from below this line ----------------------------\
SELECT View_Name, Column_Name as [Column]
     , Ordinal_Position as Pos, DATA_TYPE
     , IsIndexable as IDX, IsDeterministic as Det
     , IsPrecise as IsPrec, Is_Nullable as [Nullable]
    FROM udf_View_ColumnIndexableTAB('titleview%', null)
GO
\-------- Stop copying above this line -------------------------/
(Results)
View_Name   Column    Pos  DATA_TYPE   IDX Det Prc Nullable
----------- --------- ---- ----------- --- --- --- --------
titleview   title        1 varchar(80) NO  NO  NO  No      
titleview   au_ord       2 tinyint     NO  NO  NO  YES     
titleview   au_lname     3 varchar(40) NO  NO  NO  No      
titleview   price        4 money       NO  NO  NO  YES     
titleview   ytd_sales    5 int         NO  NO  NO  YES     
titleview   pub_id       6 char(4)     NO  NO  NO  YES     
titleviewSB title        1 varchar(80) YES YES YES No      
titleviewSB au_ord       2 tinyint     YES YES YES YES     
titleviewSB au_lname     3 varchar(40) YES YES YES No      
titleviewSB price        4 money       YES YES YES YES     
titleviewSB ytd_sales    5 int         YES YES YES YES     
titleviewSB pub_id       6 char(4)     YES YES YES YES     

None of the columns of the original view, titleview, are 
indexable because the view is not indexable.  I suppose that 
accounts for why the IsDeterministic (Det) property is also 
false for all the columns in the view.

There's a more complete example and much more information about
indexed views in the article.  They're a feature of SQL 
Server 2000 that can create a dramatic improvement in query 
performance.

Please don't forget to share this issue with anyone interested
in SQL Server.  Thanks.

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try and 
respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+