0

I have the following scalar-valued function defined in a SQL-Server 2008 database:

CREATE FUNCTION FullName 
(
    @title nvarchar(50),
    @firstName nvarchar(50),
    @middleName nvarchar(50),
    @lastName nvarchar(50),
    @suffix nvarchar(50)
)
RETURNS nvarchar(300)
AS
BEGIN
    DECLARE @result nvarchar(300) = @title

    IF LEN(@firstName) > 0
    BEGIN
        IF LEN(@result) > 0
        BEGIN
            SET @result += N' '
        END
        SET @result += @firstName
    END

    IF LEN(@middleName) > 0
    BEGIN
        IF LEN(@result) > 0
        BEGIN
            SET @result += N' '
        END
        SET @result += @middleName
    END

    IF LEN(@lastName) > 0
    BEGIN
        IF LEN(@result) > 0
        BEGIN
            SET @result += N' '
        END
        SET @result += @lastName
    END

    IF LEN(@suffix) > 0
    BEGIN
        IF LEN(@result) > 0
        BEGIN
            SET @result += N', '
        END
        SET @result += @suffix
    END

    RETURN @result

END

I want to create a persisted computed column that is based on this function, but when I try to save the computed column, I get this error:

'Members' table
- Unable to modify table.  
Computed column 'FullName' in table 'Tmp_Members' cannot be persisted because the column is non-deterministic.

I can create a non-persisted computed column no problem. Here's an abridged schema for my table:

CREATE TABLE [dbo].[Members](
    [Id] [int] NOT NULL,
    [Title] [nvarchar](50) NULL,
    [FirstName] [nvarchar](50) NULL,
    [MiddleName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Suffix] [nvarchar](50) NULL,
    [FullName]  AS ([dbo].[FullName]([Title],[FirstName],[MiddleName],[LastName],[Suffix])),
 CONSTRAINT [PK_Members] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Brian Oliver
  • 1,407
  • 2
  • 15
  • 25
  • Create the function `with schemabinding`. Though persisting this looks like a waste of space to me. – Martin Smith Dec 18 '13 at 21:17
  • My understanding is that persisting it would definitely take up more space but would also result in faster queries, since the value wouldn't need to be recomputed each time it's needed. Is that not the case? Or how do you balance the two? – Brian Oliver Dec 18 '13 at 21:19
  • You're making the complete opposite trade off from the compression available in Enterprise Edition. That works on the basis that it is better to reduce IO at the expense of CPU cycles decompressing it. That's my default instinct as well. I suppose if your entire DB fits into cache and you are already CPU bound there may be a case for persisting this but it is hardly an expensive operation. – Martin Smith Dec 18 '13 at 21:24
  • You could consider using a view instead of a computed column. – Gordon Linoff Dec 18 '13 at 21:26
  • @MartinSmith . . . Do you know why `with schemabinding` is necessary in this case, where there are no references to other tables, views, or user defined functions? Is it because it is possible to write custom versions of `len()` or is it just a default in SQL Server that user defined functions are non-deterministic? – Gordon Linoff Dec 18 '13 at 21:37
  • @GordonLinoff It's [kind of explained here](http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx) although not sure how convincing I find it. In principle it *could* determine this easily at `CREATE` / `ALTER` time for UDFs that don't access other objects. But I suppose maybe it would be confusing to have it get determined automatically in some cases but not others. – Martin Smith Dec 18 '13 at 21:41

0 Answers0