SQL Server Performance

is persisted property

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Nov 13, 2009.

  1. shankbond New Member

    Hi,
    I have a table with a computed column whose value depends upon a primary key with identity(1,1)
    The computed column is as:
    CREATE function [dbo].[function] (@id int)
    returns varchar(10)
    as
    begin
    DECLARE @return VARCHAR(10)
    SET @return= 'RQ' +convert(varchar(8), @id)
    RETURN @return
    end
    Now, I wanted to put an index on this column but the BOL says that it should be persisted value, inorder to make it persisted it should be deterministic first, but I think the above function is deterministic.
    but the management studio says that it should be deterministic
    Please help
  2. Adriaan New Member

    Why not make it a plain old non-computed column, and set the value using an insert trigger?
  3. shankbond New Member

    Hi Adriaan,
    I am working on a database used by busy site , so I think insert triggers will lock the table and hence cause performance issues, therefore it was a good option to use computed columns
  4. Adriaan New Member

    You do realize that the 'RQ' initials are completely redundant?
    Couple of options:
    -1- Create a view like ...
    SELECT 'RQ' + CAST(id_col) As [RQId], col1, col2 ....... FROM ...............
    ... and make sure that the client application reads from the view.
    -2- Let the client application worry about printing 'RQ' in front of the id value.

Share This Page