Home » News » SQL Server Column Calculated by Function

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
CREATE TABLE dbo.TempSuzuki
	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:

SQL Function 1

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

--DROP FUNCTION dbo.TestTempSuzuki
CREATE FUNCTION dbo.TestTempSuzuki(@id INT)
	Used to create the date column.
	DECLARE @result VARCHAR(100)
		@result = CAST(ID AS VARCHAR) + ' - ' + Name
		ID = @id
	RETURN @result

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:

SQL Function 2

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:

SQL Function 3

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 -> Brazil

Deixe um comentário