Creating Computed Columns with User Defined Functions - SQL Server
-- IN THIS VIDEO WE ARE GOING TO LEARN ABOUT ADDING A COMPUTED COLUMN BY USING A USER DEFINED FUNCTION WHICH IS 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
Nesta página do site você pode assistir ao vídeo on-line Creating Computed Columns with User Defined Functions - SQL Server duração hora minuto segundo em boa qualidade , que foi baixado pelo usuário Zia A. Shaikh (Programming With Zia) 06 Agosto 2018, compartilhe o link com seus amigos e conhecidos, no youtube este vídeo já foi visto 5,532 vezes e gostou 29 espectadores. Boa visualização!
