Create filtered index not working in SQL Server 2012 Express

Question

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?

that is the exact code and method using the management studio, I am making an update to this, I was able to create it using straight sql statment. — Craig Smith, Mar 23 '13 at 14:12
Hey @marc_s, why did you change the error message to italics? I find it harder to read in that format, not easier. I assume you find the opposite? — Aaron Bertrand, Mar 23 '13 at 15:33
@AaronBertrand: that's just the style I saw a lot of moderators use a while back and I adopted it. Doesn't have to be that way - I just wanted to make sure the error clearly stands out from the rest of the question — marc_s, Mar 23 '13 at 16:26
@marc_s ah ok, you didn't have to change it back, was just curious if you actually found that easier to read. As for standing out, on mobile it's still on a white background, but on desktop it is colored like code but without fixed width font. — Aaron Bertrand, Mar 23 '13 at 16:37

score 5 · Accepted Answer · edited Jul 29 '20 at 18:23

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.)

cool, error in the book. I looked at the index that I created using sql in the gui and saw that ([eMail] IS NOT NULL) is in the filter page. Thanks — Craig Smith, Mar 23 '13 at 14:36

Create filtered index not working in SQL Server 2012 Express

1 Answers1