Create filtered index not working in SQL Server 2012 Express

sql, sql-server, sql-server-2012-express

Solution

When you enter the filter expression into the dialog, you don't add the `WHERE` clause yourself. Just type:

eMail IS NOT NULL

Otherwise, if you lead with a `WHERE`, you will have two `WHERE` clauses (and don't add a semi-colon at the end, because it will interrupt the rest of the index creation statement). You can see this when you've added your columns, entered your filter condition, and then click on the Script button. Select new query window. You will see something like this:

USE [yourdb]
GO
SET ANSI_PADDING ON
GO
CREATE NONCLUSTERED INDEX [fix_Prices] ON [dbo].[Customers]
(
[eMail] ASC
)
WHERE WHERE eMail IS NOT NULL
------^^^^^ OOPS!

WITH (...index options...)
GO

Arguably, SSMS should be smart enough to strip out a redundant `WHERE` clause, or maybe even show you using IntelliSense what your script will look like and easily point out simple mistakes like this, but frankly they have much more important bugs than this to fix. (I mean, come on, in 2008 they announced deprecation of statements that aren't terminated with a semi-colon, and I've been trying to get everyone on board for a long time. Do you see a single semi-colon above? Nope.)

Problem

I'm trying to create a filtered non-clustered index. In the gui I right-click the `Indexes` folder of the table where I want the index. Add the column where the index should be applied, then click on the filter page to add this statement: `WHERE eMail IS NOT NULL`. Click OK and I'm presented with this error: Create failed for Index 'fix_Email'. (Microsoft.SqlServer.Smo) Incorrect syntax near the keyword 'WHERE'. Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (Microsoft SQL Server, Error: 156) This comes straight out of a book on SQL Server 2008 Administration. I installed SQL 2012 express to see what differences there would be between the two. All previous examples worked, creating clustered indexes, non-clustered, and covering index. Is this a problem with the book or SQL Server 2012 or maybe the Express edition? I tried placing a semi-colon as suggested by the error message at the end of my `WHERE` statment, but that didn't do anything. I was able to create the index using this code in a query window: ``` CREATE NONCLUSTERED INDEX fix_Prices ON dbo.Customers (eMail) WHERE eMail IS NOT NULL; ``` why does this work but not using the Management Studio gui?

Original source