SQL Server Column Calculated by Function

Hi guys,
Today’s subject is about something very interesting on SQL Server, which is the creation of a calculated column using a function.
Yes, it is possible to create a column on a table based on a function and to do that we will follow these steps:

1. Creation of a table (with columns: id and name)
2. Insert data on this table
3. Creation of a SQL function (that will return “id – name”)
4. We will add a column using the created function

Attention! This is an example created to show you this functionality and the use of this example can impact your queries performance,
The use of this solution is your responsibility.

Let’s create the table with the following SQL command:

--drop TABLE dbo.TempSuzuki
go
CREATE TABLE dbo.TempSuzuki
(
	ID   INT IDENTITY(1,1),
	Name VARCHAR(50)
)

Let’s insert some data on this new table with the following SQL command:

INSERT INTO dbo.TempSuzuki VALUES ('Thiago Satoshi Suzuki')
INSERT INTO dbo.TempSuzuki VALUES ('João da Silva')

Let’s query the create table to see if our commands worked with the following command:

SELECT * FROM dbo.TempSuzuki

You should get a result like this:

Let’s create our function with the following SQL command:

--DROP FUNCTION dbo.TestTempSuzuki
GO
CREATE FUNCTION dbo.TestTempSuzuki(@id INT)
RETURNS VARCHAR(100)
AS  
/*
<SUMMARY CREATED="30/04/2015" CREATOR="SUZUKI" 
	Used to create the date column.
</SUMMARY>
		
<HISTORY>
</HISTORY>
*/
BEGIN
	DECLARE @result VARCHAR(100)
	
	SELECT 
		@result = CAST(ID AS VARCHAR) + ' - ' + Name
	FROM 
		dbo.TempSuzuki
	WHERE 
		ID = @id
	RETURN @result
END
GO

The following SQL command will show how to use the created SQL function on a query:

select *, dbo.TestTempSuzuki (id)
from dbo.TempSuzuki

The query’s result should be like this:

This is the “magic” SQL command that will add a column to our table and that will use the SQL function that we created before:

ALTER TABLE dbo.TempSuzuki ADD Composite AS dbo.TestTempSuzuki(id)

From now on when we query our table with the following command:

select * from dbo.TempSuzuki

We should have the following result:

Note that the Composite column is the SQL function’s result.

Thanks for reading.
Like and Share if you found it may be useful to someone you know!

Would you like to check the Portuguese version?
Clique on the Brazilian flag ->