Using FORMATMESSAGE in indexed view

Question

When creating an indexed view which uses the built-in function FORMATMESSAGE, I get an error (below), that FORMATMESSAGE is non-deterministic. Understandably, the view cannot be materialised, and this is presumably because the output of FORMATMESSAGE depends on localisation info, so doesn't always give the same output for the same parameters.

I've tried to find a way to pass a specific locale to FORMATMESSAGE to make it deterministic by using sys.messages instead of a literal format string, with no success.

However, the latest MS info about Deterministic and Nondeterministic Functions doesn't specifically say that FORMATMESSAGE is non-deteministic. Thus,

Has anyone found a way to use FORMATMESSAGE in an indexed view?

Here is my simplified test code:

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON
GO ----------------------------------------------------------------
CREATE TABLE dbo.Extensions(
    ID INT IDENTITY(1,1) NOT NULL,
    Name VARCHAR(50) NULL,
    ExtNo INTEGER NOT NULL,
  CONSTRAINT PK_Test PRIMARY KEY CLUSTERED (ID ASC)
)
GO ----------------------------------------------------------------
INSERT dbo.Extensions (Name, ExtNo)
VALUES ('New York', 55),
       ('San Francisco', 6),
       ('Paris', 4);
GO ----------------------------------------------------------------
CREATE VIEW dbo.vwExtensions
WITH SCHEMABINDING AS

  SELECT
    ID,
    FORMATMESSAGE ('%s Extension %03i', Name, ExtNo) AS ExtensionName
  FROM
    dbo.Extensions

GO ----------------------------------------------------------------

So far, so good. SELECT * FROM dbo.vwExtensions yields the correct results:

But the whole point of this is to make the view indexed (so it gets materialised, i.e. the results are calculated in advance and persisted in the view by SQL Server - Speed, man!). However,

CREATE UNIQUE CLUSTERED INDEX IX_Extension ON dbo.vwExtensions (ID)

gives an error:

Cannot create index on view 'TEST_DB.dbo.vwExtensions'. The function 'formatmessage' yields nondeterministic results. Use a deterministic system function, or ...

Note: I'm not looking for a computed column solution, for other reasons not important here. (That wouldn't allow the persisted attribute of the computed column to be set true in any case, for exactly the same reasons as above).

Edit: Should add here - I need to use FORMATMESSAGE (or some deterministic alternative), rather than CONCAT (RIGHT (... etc, since the format string is also stored in a table, rather than hard-coded, as indicated here. The simplified version of my code above does not indicate that.

The rules for which functions are deterministic are carved in stone. `FORMATMESSAGE` is never deterministic simply by virtue of not being on the list. What you want can be achieved by combining ordinary string manipulation functions (e.g. `RIGHT('000' + CONVERT(VARCHAR(20), ExtNo), 3)`), which is significantly more clumsy but hey, deterministic. Custom CLR scalar-valued functions can also be marked deterministic, but that seems overkill here. — Jeroen Mostert, Mar 13 '19 at 10:59
Incidentally, if SQL Server were to be extended to have deterministic string formatting, I would put in a vote for having `FORMAT` be counted as deterministic if (and only if) the `culture` parameter is explicitly supplied. That sounds like the sort of thing that could be useful (and is more general than `FORMATMESSAGE`). I don't know if the .NET runtime can be relied on to be deterministic in this regard, though (and not defer to the OS for some choices, which would possibly make things nondeterministic again). — Jeroen Mostert, Mar 13 '19 at 11:07
If "the format string is stored in a table" then I take back my earlier comment: a custom CLR function is probably *not* overkill. Arbitrary formatting isn't deterministic, and if you want a function that's guaranteed to be so you'll probably need to write it yourself. (That function could legally, and sneakily, call `FORMATMESSAGE` under the covers, as much as that's asking for trouble.) You could, conceivably, write a function capable of parsing and formatting such strings in pure T-SQL, but it'd be an exercise in suffering, even if you limited the valid formatting options. — Jeroen Mostert, Mar 13 '19 at 11:22
@JeroenMostert Thank you for your comments, especially about "which functions are deterministic are carved in stone". Will have to then use the simpler `RIGHT (... CONVERT ( ...` for now. Writing a custom CLR function is too complicated for the time being for my project, so will have to go for the simpler, less flexible option. — Reversed Engineer, Mar 13 '19 at 11:28

score 1 · Answer 1 · answered Mar 13 '19 at 11:13

1

Try to use deterministic function:

CREATE VIEW dbo.vwExtensions
WITH SCHEMABINDING AS

  SELECT
    ID,
    CONCAT(Name, ' Extension', ' '
        , RIGHT('000' + CAST(ExtNo AS VARCHAR(3)), 3)) AS ExtensionName
  FROM
    dbo.Extensions

and then create your index:

CREATE UNIQUE CLUSTERED INDEX IX_Extension ON dbo.vwExtensions (ID)

answered Mar 13 '19 at 11:13

StepUp

36,391
15
88
148

Useful, answer, thank you. I'll probably end up doing this. In my actual situation, the format string is not hard-coded, but comes from another table (I edited my question) – Reversed Engineer Mar 13 '19 at 11:19
@DaveBoltman try to see this answer and adapt the function to your requirements to use instead of `FORMAT`: https://stackoverflow.com/questions/159554/string-format-like-functionality-in-t-sql – StepUp Mar 13 '19 at 11:34
Thank you - will certainly take a look. Appreciated! – Reversed Engineer Mar 13 '19 at 12:39

Using FORMATMESSAGE in indexed view

1 Answers1