Did You Know? Things Keep Changing

Kalen Delaney — Thu, 19 Jun 2008 19:07:00 GMT

My Thursday commentary for the SQL Server Magazine Update e-newsletter discussed quiet changes in SQL Server 2005 and I just realized there's another one I wanted to mention.

If you use Indexed Views at all, you're probably aware that there is a set of SET options that must on set appropriately in order for your indexed views to work as planned. One of the requirements is that ARITHABORT must be ON. In SQL Server 2000, this value had to be set explicitly. (But frequently it was set by your connection, so you didn't have to worry about it.)

In SQL 2005, if you (or your connection) enabled the setting ANSI_WARNINGS, ARITHABORT is automatically enabled.

I dug around and found the reference to this change in the BOL at
http://msdn.microsoft.com/en-us/library/ms190306.aspx

What the article doesn't make completely clear is that even if you explicitly set ARITHABORT to OFF, as long as ANSI_WARNINGS is ON, SQL Server will behave as if ARITHABORT is ON.

The SET options are particularly important when updating a table on which an indexed view is built. If the SET options have the incorrect settings, the update will actually fail.

Let's look at an example on SQL Server 2005, using the old pubs database.

-- First, create the view and the index

USE pubs
GO
CREATE VIEW sum_sales WITH SCHEMABINDING
AS
SELECT type, sum(isnull(ytd_sales,0)) AS total_sales,
count_big(*) AS number_sales
FROM dbo.titles
GROUP BY type;
GO
CREATE UNIQUE CLUSTERED INDEX idxv_sales_by_type
ON sum_sales(type);
GO

-- Next, verify the settings

SELECT ansi_warnings, arithabort
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;

-- You should see that both options are ON (1).
-- Update the titles table, and it should succeed.
UPDATE dbo.titles
SET ytd_sales = ytd_sales + 1
WHERE title_id = 'BU1032';
--Now change ARITHABORT TO OFF, verify the settings, and update:
SET ARITHABORT OFF;
GO
SELECT ansi_warnings, arithabort
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO
UPDATE dbo.titles
SET ytd_sales = ytd_sales - 1
WHERE title_id = 'BU1032';
-- The above should succeed.
-- Now change ANSI_WARNINGS to OFF:

SET ANSI_WARNINGS OFF;
GO
SELECT ansi_warnings, arithabort
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO
UPDATE dbo.titles
SET ytd_sales = ytd_sales - 1
WHERE title_id = 'BU1032';

-- The above should fail.
-- If we change to SQL 2000 compatibility level,
-- just setting ARITHABORT OFF will cause the update to fail:
EXEC sp_dbcmptlevel pubs, 80;
GO
SET ANSI_WARNINGS ON;
GO
SET ARITHABORT OFF;
GO
SELECT ansi_warnings, arithabort
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID;
GO
UPDATE dbo.titles
SET ytd_sales = ytd_sales + 1
WHERE title_id = 'BU1032';

I'm sure there are lots more quiet changes. They're just so quiet I haven't found them yet!

Have fun

~Kalen

Kalen Delaney : compatibility level

Did You Know? Things Keep Changing