Database Options - Best-of-Breed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Database Options - Best-of-Breed

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-10-25 : 07:44:20

I've just restored a client database here and discovered that they had AutoClose set ... so I thought I'd set up a set of Best-of-Breed settings which I can use after a RESTORE.

Could anyone pass comment on any of these that they disagree with please


ALTER DATABASE MyDatabase
SET
	-- State Options
	READ_WRITE,
	MULTI_USER,
	
	-- Cursor Options
	CURSOR_CLOSE_ON_COMMIT OFF,
	CURSOR_DEFAULT GLOBAL,
	
	-- Auto Options
	AUTO_CLOSE OFF,
	AUTO_CREATE_STATISTICS ON,
	AUTO_UPDATE_STATISTICS ON,
	AUTO_SHRINK OFF,
	
	-- SQL Options
	ANSI_NULL_DEFAULT OFF,
	ANSI_NULLS OFF,
	ANSI_PADDING ON,
	ANSI_WARNINGS OFF,
	ARITHABORT ON,
	CONCAT_NULL_YIELDS_NULL OFF,
	NUMERIC_ROUNDABORT OFF,
	QUOTED_IDENTIFIER ON,
	RECURSIVE_TRIGGERS OFF,
	
	-- Recovery Option
	RECOVERY FULL,
	TORN_PAGE_DETECTION ON

Thanks

Kristen

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-10-29 : 14:33:20

Those settings look pretty good to me. But just for the record, some of them would need to be changed if you are creating or manipulating indexes on computed columns or indexed views. So far I'm not using those, so it doesn't yet affect me, but just something to keep in mind.

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Kristen
Test

22859 Posts

Posted - 2004-10-29 : 19:07:57

Thanks Mark.

Unfortunately we have some Indexed Views and Computed Columns, so I've come across that. They are such a PITA that we are working to remove them completely - seemed like a good idea at the time, but the subsequent hassle means we wish we hadn't used them!

So ... we've got a computed column for PRICE which is the Special Offer price if there is one, otherwise the regular price.

Now we have a VIEW to this table from another database. But we can't INSERT into this view - 'coz of the computed column (even if we specifically name just the columns we want to insert). So now we have to have an INSTEAD OF trigger on the view. Blarrgh!

And we can't use INSERT INTO MyTable SELECT * FROM OtherTable - have to name the columns and leave the Computed column off (yeah, we don't do this sort of sttuff in production code, but for a "copy data to TEMP database for working on" doing a SELECT * makes sure that we don't miss newly-added-columns next time we run the script)

And then all of our stuff that mechanically generates code needed to be made aware that computed columns had to be exclued from INSERT column lists and so on.

Also, we have a VIEW on

SELECT OtherReference FROM MyTable WHERE OtherReference IS NOT NULL

and we have a unique index on this. The index is not used in queries on the table (client not using Enterprise Edition), and we keep falling over ARITHABORT ON - and I don't understand why.

The SProcs all have:

CREATE PROCEDURE MyProc
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ARITHABORT ON
... do stuff ...

and still SQL complains that ARITHABORT is not on. So we have to include it in the calls from the Client app - or user a wrapper SProc (same SETs as above) which then just does an EXEC on the actual SProc, and that works fine.

Its a ball-of-chalk if you ask me, and if I had my time over I'd avoid them both!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources