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
– Jeroen Mostert, Commented Mar 13, 2019 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
– Jeroen Mostert, Commented Mar 13, 2019 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
– Jeroen Mostert, Commented Mar 13, 2019 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
– Reversed Engineer, Commented Mar 13, 2019 at 11:28

StepUp · Accepted Answer · 2019-03-13 11:13:16Z

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, 2019 at 11:13

StepUp

38.5k16 gold badges95 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Reversed Engineer Over a year ago

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)

StepUp Over a year ago

@DaveBoltman try to see this answer and adapt the function to your requirements to use instead of FORMAT: stackoverflow.com/questions/159554/…

Reversed Engineer Over a year ago

Thank you - will certainly take a look. Appreciated!

Collectives™ on Stack Overflow

Using FORMATMESSAGE in indexed view

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related