• Login
• Become a member
• RSS
• Part of the TechTarget network

The proposed database architecture suggests several standard routines for each table: six standard stored procedures, one standard user-defined function and two standard triggers. Such routines can be generated from the database schema rather than be coded manually. Generated routines should be flexible for developers to use, but they must be carefully designed to minimize the occurrences of table scans (a common reason for performance issues) and to maximize the benefit of cached execution plans. Dynamic T-SQL is sometimes the best way to balance these needs.

The use of dynamic T-SQL has some security implications. Stored procedures are executed in the security context of the stored procedure owner and the ability to invoke stored procedures can be controlled for individual database users. However, dynamic T-SQL within a stored procedure is executed in the security context of the database user that invoked the stored procedure. Therefore, the database user must be given sufficient direct access to tables referenced by the code. The risk associated with such access can be substantially reduced with careful attention to security issues in the architecture of an application. In addition, dynamic T-SQL could be used maliciously through SQL injection, but that's not much of an issue with these standard routines because the parameter values would not come from user input.

The standard objects are commonly known as CRUD routines. CRUD is an acronym for Create, Read (Retrieve), Update and Delete. CRUD routines are used for basic entry, retrieval and maintenance of rows in tables.

The nine standard objects are:

I will start with a brief explanation of the naming convention used for these objects. Each name begins with a three-letter prefix to indicate the object type. Notice that without the prefix the names are not unique and unique names are required by SQL Server. The next two characters (positions 4 and 5) are a two-letter label (GR) to indicate a generated routine. The next two characters (positions 6 and 7) are a two-letter label to indicate the purpose for the routine, with C/K meaning Common/Key and I/U/D/S meaning INSERT/UPDATE/DELETE/SELECT. The remaining characters are a table name.

The trgGRCITableName trigger fires with an insert and sets the CreateDate column to the current date and time.

The trgGRCUTableName trigger fires with an update and sets the ModifyDate column to the current date and time.

The uspGRCITableName routine performs an INSERT of one row. It accepts values for each column (except for the primary key) and it returns the system-generated primary key (IDENTITY value) as an OUTPUT parameter. It can automatically assign values to the Create and Modify standard columns.

The uspGRCUTableName routine performs an UPDATE of one row. It accepts a primary key value and values for each of the other columns. If the DetectWork standard column is present, the new value is compared with the existing value. The existing value must be less than the new value or the row is not affected. The application can check the number of rows affected and react accordingly. This behavior supports the earlier discussion of concurrent access. It can automatically assign a value to the Modify standard column.

The uspGRCDTableName routine performs a DELETE of one row. It accepts (expects) a primary key value.

The uspGRCSTableName routine performs a SELECT of one row. It accepts (expects) a primary key value and it returns all columns.

The uspGRKDTableName routine performs a DELETE of one or more rows.

The uspGRKDTableName stored procedure accepts an optional column name to determine which key column will be used to identify a set of rows. The default behavior is to use the primary key, but any foreign key can be used instead. This routine accepts an optional key value. The default behavior is to use a key value of zero (0), which matches all rows. This routine also accepts an optional value for the RecordMask standard column. The default behavior is to use a value of zero (0), which matches all rows. If the RecordMask standard column is not present, the value is simply ignored.

The uspGRKSTableName routine performs a SELECT of one or more rows, returning all columns.

The uspGRKSTableName stored procedure accepts an optional column name to determine which key column will be used to identify a set of rows. The default behavior is to use the primary key, but any foreign key can be used instead. This routine accepts an optional key value. The default behavior is to use a key value of zero (0), which matches all rows. This routine also accepts an optional value for the RecordMask standard column. The default behavior is to use a value of zero (0), which matches all rows. If the RecordMask standard column is not present, the value is simply ignored.

The udfGRKSTableName routine performs a SELECT of one or more rows, returning all columns.

The udfGRKSTableName user-defined function expects a column name to determine which key column will be used to identify a set of rows. The primary key or any foreign key can be used. This function expects a key value to be supplied. The value zero (0) matches all rows. This function also expects a value for the RecordMask standard column as a way to filter or mask rows based on their status. The value zero (0) matches all rows. If the RecordMask standard column is not present, the value is simply ignored. The parameter is included for all tables for consistency. This function offers advantages over a view or stored procedure. It allows the use of parameters (a view does not) and it can be referenced in a FROM clause (unlike a stored procedure).

Columns and objects to support a surrogate key architecture

 Home: Introduction
 Part 1: Overview of proposed surrogate key architecture
 Part 2: Dealing with concurrent access
 Part 3: Standard routines for each table
 Part 4: Stored procedures: Create and manage standard objects

Join the conversationComment