Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having trouble creating a unique key for a full-text index on an indexed view. My definition is as follows:

    CREATE VIEW [dbo].[vw_inventory_metadata] WITH SCHEMABINDING AS
        SELECT  CAST(ibb.pid AS varchar(5)) + CAST(pkey AS varchar(6)) + CAST(shopid AS varchar(2)) + CAST(genreid AS varchar(2)) AS unkey, ibb.pid, 
                pkey, pupc, pname, ibd.description AS pdescription, price AS pprice, psale, psavings, 
                stockstate, readystate, ibd.ptype AS ptype_id, ist.refshort AS ptype_short, ist.refname AS ptype_display,
                ism.refname AS pmfg_short, ism.display AS pmfg_display, ise.display AS pexcl_display, cPhysical, cVirtual, pricesort, 
                created AS createdate, metaupd AS lasteditdate, release AS releasedate, 
                ibd.isSALEToStr AS pissale, ibd.isOLFTToStr AS oleftstate, ibd.isExcToStr AS pisexclusive,
                available, modifytime AS pmodified, ist.refparent AS ptypeparent_id, release, shopid, genreid   
        FROM    dbo.tbl_inventory_base ibb INNER JOIN
                dbo.tbl_inventory_extend ibo ON ibb.pid = ibo.productid INNER JOIN
                dbo.tbl_inventory_description ibd ON ibb.pid = ibd.pid INNER JOIN
                dbo.tbl_inventory_quantity ibq ON ibb.pid = ibq.pid  INNER JOIN
                dbo.tbl_inventory_meta_manufacturers ism ON ibd.pmnfg = ism.refid INNER JOIN
                dbo.tbl_inventory_meta_types ist ON ibd.ptype = ist.refid INNER JOIN
                dbo.tbl_inventory_meta_exclusives ise ON ibd.pexcl = ise.refid  INNER JOIN      
                dbo.tbl_inventory_metalink_shops isms ON ibb.pid = isms.productid INNER JOIN 
                dbo.tbl_inventory_metalink_genres ismg ON ibb.pid = ismg.productid
    GO

    CREATE UNIQUE CLUSTERED INDEX ix_inventory_meta_view ON dbo.vw_inventory_metadata(unkey)
    GO

    CREATE FULLTEXT CATALOG dxFT WITH ACCENT_SENSITIVITY = OFF AS DEFAULT AUTHORIZATION [dbo];
    GO

    CREATE FULLTEXT INDEX ON [dbo].[vw_inventory_metadata]
    ( 
        [pname], [pdescription], [pexcl_display],
        [pmfg_display], [ptype_display], [pmodel]
    ) 
    KEY INDEX ix_inventory_meta_view WITH CHANGE_TRACKING AUTO; 
    GO          

I am getting the usual cryptic error about not null columns, unique, 900 bytes, etc.

    'ix_inventory_meta_view' 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 on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.

I am not sure which is the culprit for not allowing me to create the FT index. If SS allowed me to create the unique index on the view, then my key is unique right? I looked at the index and it says that index is varchar(15), is this over 900 bytes?

How do I do a byte calculation for this purpose? All of the key components are being cast to their maximum length. "pid" will not grow beyond 4, and if it does then I can hire an expert, same with pkey, shopid, and genreid.

Thanks

share|improve this question
1  
Are all of the columns that make up the unkey column NOT NULL? If any of them allow NULLs, you'll need to handle that. – Jon Seigel Sep 20 '12 at 22:27

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.