Data integrity

Within a database, there are three different levels of integrity – domain, entity, and referential.

• Domain integrity
  Domain integrity ensures that the values for a specific column are valid and meet the relevant business rules. This is enforced through CHECK constraints, DEFAULT values, and FOREIGN KEY constraints.

• Entity integrity
  Entity integrity ensures that every row within a table is unique. This integrity is enforced through UNIQUE constraints and PRIMARY KEYS.

• Referential integrity
  Referential integrity ensures that relationships between tables are maintained. It can be enforced through FOREIGN KEYS as well as cascading updates. This is the only type of integrity that is enforced by stored procedures.

Note

When a deletion or modification is performed on the parent table, SQL Server also deletes or modifies all children of any table referenced by a foreign key. This is known as cascading updates, and is set up at table creation.

We will examine data integrity in Chapter 7, when we look at triggers, which are ideal to ensure referential integrity.

Database Integrity Checks

Even SQL Server can suffer from an odd glitch, whereby the database or the data in a table may get corrupted. For example, there may be a fault with the network card or the network router, which may result in generation of packets that corrupt the database. This is rare but it can happen through no fault of the SQL Server, but we still need to be aware of it, and have checks in place for recovering quickly from such a scenario.

Every night on our production server, once the overnight processing and backups have been done, special stored procedures execute T-SQL commands to check if everything is in order. These are mainly DBCC (Database Console Commands) statements, which run when system usage is low (as they can be quite CPU and process-intensive).

The system procedures, which run these commands, are usually placed in jobs. If an error occurs, these procedures will fail, and hence the job fails too. It will then produce a page or e-mail, so that DBAs can check and fix the error manually. These DBCC commands are usually run to list errors, but it is also possible to set them up to automatically correct the errors. This needs extreme care, however.

We can also expand this by building stored procedures to be executed when SQL Server starts up, known as recycled procedures, which can be used for checking the integrity of the database. For example, we can set up auto reboot when the server gives the ‘blue screen of death’, so that when the server reboots, SQL Server also reboots, and is available for use again. By building a stored procedure that checks the tables and data, or maybe even automatically fixes problems found, so that we are back to a stable scenario after the crash.

If you meet the ‘blue screen of death’, it is advisable to carry lots of checks on the data as well as the transaction log. Many corporations include their overnight checks as part of the startup check. Thus, you can really gain by having stored procedures that run when the SQL Server automatically starts up.

By executing the following system stored procedure, it's possible to check if this option is set for the database:

     sp_configure 'scan for startup procs' 

If you receive the following message, the configuration option 'scan for startup procs' does not exist:

Server: Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

Then you need to run the following. This message indicates that advanced options are not set to be displayed, and the advanced option setting is:

     EXEC sp_configure 'show advanced option',1     GO     RECONFIGURE 

Valid configuration options are:

The first column is the name of the option, the next two are the minimum and maximum values that the option can have, config_value is what the option is currently set to by sp_configure, whereas run_value is the setting when the option is actually used by SQL Server.

If a value of 1 is returned, then you can create and set up procedures to run automatically. To set this option you will execute:

     sp_configure 'scan for startup procs',1 

If we wanted a stored procedure to run when SQL Server starts up, we can do this through the sp_procoption system stored procedure. I won't give a specific example, as stored procedures designed to run at startup need a great deal of consideration. However, once you have your procedure, the syntax is demonstrated here:

     sp_procoption @ProcName = 'ut_startsproc', @OptionName = 'startup',     @OptionValue = 'true' 

Note

Note the case on the parameters. On most installations this is not vital, but if you use a collation that is case sensitive you will need to use the above case.

Important

If SQL Server refuses to start and the problem can be an auto start procedure, then you can start SQL Server from a DOS prompt with minimal configuration to allow you to remove any problem, by using the -f option as shown below

     >sqlservr -c -f 

Note

Stored procedures are certainly not replacements for functionality that already exists within the SQL Server, such as CHECK constraints. Procedures, such as triggers, can supplement these. If the CHECK constraint is more complex, it can be achieved with the CHECK constraint code. Stored procedures are also not meant for processes that run only once. The only exception to this rule will be if the procedure were a part of the setup process for building a new database or something similar.