Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

Persisted Computed column behavior when arithabort is off

Nov 8 2012 12:00AM by Chintak Chhapia   

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.

 Computed Column and Arithabort

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.

Tags: SQL, Computed Column, Arithabort


Chintak Chhapia
40 · 5% · 1477
4
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Persisted Computed column behavior when arithabort is off" rated 5 out of 5 by 4 readers
Persisted Computed column behavior when arithabort is off , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]