469,576 Members | 1,699 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,576 developers. It's quick & easy.

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

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?

Jul 23 '05 #1
3 1659
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

Jul 23 '05 #2


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

Jul 23 '05 #3
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

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Indraneel Sheorey | last post: by
1 post views Thread by Robert Oschler | last post: by
6 posts views Thread by christopher.secord | last post: by
5 posts views Thread by Gerard M. Operana | last post: by
9 posts views Thread by WalterR | last post: by
3 posts views Thread by lauren quantrell | last post: by
7 posts views Thread by Cyberwolf | last post: by
6 posts views Thread by john | last post: by
2 posts views Thread by oswald.harry | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.