Persisted Computed column behavior when arithabort is off

If we have persisted computed columns and the arithabort is set to ON, SQL needs to re-compute the column despite having column marked as computed. The rather bigger issue is the query always scans index, in spite of having proper index.

Let’s have a look at the example

--Create test data
use tempdb
go
if object_id('dbo.testPersistedComputedColumn') is not null
drop table dbo.testPersistedComputedColumn
go
Create table dbo.testPersistedComputedColumn
(
c1 int not null identity(1,1) primary key
, c2 char(4000)
, c3 as substring(c2,1,2) persisted
)
go

insert into dbo.testPersistedComputedColumn(c2)
select top 200 name from sys.all_columns
go

if object_id('dbo.testNonPersistedComputedColumn') is not null
drop table dbo.testNonPersistedComputedColumn
go
create table dbo.testNonPersistedComputedColumn
(
c1 int not null identity(1,1) primary key
, c2 char(4000)
, c3 as substring(c2,1,2)
)
go

insert into dbo.testNonPersistedComputedColumn(c2)
select top 200 name from sys.all_columns
go

Now, If you run the below query, first will perform scan while the second will seek the clustered index.

use tempdb
go
/*
Also need to set ansi_warning to off, as on database having compatibility
greater than 80, setting ansi_warnings ON automatically sets arithabort ON
and SSMS default setting have both these ON.
*/
set ansi_warnings off
go
set arithabort off
go
select tpc.c2 , tpc.c3
from dbo.testPersistedComputedColumn  tpc
where tpc.c1 = 1
go
select tpc.c2 , tpc.c3
from dbo.testNonPersistedComputedColumn   tpc
where tpc.c1 = 1
go

Below is the snap of execution plan for both queries. In case of persisted column clustered index scan is performed and filter is used to filtering rows, while second perform the index seek. Also, even in case of persisted column, value of C3 is calculated at run time.

This will not be issue for most of environments as apart from one which are using DB library,  all other client libraries have ansi_warnings to ON as a default and most of databases now days have database which have compatibilities grater than 80.