Unable To Create Indexable View On SQL 2000.

Hello,

I have a query that seems to take a while to execute and I'm looking into using an indexed view to see if this helps. I use the script below to create the view but when I query it's indexability using:

(select ObjectProperty(object_id('GetMessageQueueDetails'), 'IsIndexable'))

it always return '0'.

Here is a very cut down version of the view, only selects the uid! from a single table


IF OBJECT_ID ('GetMessageQueueDetails', 'view') IS NOT NULL

DROP VIEW GetMessageQueueDetails ;

GO



IF sessionproperty('ARITHABORT') = 0 SET ARITHABORT ON

IF sessionproperty('CONCAT_NULL_YIELDS_NULL') = 0 SET CONCAT_NULL_YIELDS_NULL ON

IF sessionproperty('QUOTED_IDENTIFIER') = 0 SET QUOTED_IDENTIFIER ON

IF sessionproperty('ANSI_NULLS') = 0 SET ANSI_NULLS ON

IF sessionproperty('ANSI_PADDING') = 0 SET ANSI_PADDING ON

IF sessionproperty('ANSI_WARNINGS') = 0 SET ANSI_WARNINGS ON

IF sessionproperty('NUMERIC_ROUNDABORT') = 1 SET NUMERIC_ROUNDABORT OFF

GO



CREATE VIEW GetMessageQueueDetails

WITH SCHEMABINDING

AS

SELECT Uid

FROM dbo.MyTable

GO

I see from See http://msdn.microsoft.com/en-us/library/aa933148(SQL.80).aspx that the pre-requsites for indexed views are pretty strict, I have been through this list and think I have everything covered, except for :

"The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view."

Is there an easy way to find out if ANSI_NULLS was ON or OFF when the table was created. If it was OFF can I do an ALTER TABLE to turn it on and will that make the view indexable? If so how do I do this with out trashing the data in the table?

Or am I doing something else wrong?

Can anybody offer any help?

Thanks