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

Did You Know? Nobody upgraded the SET OPTIONS screen in SQL Server Management Studio

Kalen Delaney — Tue, 17 Jul 2007 22:16:00 GMT

In SQL Server Management Studio, the screen where you choose which SET options to enable for all connections appears to be left over from SQL Server 2000. I know there are a lot of different dialog boxes available through the Tools | Options menu, but I find it amazing that this one was completely overlooked when updating the old Enterprise Manager GUI.

Here's how to get to the dialog:

Tools | Options

Then expand Query Execution to SQL Server and then to Advanced...

The list of the set options to enable does not include anything new in SQL Server 2005.

1. It does not include SET SHOWPLAN_XML or SET STATISTICS XML (but includes SHOWPLAN_TEXT)

2. Transaction Isolation Level does not include SNAPSHOT

3. Deadlock Priority only allows NORMAL and LOW, not the numbers -10 to 10, or the value HIGH
(see my earlier post: http://sqlblog.com/blogs/kalen_delaney/archive/2006/09/09/192.aspx )

Maybe there are others that I haven't noticed yet.

So now you know...

~Kalen

Kalen Delaney : SET Options

Did You Know? Things Keep Changing

Did You Know? Nobody upgraded the SET OPTIONS screen in SQL Server Management Studio