Filetred index

  • \hello Guys,

     

    Mayyou help wuth the corect index filetred creation using modulo?

     

    The query need to  assist is  select col1,col2,.........  WHERE ISRECNUM % 20 = 1  and i wish to create a filtered index on ISRECNUM column which is INT     like  WHERE ISRECNUM % 20 = 1    .

    Thanks,

    Best Regards,

    Hadrian

     

  • I do not think that this is possible. If you check the documentation for filtered indexes (link), you will find the following limitation:

    Filtered indexes are defined on one table and only support simple comparison operators. If you need a filter expression that references multiple tables or has complex logic, you should create a view. Filtered indexes do not support LIKE operators.

    But, as implied in the above, an indexed view might give you what you want.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Maybe you could create a calculated column and put the filter on that.

    https://stackoverflow.com/questions/6733264/schema-binding-with-computed-column-function

  • Jonathan AC Roberts wrote:

    Maybe you could create a calculated column and put the filter on that.

    This is a far better idea than mine!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • FYI: Brent wrote a nice 101 about Filtered Indexes "What You Can (and Can’t) Do With Filtered Indexes"

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • This was removed by the editor as SPAM

  • This was removed by the editor as SPAM

Viewing 7 posts - 1 through 7 (of 7 total)

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