Creating Computed Columns With User Defined Functions
Aug 10 2018

Zia Ahmed Shaikh

In this video, we are going to learn about adding a computed column by using a user-defined function, also known as Custom Function. --Create a function (NON DETERMINISTIC) CREATE FUNCTION [dbo].[MyFunc] (@val int) RETURNS int AS BEGIN RETURN @val*2; END --Test the function select dbo.myFunc (200); -- should give output: 400-- Add a computed column using ALTER TABLE query ALTER TABLE jobs ADD double_salary AS dbo.myFunc(salary) Go--Get data from table SELECT * FROM jobs--Find if User Defined function is Deterministric SELECT id, Name, OBJECTPROPERTY(id, 'IsDeterministic') AS isDeterministic FROM dbo.SysObjects WHERE XType = 'FN' ORDER BY OBJECTPROPERTY(id, 'IsDeterministic') DESC, Name--Create a deterministic function WITH SCHEMABINDING CREATE FUNCTION [dbo].[MyDetFunc] (@val int) RETURNS int WITH SCHEMABINDING AS BEGIN RETURN @val*2; END -- Add a computed column with PERSISTED option ALTER TABLE jobs ADD double_salary2 AS dbo.myDetFunc(salary) persisted Go-- Again Get data from table SELECT * FROM jobs