Problems with Full-Text Index on Indexed View

Hello,

I'm running SQL Server 2005 and am attempting to creat a full-text index over three related tables. Unfortunately, I am finding myself unequal to the task and so I'm here asking for help.

My main stumbling block seems to be the indexed column used for the FTI key. For the sake of simplicity, let's say that I'm working with three tables A, B, and C, defined below:

A(

A_ID (PK, varchar(20), not null),

A_Description (varchar(1024), null)

B_ID(varchar(20), null)

C_ID(varchar(20), null)

)

B(

B_ID (PK, varchar(20), not null)

B_Description (varchar(1024), null)

)

C(

C_ID (PK, varchar(20), not null)

C_Description (varchar(1024), null)

)

I created a view (let's call it ABCView), combining these three tables with inner joins. Because I am interested only in distinct combinations, I first used a DISTINCT select statement, and then, because of errors I got in trying to index the view, switched to using GROUP BYs on all fields, with a Count_Big(*) put in basically because SQL Server told me to.

I tried to add an unique index over all three ID fields. However, if I understand things correctly, this doesn't work for full-text indexing because a full-text index must have only one indexed column as a key. In order to comply with this, I created a new column in my view called ViewID that was the result of concatenating the A, B, and C IDs and was filtered as "NOT IS NULL".

I was able to create a unique index on this field just fine and got through the entire full-text indexing process until I attempted to finish the full-text index creation. For the action "Create full-text index on ABCView" I got the error "Create full-text index failed()".

The additional information gave me the message: 'ViewID' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined by a non-deterministic or imprecise nonpersisted computed column, and has maximum size of 900 bytes. Choose another index for the full-text key. (Microsoft SQL Server, Error: 7653)

Several modifications and work arounds later, that is still where I find myself. So, I'm wondering if someone has any advice as to how I can full-text index the join of these three tables?

Thanks for your time,

Steve Erdman

Nevermind. I banged away at it for a bit and I figured it out.

For anyone else who runs into this problem, you need to wrap your index column in an IsNull call in the view definition. If you don't do that, SQL Server treats it as nullable, even if all the columns you append to create it are not nullable.

Cheers,

Steve Erdman