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



No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts