Filtered index error.

I am trying to create a Filtered Index...

I am getting an error reading

Incorrect syntax near the keyword 'WHERE'.

Here is my code:

CREATE NONCLUSTERED INDEX [IDX_Test] ON [Test].[Products]
(
      [ShopID] ASC,
      [ProductID] ASC,
      [OrderDate] ASC,
      [InProduction] ASC
)
INCLUDE (
[CustomerID],
[LanID],
[Brand],
[TotalSpent])
WHERE      InProduction = 0
Mr_ShawAsked:
Who is Participating?
 
khan_webguruCommented:
Hello Bro!


Here is the sample from MSDN

 
Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Create XML Index 
CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
        table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

Open in new window


Hope this will help you

Regards,

AAK
0
 
mayank_joshiCommented:
try without asc
0
 
Mr_ShawAuthor Commented:
Still does not work.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mr_ShawAuthor Commented:
How do I check if my server is 2008 or 2005?

0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

You can not put where condition in SQL SERVER 2005

WHERE      InProduction = 0


Your code is perfect but will work with SQL server 2008


- Bhavesh
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Run this.

SELECT @@VERSION
0
 
Mr_ShawAuthor Commented:
Is the filter index a feature in 2005?
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Filter index Feature available in SQL Server 2008
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Hi,
It seems you are using SQL Server 2005, you can check version properties, General of from Object Browser in SQL Studio.
check following http://msdn.microsoft.com/en-us/library/ms188783(v=SQL.90).aspx
it do not show where allowed in sysntax

or you can use following query to check

Print(@@version)

if it's version is 9.x then it is 2005 and if 10 then 2008
Thanks
0
 
Mr_ShawAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.