Computed Column cannot be Persisted

I have a custom function, and I am trying to created a persisted column using this function.

It is giving me following error.

Computed column 'FormattedSSN' in table 'SomeTable' cannot be persisted because the column is non-deterministic.

Here is the function:

ALTER FUNCTION [dbo].[FormatSSN]() RETURNS VARCHAR(11) AS BEGIN return ''; END

Here is the query to add the column using the function:

ALTER TABLE SomeTable ADD FormattedSSN as dbo.FormatSSN() PERSISTED

Please suggest if there is any way out. Thanks.

-------------Problems Reply------------

Add WITH SCHEMABINDING to the function like this:

ALTER FUNCTION [dbo].[FormatSSN]
(
@SSN VARCHAR(9)
)
RETURNS CHAR(11)
WITH SCHEMABINDING
AS
BEGIN
your stuff here
END

and then run this to verify:

IF OBJECTPROPERTY (OBJECT_ID(N'[dbo].[FormatSSN]'),'IsDeterministic') = 1
PRINT 'Function is detrministic.'
ELSE IF OBJECTPROPERTY (OBJECT_ID(N'[dbo].[FormatSSN]'),'IsDeterministic') = 0
PRINT 'Function is NOT detrministic'
GO

Works here.

How about specifying the definition directly:

ALTER TABLE SomeTable
ADD FormattedSSN as
case when len(EmployeeSSN) = 9 then
substring(EmployeeSSN, 1, 3) + '-' +
substring(EmployeeSSN, 4, 2) + '-' +
substring(EmployeeSSN, 6, 4)
else EmployeeSSN end
PERSISTED

Instead of calling the UDF, Set the computed column expression to

Case When Len(EmployeeSSN) = 0 Then
SUBSTRING(EmployeeSSN, 1, 3) + '-' +
SUBSTRING(EmployeeSSN, 4, 2) + '-' +
SUBSTRING(EmployeeSSN, 6, 4)
Else EmployeeSSN End

In the Create Table script you can add a column:

[NewColumnName] As
(Case When len([UpdateUserId])=(0) T
Then (((substring([UpdateUserId],(1),(3))+'-')+
substring([UpdateUserId],(4),(2)))+'-')+
substring([UpdateUserId],(6),(4))
Else [UpdateUserId] End) PERSISTED,

Category:sql Views:1 Time:2009-11-13
Tags: sql sql server

