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
En esta página del sitio puede ver el video en línea Creating Computed Columns with User Defined Functions - SQL Server de Duración hora minuto segunda en buena calidad , que subió el usuario Zia A. Shaikh (Programming With Zia) 06 agosto 2018, comparta el enlace con amigos y conocidos, en youtube este video ya ha sido visto 5,532 veces y le gustó 29 a los espectadores. Disfruta viendo!
