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
In questa pagina del sito puoi guardare il video online Creating Computed Columns with User Defined Functions - SQL Server della durata di ore minuti seconda in buona qualità , che l'utente ha caricato Zia A. Shaikh (Programming With Zia) 06 agosto 2018, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 5,532 volte e gli è piaciuto 29 spettatori. Buona visione!