Related post

  • How do I use a computed column specification to persist a datetime value using getdate()? 2011-08-29

    Does anyone know if there is a way to achieve the same affect as a datetime column with a default binding of getdate() with computed columns? I have tried setting the formula to getdate() and persist to Yes but I get an error Computed column 'InsertD

  • Type Conversion in Persisted Computed Column 2009-08-18

    I'm working with 2 related tables in a Microsoft SQL Server 2008 environment which are connected via a GUID. In one table, the field has the type varchar(50), the other one is properly types as uniqueidentifier. This is obviously bad but I can't chan

  • Alter SQL Function Referenced by Computed Column 2009-02-12

    If you set up a table's column to be a computed column whose Formula calls a Function, it becomes a pain to change that underlying Function. With every change, you have to find every single column whose Formula that references the Function, remove th

  • When are computed columns appropriate? 2010-12-02

    I'm considering designing a table with a computed column in Microsoft SQL Server 2008. It would be a simple calculation like (ISNULL(colA,(0)) + ISNULL(colB,(0))) - like a total. Our application uses Entity Framework 4. I'm not completely familiar wi

  • SQL Server 2005 Computed Column Is Persisted 2009-05-27

    I have some computed columns in a table and need to know if I should set Is Persisted to true. What are the advantages? Are there any disadvantages? What does 'Is Persisted' mean? --------------Solutions------------- "Persisted" means "stored physica

  • created persisted computed columns when the user defined scalar function appears to be non-deterministic 2009-09-16

    I have a scalar UDF that I know to be deterministic, however SQL doesn't. Is there a way to declare it as deterministic so that I can then use it in a persisted computed column definition? further clarification: The purpose of this exercise is that I

  • Marking persisted computed columns NOT NULL in SQL Server Management Studio 2010-01-12

    It is possible in SQL Server 2005 to create a computed column that is both persisted and is defined as NOT NULL (cannot contain a null value). The 2nd attribute is of importance when using libraries like Linq2Sql if we want to avoid a lot of manual w

  • Will Microsoft SQL Server efficiently handle a non-persisted computed column? 2010-08-13

    I'm having a hard time phrasing this question in such a way that doesn't turn up results for persisted, indexed computed columns. My question is, if I have a table such as: CREATE TABLE Customers ( ID int, Name nvarchar(50), Balance money, HasBalance

  • How to check if computed column is persisted? 2011-03-11

    How to check if computed column is persisted? (MS SQL Server) --------------Solutions------------- Computed column attributes are available in sys.computed_columns. select * from sys.computed_columns where is_persisted = 1 is_persisted = 1 for the co

  • Persisted computed column with subquery 2011-03-11

    I have something like this create function Answers_Index(@id int, @questionID int) returns int as begin return (select count([ID]) from [Answers] where [ID] < @id and [ID_Question] = @questionID) end go create table Answers ( [ID] int not null ide

  • Can't add index to persisted computed column because it is "of a type that is invalid for use as a key" 2012-01-31

    I have a table like this with a computed column: CREATE TABLE PhoneNumbers ( [PhoneNumberID] int identity(1,1) not null primary key clustered, [Number] varchar(20), /* Entire number, like (800) 555-5000 */ [Digits] AS dbo.RegExReplace(Number, '[^0-9]

  • Add and populate a column or add a persisted computed column? 2012-12-20

    I have a DB table with 10 million rows, 5GB of data, no primary key, but a clustered index. Every day 100s of thousands of records get added. I am looking to add a new column: ALTER TABLE dbo.CMS_hsplit ADD split_locIDacd AS CONVERT(varchar(8), split

  • SQL Server: One large persisted computed column for Fulltext Indexing 2010-05-26

    It appears to me as the easiest, most straightforward solution, but please correct me if I'm wrong. Instead of having a fulltext index on all individual columns of a table, isn't it better to just generate one single wide computed column and run the

  • Trying to convert "yy/mm/dd hh:mm:ss" (stored as char) into datetime using computed column 2009-11-28

    This seems like it should be simple but it's driving me up the wall. I have two columns - 'tx_date' and 'time' stored each as char(10). (bad database design I know, but wasn't my design) From a query I can convert them into a datetime just fine - "..

  • Computed column based on another computed column? 2009-12-04

    I have a computed column called Cost that returns money. I want to have another column that returns (Cost * 2), but it doesn't allow me. --------------Solutions------------- Computed columns cannot reference other computed columns. Though you ought t

  • Computed columns: SQL or Locally in Entity Framework? 2009-12-14

    I have some computed-columns of two types: Computed columns based on columns in current table (i.e. Price * Tax) Computed columns based on other columnes (i.e. Price * fn_GetTax(OrderId)) Would you say it's better to use these columns in CLR on the c

  • SQL Indexing - Computed Column vs Field Used by Computed Column 2009-12-16

    Quick question for the DBA's out there: Say I have 2 columns on my table, IsDeleted (bit) and DeletedDate (datetime). The table contains approx 10,000,000 rows. IsDeleted is a computed column that checks to see if DeletedDate is NULL; and it returns

  • How to make computed column not nullable? 2009-12-21

    So far I've been using ISNULL(dbo.fn_GetPrice(ItemId), 0) to make it not nullable (rather call it default-valued, but whatever). Is this the right way? --------------Solutions------------- Yes, that is the right way to do it. By using the isnull func

  • Check for arithmetic overflows in computed columns? 2009-12-21

    In our application we're going to be allowing users to type in arithmetic expressions (+ - * /) using other database columns for numbers, which would then be parsed by the application and written into the database as a computed column. However there

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.064 (s). 11 q(s)