Creating index on a view to prevent multiple not null values - Indexed view?
This is a discussion on Creating index on a view to prevent multiple not null values - Indexed view? within the SQL Server forums, part of the Microsoft SQL Server category; --> I am looking to create a constraint on a table that allows multiple nulls but all non-nulls must be ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-29-2008, 08:43 AM
| ||||
| ||||
 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?      |
|
02-29-2008, 08:43 AM
| |||
| |||
| Re: Creating index on a view to prevent multiple not null values - Indexed view? 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 |
|
02-29-2008, 08:43 AM
| |||
| |||
| Re: Creating index on a view to prevent multiple not null values - Indexed view? 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 |
|
02-29-2008, 08:43 AM
| ||||
| ||||
| Re: Creating index on a view to prevent multiple not null values - Indexed view? 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 |
Linear Mode
