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
unkeycolumnNOT NULL? If any of them allowNULLs, you'll need to handle that. – Jon Seigel Sep 20 '12 at 22:27