|
Home > Archive > Microsoft SQL Server forum > May 2005 > Creating index on a view to prevent multiple not null values - Indexed view?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Creating index on a view to prevent multiple not null values - Indexed view?
|
|
| noelwatson@bristol94.freeserve.co.uk 2005-05-31, 7:23 am |
| 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/0.../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 2005-05-31, 7:23 am |
| 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@bristol94.freeserve.co.uk 2005-05-31, 9:23 am |
|
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 2005-05-31, 9:23 am |
| 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/...&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
|
|
|
|
|