Clustered Index on a View 1

[rheilman]

This is a newbie question on creating an indexed view. The following code (minus proprietary field information) is my attempt at creating the indexed view. My resource for the information is Books Online.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
USE MyDataBase
GO
CREATE VIEW dbo.vwMyView
WITH SCHEMABINDING
AS SELECT <my field list>
FROM dbo.MyTable
WHERE MyDateField >= '1/1/2000'
AND <my other conditions>
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX RowID
ON dbo.vwMyView (RowID)
CREATE INDEX MyField
ON dbo.vwMyView (MyField)
GO

The view creates successfully and then the code generates the following error...

Error on the Unique clustered index:
Server: Msg 1957, Level 16, State 1, Line 1
Index on view 'dbo.vwMyView' cannot be created because the view requires a conversion involving dates or variants.

The data type for the RowID field is...

rowid (int, Not Null)

Though it doesn't seem relevant, the data type for the field in the second index is...

MyField (varchar(5), Null)

Also, the data type for the date field in the WHERE clause is...

MyDateField (datetime, Null)

I tried chasing down a relationship (in the generic sense and not the database sense) between variant from the error message and varchar from the data definition in the second index statement, but I don't think that's what it is. I'm stumped. Do you have any ideas?

Thanks!
Ray <><

 

[hmckillop]

Problem is in View WHERE clause with

WHERE MyDateField >= '1/1/2000'
AND <my other conditions>

The '1/1/2000' is a varchar which is converted to datetime

if you use a deterministic function such as dateadd you can use

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
USE iCHOBS
GO
drop view dbo.vwMyView
go
CREATE VIEW dbo.vwMyView
     WITH SCHEMABINDING
     AS SELECT OID
     FROM dbo.PaymentType
     WHERE CreatedDate >= dateadd(d, 0,'1 jan 2000')
          --AND <my other conditions>
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX idx_OID
     ON dbo.vwMyView (OID)
--CREATE INDEX MyField ON dbo.vwMyView (MyField)
GO

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

sry that was my test code, yours should be

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
USE MyDataBase
GO
CREATE VIEW dbo.vwMyView
     WITH SCHEMABINDING
     AS SELECT <my field list>
     FROM dbo.MyTable
     WHERE MyDateField >= dateadd(d, 0, '1/1/2000') --converts this to datetime using a deterministic function (i.e. always has same output for given input).
          AND <my other conditions>
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE UNIQUE CLUSTERED INDEX RowID
     ON dbo.vwMyView (RowID)
CREATE INDEX MyField
     ON dbo.vwMyView (MyField)
GO

"I'm living so far beyond my income that we may almost be said to be living apart

 

[rheilman]

hmckillop,

Unfortunately, I have insufficient permissions on our server to build this view in the location of its source table. I have forwarded your timely suggestion on to those who do. It makes sense and looks like it should work. In addition to your star, I send my thanks for your quick solution to this problem as well as the principle that I learned from it!

Thanks!
Ray <><

 

[hmckillop]

you're welcome

"I'm living so far beyond my income that we may almost be said to be living apart