Persisting a Computed Datetime Column in SQL Server 2005

Persisting a computed datetime column in SQL Server 2005

What about:

CREATE FUNCTION [dbo].[fComputeValue] (@data XML)
RETURNS varchar(50)
WITH SCHEMABINDING
AS
BEGIN
  RETURN @data.value('(/Metadata/Value[@Key="StartDate"])[1]', 'varchar(50)')
END

and:

ALTER TABLE dbo.CustomMetadataTable ADD [StartDate] AS (convert(datetime,([dbo].[fComputeValue]([CustomMetadataColumn]), 127)) PERSISTED

or:

return convert(datetime, @data.value('(/Metadata/Value[@Key="StartDate"])[1]', 'varchar(50)'), 127)

From books online:

CONVERT is Deterministic unless one
of these conditions exists:

Source type is sql_variant.

Target type is sql_variant and its
source type is nondeterministic.

Source or target type is datetime or
smalldatetime, the other source or
target type is a character string, and
a nondeterministic style is specified.
To be deterministic, the style
parameter must be a constant.
Additionally, styles less than or
equal to 100 are nondeterministic,
except for styles 20 and 21. Styles
greater than 100 are deterministic,
except for styles 106, 107, 109 and
113.

It might help if you use CONVERT with style 127

SQL Server 2005 Computed Column Is Persisted

"Persisted" means "stored physically" in this context.

It means that the computed value is computed once on insert (and on updates) and stored on disc, so it does not have to be computed again on every select.

Persisted also causes a performance penalty on insert and updates, since the column must be computed, but will increase the performance on subsequent select queries.

So, it depends on your usage pattern, which approach to follow: if you update infrequently, but query a lot, you should set persisted = true.

If you update frequently, or if you do not care about retrieval performance, you should consider setting persisted = false

How do I use a computed column specification to persist a datetime value using getdate()?

forget the "computed column" and make it a regular not null column, with a default to GETDATE(), or use an INSTEAD OF UPDATE/INSERT trigger to set it.

you can't make a computed column use a function that constantly returns a different value (based on the same parameter values), it must return the same value each time (based on the same parameter values). Read this: Deterministic and Nondeterministic Functions

All functions are deterministic or nondeterministic:

• Deterministic functions always return the same result any time
  they are called with a specific set of input values.
• Nondeterministic functions may return different results each time
  they are called with a specific set of input values.

Whether a function is deterministic or nondeterministic is called the
determinism of the function.

For example, the DATEADD built-in function is deterministic because it
always returns the same result for any given set of argument values
for its three parameters. GETDATE is not deterministic because it is
always invoked with the same argument, yet the value it returns
changes each time it is executed.

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

As it turns out I needed to do was add WITH SCHEMABINDING to the UDF definition. the value method is determinisitic after all. (Apparently because the XQuery doesn't include dates?)

Datetime column cannot be pesisted

Alas, this is explained in the documentation:

CAST Deterministic unless used with datetime, smalldatetime, or sql_variant.

You may be able to parse the date and reconstruct the value using datefromparts() or datetimefromparts().

Computed Column with Current DateTime?

Try setting GETDATE() to be the DEFAULT value for the column.

Create a computed column on a datetime

Alter Table MyTableName 
  Add IsDeleted As 
     (Case When [DateTimeDeleted] Is Null 
       Then (0) Else (1) End)

This will output as an integer... If you really want it to be a bit, then:

Alter Table MyTableName 
  Add IsDeleted As 
     cast( (Case When [DateTimeDeleted] Is Null 
       Then (0) Else (1) End) as Bit)

How to get a Deterministic computed value for Date + Time

Firstly, the + operator is not valid for date and time values, strange as it may seem. So you need to use your second version.

Secondly, your time column is actually varchar, so the conversion is an implicit non-deterministic conversion.

You can make it deterministic by using CONVERT with a deterministic style parameter, such as 108 in this case:

ALTER TABLE dbo.[table] ADD created_time AS
  (DATEADD(millisecond,
      DATEDIFF(millisecond, 0, CONVERT(time, [time], 108)),
  [date])) PERSISTED;

This is documented here, some style parameters are not deterministic, because it can depend on culture and century (where year is two-digits).

I strongly suggest you either convert your time column into the time data type, or better yet, change it to a combined datetime column:

ALTER TABLE dbo.[table]
  ALTER COLUMN [time] time NOT NULL;

-- or
ALTER TABLE dbo.[table] ADD created_time datetime NULL;

UPDATE dbo.[table]
SET created_time = (DATEADD(millisecond,
      DATEDIFF(millisecond, 0, CONVERT(time, [time], 108)),
  [date]));

ALTER TABLE dbo.[table]
  DROP COLUMN [time]
  DROP COLUMN [date];

ALTER TABLE dbo.[table] ALTER created_time datetime NOT NULL;

Is there a workaround to allow using a computed column in its own formula

It seems like you need to keep track of not only what value1 is now but also what value1 used to be. You won't be able to do that with a computed column, because it can only react to the current value, not itself or the previous value.

I suggest an INSTEAD OF TRIGGER as opposed to a computed column. Here is a simple example:

USE tempdb;
GO

CREATE TABLE dbo.SparkyMark
(
    [key]    INT IDENTITY(1,1) PRIMARY KEY,
    [string] VARCHAR(32),
    Value1   INT,
    Value2   INT
);
GO

An INSTEAD OF INSERT TRIGGER:

CREATE TRIGGER dbo.SparkyMark_BeforeInsert
ON dbo.SparkyMark
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.SparkyMark([string], Value1, Value2)
        SELECT [string], Value1, Value1 FROM inserted;
END
GO

An INSTEAD OF UPDATE TRIGGER:

CREATE TRIGGER dbo.SparkyMark_BeforeUpdate
ON dbo.SparkyMark
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE sm
        SET [string] = i.[string],
            Value1 = i.Value1, 
            Value2 = CASE WHEN sm.Value2 < i.Value1 THEN i.Value1 ELSE sm.Value2 END
        FROM
            dbo.SparkyMark AS sm
        INNER JOIN
            inserted AS i
            ON sm.[key] = i.[key];
END
GO

Now let's insert a couple of rows and prove we can maintain Value2 without ever inserting or updating that column directly:

INSERT dbo.SparkyMark([string], Value1) SELECT 'foo', 3;
INSERT dbo.SparkyMark([string], Value1) SELECT 'foo', 5;

-- Value1 and Value2 are the same:
SELECT * FROM dbo.SparkyMark ORDER BY [key];

-- they will still be the same because the new Value1 > old Value2:
UPDATE dbo.SparkyMark SET Value1 = Value1 + 1;
SELECT * FROM dbo.SparkyMark ORDER BY [key];

-- now they will be one less because the new Value1 < old Value2:
UPDATE dbo.SparkyMark SET Value1 = Value1 - 1;
SELECT * FROM dbo.SparkyMark ORDER BY [key];

-- in row 1 Value1 drops by 2 but Value2 stays the same:
UPDATE dbo.SparkyMark SET Value1 = Value1 - 2 WHERE [key] = 1;
SELECT * FROM dbo.SparkyMark ORDER BY [key];

-- and finally we get both values in both rows equal again:
UPDATE dbo.SparkyMark SET Value1 = Value1 + 5;
SELECT * FROM dbo.SparkyMark ORDER BY [key];

Clean-up:

DROP TRIGGER dbo.SparkyMark_BeforeInsert, dbo.SparkyMark_BeforeUpdate;
DROP TABLE dbo.SparkyMark;
GO

---

Related Topics