Search code examples
sqlsql-serversql-server-2005full-text-indexing

Index defaults to Allow Nulls 'YES' when I convert columns to NVarchar on an indexed view

Some background: I'm on sql server 2005 (I know :( ...)

Briefly: I am concatenating three varchar columns and converting them to nvarchar as a column in a schema-bound, indexed view. All three columns are pkeys and not null. But when the conversion takes place the resulting column is set to allow nulls and then cannot take part in a full-text index. (If the concatenation is not converted to nvarchar then the column is implicitly created as not null.) So, how do I can convert AND concatenate three varchars to nvarchar so that the resulting, calculated column does not allow nulls?

Detail:

I created a schemabound view (Indexed View) so I could apply a fulltext index and improve search performance. All is well with the world except I ran into this bug with sql server 2005

So, as the article suggested I figured I would convert the Clustered index (that which my full text index is built on) to NVARCHAR from varchar.

Here is the code for the view before I convert the indexed column to nvarchar : 

CREATE VIEW [dbo].[IndexedPart] WITH SCHEMABINDING
AS
SELECT     (I.ItemCode + ILP.CurrencyCode + IU.UOM) as Id, I.ItemCode, I.Image1, I.Image2,       I.Image3, REPLACE(I.ShortDescription, '&','&') AS ShortDescription, I.ModelNum, REPLACE(I.ItemBrand,'&','&') AS ItemBrand, ILP.ListPrice, ILP.CurrencyCode, IU.UOM, IU.DefaultUOM, 
                  IU.MinimumPrice, IU.MinimumOrderQty, IU.MaximumOrderQty, IU.OrderIncrement, I.LongDescription, IU.QtyAvail, irc.ReplenishmentClass, 
                  ISNULL(rc.SortLast, 1) AS SortLast, ISNULL( I.DoNotSell, 'N') as DoNotSell, 
ISNULL( I.EffectiveStatus, 'I') as EffectiveStatus, (REPLACE(I.ItemCode + ' ' +     I.ShortDescription + ' ' + I.ItemBrand + ' ' + I.ModelNum,'&','&')) AS FullTextIndex
FROM         dbo.Item AS I INNER JOIN
                  dbo.ItemUOM AS IU ON I.ItemCode = IU.ItemCode INNER JOIN
                  dbo.ItemListPrice AS ILP ON I.ItemCode = ILP.ItemCode AND IU.UOM =     ILP.UOM INNER JOIN
                      dbo.ItemReplenishmentClass AS irc ON irc.ItemCode = I.ItemCode INNER     JOIN
                      dbo.ReplenishmentClass AS rc ON irc.ReplenishmentClass =     rc.ReplenishmentClass;

CREATE UNIQUE CLUSTERED INDEX [IX_ItemCode_CurrencyCode_UOM_Clustered] ON [dbo].    [IndexedPart] 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

For background the three columns I am using to create the Id on this view are all Primary Keys of other tables.

When I add a conversion from VARCHAR to NVARCHAR to the Id column on that indexed view, I can run the same script to create that index but suddenly SQL Server decides that the index will allow nulls. That means I can't create a full-text index on that view anymore.

Is there anyway I can either make this index come back as allow nulls 'NO' or to add an identity column to an indexed view to make it so I can avoid the bug outlined in the article?

Solution

  • What I have found is that this unfortunately isn't possible to do in a view. The workaround? Upgrade to SQL 2008 (my preference), Create a table that is populated via ssis/dts that has all of the columns of the view but with nvarchar datatype.

    No great solution but a solution nonetheless.