Filtered Index, Getting 'Created failed for Index"

Question

Post reply

Filtered Index, Getting 'Created failed for Index"

comic_rage

Mr or Mrs. 500

Points: 590
More actions
December 27, 2016 at 10:41 am

#324689

HI,
I am trying to added a filtered index. Here are my steps
1. right click to Indexes and selected Create new index
2. selected nonclustered
3. Added index name, Index type: Nonclustered
4. Added the column
3. Clicked on Filter and added this: isnull([IP_Column50],1) =1
I get the create failed for index and here are additional info
Any help is greatly appreciated. Thanks.
TITLE: Microsoft SQL Server Management Studio
------------------------------
Create failed for Index 'ixMyIndex_Column50'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Index&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Incorrect WHERE clause for filtered index 'ixMyIndex_Column50' on table 'dbo.MyTable'.
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: 10735)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5058&EvtSrc=MSSQLServer&EvtID=10735&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

no functions are allowed in a filtered index definition, so the ISNULL is not compliant with teh syntax.

also, OR statements are not valid either; the filter has to be rather simple.

so WHERE (ID IS NULL OR ID = 1) is also not valid, but there is still hope!

change your columns to not have a three state value:

make it not null, modify it with a default value of 1.

then you can have a nice filtered index like WHERE (IsValid = 1)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

comic_rage Mr or Mrs. 500 Points: 590 More actions · Answer 2

Hi,

Thanks for the input, is this correct approach,

In my VIEW, vMyTable

select col1,

isnull(column50,1) as column50,

column51

from dbo.MyTable

where Column50 = 1

In the MyTable indexes, I added the following script

create nonclustered index ixIndex_Column50 on [dbo].[MyTable] (Column50)

where ([Column50]=1)

Originally, isnull(column50,1)=1 was in the Where clause and it was super slow. I moved it to inside the select and added the filtered index. Hope this is correct to reduce the timing by a little.

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 3

that's a better choice. Putting functions in the WHERE clause means all rows need to be evaluated, which is a scan of the heap or index.

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

comic_rage (12/27/2016)
Hi,
Thanks for the input, is this correct approach,
In my VIEW, vMyTable
select col1,
isnull(column50,1) as column50,
column51
from dbo.MyTable
where Column50 = 1
In the MyTable indexes, I added the following script
create nonclustered index ixIndex_Column50 on [dbo].[MyTable] (Column50)
where ([Column50]=1)
Originally, isnull(column50,1)=1 was in the Where clause and it was super slow. I moved it to inside the select and added the filtered index. Hope this is correct to reduce the timing by a little.
Thanks

This would be fine provided you did what Lowell said to your "Column50" (paraphrasing): e.g. make it NOT NULLable and set the default to 1.

Alternatively you could accomplish what you need using an indexed view (which is the way I often deal with Filtered Index limitations).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001