Results 1 to 2 of 2

Thread: Database Options

  1. #1
    Join Date
    Dec 2006
    Location
    Portland, Oregon
    Posts
    64

    Question Database Options

    This thread is related to the second article in the SqlCredit series: Part 2: Creating the Database, Tables, CRUD Procedures.

    There are a number of database options that are OFF by default but should be ON based on recommendations from the SQL Server 2000 Best Practices Analyzer. The CreateDatabase script sets all these options to ON:
    • ANSI_NULLS
    • ANSI_WARNINGS
    • ARITHABORT
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER

    I am very interested to find what people are using in production environments (either 2000 or 2005).

    Are you setting these options to ON? If not, why not? What other options do you change, and why?

    Rob
    Last edited by rgarrison; 02-23-2007 at 01:00 PM. Reason: Updated after article published.

  2. #2
    Join Date
    Feb 2007
    Posts
    2

    Be aware of user options

    You could write an entire article on this topic alone (yes Rob, take that as a suggestion). User options can be set at the server, database, and session level. These five user options must be ON to allow UPDATE, DELETE, or INSERT statements against tables with an index on a computed column or indexed views. If a user option is not explicitly set, it can be dependant on how a user has connected to the database (DbLib, ODBC, OLEDB, etc.). So a DML statement that works in Query Analyzer may not work correctly when run from a client connection.
    I definitely think it’s a good idea to set these options at the database (and maybe the server level) especially if you have indexes on computed columns or views - or if you run distributed queries.
    Also as a best practice, it is a good idea to avoid code that could behave differently with different user options such as:
    Don’t use: SELECT * FROM tableX WHERE Col1 = NULL
    Instead: SELECT * FROM tableX WHERE Col1 IS NULL

    Don’t use: SET @s1 = 'Some string' + @s2 --when @s2 could be null
    Instead: SET @s1 = 'Some string' + ISNULL(@s2,' ')

    Don’t use double quotes around strings or object names.
    Instead use single quotes around strings and brackets around object names.

    Do check for division by zero and arithmetic overflow errors (or handle the exception). Alternatively, you can explicitly unset ARITHABORT and ANSI_WARNINGS (in a stored proc) in cases where you want to avoid a statement-terminating error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •