Computed Columns 2

Sometimes we are using some scalar function to generate computed column values

This values also not persisted until the function is marked as schema bound

Lets see example here

Create table test

(

Id int,

Name varchar(100) ,

qualification as dbo.Get_qualification(Name) )

CREATE FUNCTION dbo.Get_qualification (@str VARCHAR(100))

returns VARCHAR(100)

AS

  BEGIN

      DECLARE @str1 VARCHAR(100)

      IF Charindex (' ', @str, 0) > 0

        SET @str1 = LEFT(@str, Charindex(' ', @str, 0) - 1)

      ELSE

        SET @str1 = ''

      RETURN @str1

  END

Now lets insert value

SELECT *

FROM   test

INSERT INTO test

VALUES      (1,

             'Er Amish')

INSERT INTO test

VALUES      (2,

             'CA John')

INSERT INTO test

VALUES      (3,

             'Dr Rahul')

SELECT *

FROM   test

Now we will try to create index on qualification

first we will check either this columns is indexable?

SELECT Columnproperty(Object_id('test'), 'qualification', 'isindexable')

This column is not indexable.

Reason is that the function we are using is not schema bounded.

Now we have to create function with schema binding

DROP TABLE test

Alter  FUNCTION dbo.Get_qualification (@str VARCHAR(100))

returns VARCHAR(100)

with schemabinding

AS

  BEGIN

      DECLARE @str1 VARCHAR(100)

      IF Charindex (' ', @str, 0) > 0

        SET @str1 = LEFT(@str, Charindex(' ', @str, 0) - 1)

      ELSE

        SET @str1 = ''

      RETURN @str1

  END

Create table test

(

Id int,

Name varchar(100) ,

qualification as dbo.Get_qualification(Name) )

INSERT INTO test

VALUES      (1,

             'Er Amish')

INSERT INTO test

VALUES      (2,

             'CA John')

INSERT INTO test

VALUES      (3,

             'Dr Rahul')

SELECT *

FROM   test

SELECT Columnproperty(Object_id('test'), 'qualification', 'isindexable')

ALTER TABLE test

ALTER COLUMN qualification

ADD persisted