Computed column as a fulltext index with formatted datetime

Question

I have the following table:

[dbo].[TableName]
(
[created] datetime not null,
[modified] datetime null,
[computedValue] as 
(
    format([created], 'yyy-MM-dd MM/dd/yyy') +
    case when ([modified] is not null)
    then 
            (format([modified], 'yyyy-MM-dd MM/dd/yyyy')) 
    end
)

And the problem occurs when I try to alter fulltext index on this table.

ALTER FULLTEXT INDEX ON [dbo].[TableName] ADD ([computedValue] LANGUAGE 1033);

In response, I get the following error:

Computed column 'computedValue' cannot be used for full-text search because it is nondeterministic or imprecise nonpersisted computed column.

According to MSDN : https://msdn.microsoft.com/en-us/library/ms181984(v=sql.110).aspx

"All built-in string functions are deterministic." So I`m racking my brain why it says that this column is not deterministic.

This answer shows a lot of research on `FORMAT()` and its status as `deterministic vs. non-deterministic`: http://stackoverflow.com/a/37695342/5089204 — Shnugo, Apr 05 '17 at 09:13

score 1 · Accepted Answer · edited May 23 '17 at 11:46

Can you try this? (it worked for me on MSSQL 2008) (FORMAT() seems to be non deterministic (see also [link]Can I make SQL Server FORMAT deterministic?

    CREATE TABLE TableName
    (ID INT NOT NULL,
    [created] datetime not null,
    [modified] datetime null,
    [computedValue] as (
        CONVERT(varchar(10), [created], 121) +' '  + CONVERT(varchar(10), [created], 110)
        + CASE WHEN ([modified] IS NOT NULL)
        THEN CONVERT(varchar(10), [modified], 121) +' '  + CONVERT(varchar(10), [modified], 110) 
        END 
    ) 
    )
    ;
    ALTER TABLE TABLENAME ADD CONSTRAINT TABLENAME_PK PRIMARY KEY (ID);
   GO   
    CREATE FULLTEXT CATALOG CATALOG1;  
    GO  
    CREATE FULLTEXT INDEX ON [TableName]([computedValue]  LANGUAGE 1033 ) KEY INDEX TABLENAME_PK ON CATALOG1;

I was just about to answer my question with the same result. Yeah, that worked for me too :) — Lubudubu1010, Apr 05 '17 at 09:23

Computed column as a fulltext index with formatted datetime

1 Answers1