Databases
StackOverflow2013
Postsdbo
POHow can I make a deterministic FullName scalar-valued function in SQL Server?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow can I make a deterministic FullName scalar-valued function in SQL Server?
    primarykey
    data
    text
    <p>I have the following scalar-valued function defined in a SQL-Server 2008 database:</p> <pre class="lang-sql prettyprint-override"><code>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) &gt; 0 BEGIN IF LEN(@result) &gt; 0 BEGIN SET @result += N' ' END SET @result += @firstName END IF LEN(@middleName) &gt; 0 BEGIN IF LEN(@result) &gt; 0 BEGIN SET @result += N' ' END SET @result += @middleName END IF LEN(@lastName) &gt; 0 BEGIN IF LEN(@result) &gt; 0 BEGIN SET @result += N' ' END SET @result += @lastName END IF LEN(@suffix) &gt; 0 BEGIN IF LEN(@result) &gt; 0 BEGIN SET @result += N', ' END SET @result += @suffix END RETURN @result END </code></pre> <p>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:</p> <pre><code>'Members' table - Unable to modify table. Computed column 'FullName' in table 'Tmp_Members' cannot be persisted because the column is non-deterministic. </code></pre> <p>I can create a non-persisted computed column no problem. Here's an abridged schema for my table:</p> <pre class="lang-sql prettyprint-override"><code>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] </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USBrian Oliver
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COCreate the function `with schemabinding`. Though persisting this looks like a waste of space to me.
      singulars
      1. POHow can I make a deterministic FullName scalar-valued function in SQL Server?
      1. USMartin Smith
    2. COMy 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?
      singulars
      1. POHow can I make a deterministic FullName scalar-valued function in SQL Server?
      1. USBrian Oliver
    3. COYou'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.
      singulars
      1. POHow can I make a deterministic FullName scalar-valued function in SQL Server?
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload