Database options

Do you remember that some options of a server can be changed using sp_configure command? If the option affects database, it should be applied using a similar sp_dboption. Logic of the function is quite alike the server sp_configure. It's general syntax is:

sp_dboption 'database_name','option name', <'true'|'false'>

That procedure must be executed from the master database.

Some of database options are:

• abort tran on log full - this option causes a transaction to be rolled back, if there is a risk of filling up the transaction log. When managing database logs dba may define actions that should be taken by Sybase engine on some levels of filling up transaction log. If the current transaction is breaching the last-chance treshold it will be rolled back when this option is used.
• allow nulls by default - when you create a new table and nothing was mentioned about null acceptance for the NULL value, than by default NULLs are not accepted (so database in background adds NOT NULL expression). When this option is enabled, than the behavior of database engine changes and columns in the table will by default accept nulls
• auto identity - enabling that option causes Sybase to create in each new table additional column named SYB_IDENTITY_COL (numeric 10 digits). That new column will be unique and could be used by the logic of application, however SELECT * statement will not display it
• dbo use only - this option causes that the database is available only for a database owner. The main purpose of this option is to have database in state when some maintenance can be done on the database, but no user will have access to the database in the meantime.
• ddl in tran - causes ddl operations be able to be performed in sope of a transaction, what is not enabled by default
• identity in non uniqe index - if the table has an identity column, that column will be automatically added to every index on the table. Note that this is valid also in case when the option auto identity has been enabled before table creation. See details....
• no chkpt on recovery - normally when server starts on each database a checkpoint is issued, what means - all completed and saved in transaction log transactions will be pushed into the database. Finally database will be online. However if the database was intentionally left in RESTORING state, than any checkpoint should be issued
• no free space acctg - skips the recalculation of free space under some conditions making recovery faster
• read only - as the name suggests no modification to the data will be possible
• select into/bukccopy/pllsort - causes that some operations (like select into, bcp, writetext, parallel sorting) will be minimally logged
• single user - only one user is allowed to use a database at a time. Similar as option 'dbo use only', this option will be used during maintnance procedures
• trunc log on chkpt - when this option is enabled, Sybase will try to truncate the log approximately once every minute (with exception when the log is very small). Truncation - means removing all completed and checkpointed to the database file transactions. Usually those transactions are removed first after an incremental backup has been taken. Enabling this option causes that incremental backups will not contain all the changes. This is usually not a problem on developer machines, but the option should probably not be used on production.
• unique auto_identity index - adds an IDENTITY column to each unique non clustered table created on a new table See details...
• disable alias access - disables accessing objects in another databases using aliased users, until the original user is explicit granted access to that object 

Let's examine the option 'allow nulls by default'

USE ALFA
GO

CREATE TABLE TestBefore
( ID INT IDENTITY,
  Name CHAR(20))
GO

sp_help TestBefore
GO

INSERT TestBefore(Name)VALUES('a')
INSERT TestBefore(Name)VALUES(NULL)
GO

The result of the sp_help indicates nulls are not allowed:

And the result of the second insert is an error "the column ... in the table ... does not allow null values":

So let's change the options

USE master 
GO

sp_dboption ALFA, 'allow nulls by default','true'
GO

USE ALFA
CREATE TABLE TestAfter
( ID INT IDENTITY,
  Name CHAR(20))
GO

sp_help TestAfter
GO

INSERT TestAfter(Name)VALUES('a')
INSERT TestAfter(Name)VALUES(NULL)
GO

SELECT * FROM TestAfter
GO

Here sp_help returns info about accepting the nulls:

As during the insert operation no error was met, the final SELECT statement returns two rows:

As you see - results differ, however the CREATE TABLE statement was the same. The option allow nulls by default has changed the behavior of the database. The conclusion could be: It is always better to explicitly determine if the column should accept nulls or not.