Post reply

CONTAINS predicate in Table with Composite Primary key.

  • May 16, 2017 at 6:57 am

    #323547

    Is there any way to use CONTAINS predicate in table which has got a Composite primary. Could you please help me with some options.
    I want to implement some search functionality...

  • May 16, 2017 at 12:43 pm

    #1942765

    sudheera - Tuesday, May 16, 2017 6:57 AM

    Is there any way to use CONTAINS predicate in table which has got a Composite primary. Could you please help me with some options.
    I want to implement some search functionality...

    Some options would be -

    Any unique index on a non-nullable single column will work - it's doesn't have to be the primary key.
    If the table doesn't have such and doesn't have an identity column, you could add an identity column and use that.
    You can also create a full text index on an indexed view.

    Sue

  • May 19, 2017 at 4:58 am

    #1943252

    Sue_H - Tuesday, May 16, 2017 12:43 PM

    sudheera - Tuesday, May 16, 2017 6:57 AM

    Is there any way to use CONTAINS predicate in table which has got a Composite primary. Could you please help me with some options.
    I want to implement some search functionality...

    Some options would be -

    Any unique index on a non-nullable single column will work - it's doesn't have to be the primary key.
    If the table doesn't have such and doesn't have an identity column, you could add an identity column and use that.
    You can also create a full text index on an indexed view.

    Sue

    Thank you. That helped, I have added a new column to the table.  If I use the same set of columns in view, again it will not allow for full text- index.  I tried to add identity column using 'ROW_NUMBER()

    ' in view. But it will not allow unique index because of aggregate function. So I found the best option is adding a unique index on a non-nullable single column.

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

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