Creating index on a view to prevent multiple not null values - Indexed view?

[noelwatson]

I am looking to create a constraint on a table that allows multiple
nulls but all non-nulls must be unique.
I found the following script

http://www.windowsitpro.com/Files/09...Listing_01.txt

that works fine, but the following line

CREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)

appears to use indexed views. I have run this on a version of SQL
Standard edition and this line works fine. I was of the understanding
that you could only create indexed views on SQL Enterprise Edition?

[Simon Hayes]

You can create indexed views in any MSSQL edition, but the query
optimizer will ignore them unless you either have Enterprise Edition,
or you use the WITH (NOEXPAND) hint in your queries. See "Creating an
Indexed View" in Books Online.

Simon

[noelwatson]

Simon Hayes wrote:

You can create indexed views in any MSSQL edition, but the query
optimizer will ignore them unless you either have Enterprise Edition,
or you use the WITH (NOEXPAND) hint in your queries. See "Creating an
Indexed View" in Books Online.

Simon

Simon,
Thanks for this. I was confused as I'm sure I recall using SQL a few
Sp's ago that the "manage indexes" option wasn't available from the
standard edition enterprise manager when modifying a view. The BOL
states

"You can create indexed views only if you install Microsoft SQL Server
2000 Enterprise Edition or Microsoft SQL Server 2000 Developer
Edition."

However, I'm more than happy if it works

Cheers

Noel

[Simon Hayes]

I'd never noticed that, but I just checked and you're correct - if
you're using EM with a Standard Edition server the option isn't
available, however it is with Enterprise Edition. I didn't see that
quote in BOL, but is it possible you're using an older version? The
latest one is here:

http://www.microsoft.com/downloads/d...DisplayLang=en

Personally, I don't use EM much anyway - since all my DDL scripts are
under source control, a graphical tool isn't that useful, and I'd
rather not have EM hiding something from me. This article has more
information about using QA and EM:

http://www.aspfaq.com/show.asp?id=2455

Simon