Database Reference
In-Depth Information
SQL Server calculates the value of the calculated column when queries reference it. This can introduce some
performance impact in the case of complex calculations, especially when a calculated column is referenced in the
WHERE clause of a query. You can avoid this by making the calculated columns PERSISTED . In that case, SQL Server
persists the calculated values, storing them in data rows similar to regular columns. While this approach improves
the performance of queries that read data by removing on-the-fly calculations, it reduces the performance of data
modifications and increases the size of the rows.
User-defined functions allow the implementation of very complex calculations. However, they can significantly
reduce the performance of queries. Let's look at an example and create a table with 65,536 rows, as shown in Listing 4-20.
We will use this table as the source of the data.
Listing 4-20. Calculated columns and UDF: Creating a table with data
create table dbo.InputData
(
ID int not null
);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2 ) -- 65,536 rows
,Nums(Num) as (select row_number() over (order by (select null)) from N5)
insert into dbo.InputData(ID)
select Num from Nums;
For the next step, let's create two other tables with calculated columns. One of the tables persists calculated
column data while another table does not. The code to accomplish this is shown in Listing 4-21.
Listing 4-21. Calculated columns and UDF: Creating test tables
create function dbo.SameWithID(@ID int)
returns int
with schemabinding
as
begin
return @ID;
end
go
create table dbo.NonPersistedColumn
(
ID int not null,
NonPersistedColumn as (dbo.SameWithID(ID))
);
create table dbo.PersistedColumn
(
ID int not null,
PersistedColumn as (dbo.SameWithID(ID)) persisted
);
 
Search WWH ::




Custom Search