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.
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:
SET ANSI_NULLS ON
GOCREATE 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 https://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
https://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
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:
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
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:
, 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
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.