[RESOLVED] SQL Server 2005: Create view with unique numeric column

[aconybeare]

Hi,

I need to set up full text searching on a table with a non-numeric composite primary key. When I try to do this I get an error saying it requires a unique index, despite the fact that the composite PK is unique and has an index on it

The idea is to create a view of this table with a new key field I've tried using the NewID() function but when I try to create the index get an error saying it contains text,ntext,xml etc.

Does anyone know how to create a view with a unique numeric key? Or any other ideas on tackling this little snag.

I guess an alternative is to create a new numerical primary key. This might be possible since referential integrity isn't enforced in the db, trick would be populating the existing table data.

Regards Allan

[brucevde]

From BOL
The KEY INDEX must be a unique, single-key, non-nullable column. Select the smallest unique key index for the full-text unique key. For best performance, a CLUSTERED index is recommended.

The column does not have to be the PK.

Add an Identity column to the table. SQL Server will populate the new column automatically.

[aconybeare]

Perfect thanks