Besides the ability of store and retrieve information, most leading databases provide additional features to aid administrators and programmers in working with databases. The following are some of the more prominent additional features in databases.


Constraints

Mistakes commonly happen when data is inserted into a database table; or when existing data is modified. For example, errors can occur because of human errors in typing in the data.

A good database allow designers to specify column constraints so that the values that a column can accept are constrained to certain limitations. For example, state codes in America are uppercase 2-letter abbreviations; age cannot be a negative number; product code values need to be constrained to be looked up from a product table.


Cursors

When an SQL query has been successfully executed, the database engine returns a result set consisting 1 or more rows of data. Most leading databases provide cursors to aid programmers by providing a logical mechanism to access the results in such a result set. This logical mechanism is called a cursor.

Cursors are pointers the the current row within a returned result set. Cursors are scrollable in the sense that they can move forward and backwards within the returned result set as instructed by a program. The program typically issues an SQL query and then point the cursor to the start of the returned result set. The cursor can then be moved forward or backward by the program within the returned result set. The program can access the data in the row that the cursor points to.


Stored Procedures

Stored procedures are a collection of SQL statements that are optimized; ready to execute; and are stored in the database engine. With stored procedures, such a collection of SQL statements can be executed all at one go with a single command. The initiator simply initiates the execution command and waits for a returned result.

Without such a feature, SQL statements would need to be sent one by one by a program or a human through a client interface. Each SQL statement has to be sent individually to the database engine which will return a result for each individual SQL statement. The additional overhead as compared to using stored procedures is that the program or human will have to wait for and process each individual return before sending the next SQL statement to the database engine. Also, instead of being already optimized and ready to execute, each SQL statement that the database engine receives will have to be prepared and 'compiled' before it can be understood by the database engine.


Locking

Most databases cater for multi-user access through the use of locking mechanisms. A lock is a hold or a reserve that is placed on a row of data. This lock prevents other users from accessing the row until the lock is released.

Whenever a user tries to access or update a particular row of data that is currently being accessed or updated by another user, the 'late' user will have to wait until the first user has completed his task before the 'late' user can begin working on the row of data. This mechanism ensures the integrity of data in a database and prevents concurrency problems.

However, in some cases where users issue long and complicated queries to the database engine, this locking mechanism causes dead-locks which can potentially crash the database engine. Dead-lock occurs in situations when 2 or more users are waiting for resources that are being held up by each other. For example, user A has issued a complicated SQL statement that has locked rows 1 and 2. User A's query now needs to access row 3 before the entire transaction can be completed and all locks can be released. However, user B has also issued a complicated SQL statement that has locked rows 3 and 4. User B's query is now waiting to access row 2 which has been locked by user A's query. In such a situation, both user A's and user B's queries will continue waiting until an administrator kills both processes and releases the dead-lock. Dead-locks put a heavy drain on computing resources and can potentially bring down the database engine.