Standard Columns and Standard Objects by Brian Walker My previous article describes a surrogate key structure for implementing relationships between tables. It also provides eight stored procedures to support the architecture. This article builds on that foundation with several pieces of framework for the proposed database architecture. It describes six standard table columns, six standard stored procedures, one standard user-defined function, and two standard triggers. It also provides 12 stored procedures to create and manage the standard objects. The proposed database architecture suggests up to six columns that could be included in every table. It’s not likely that all six are needed for every database, but some are fairly universal. It would be best to use the same set of standard columns in every table of a particular database. That’s not a requirement, but consistency is a very desirable thing. Consistency allows for powerful dynamic routines that work for any table in the database. The six standard columns are: Name Data Type ========== ================== CreateUser varchar(40) or int CreateDate smalldatetime ModifyUser varchar(40) or int ModifyDate smalldatetime DetectWork int RecordMask tinyint The CreateUser column is used to identify the user that originally created the row. The column may contain a SQL Server login name, or it may contain a foreign key to a system user table if the application being supported has internal security. This column would be populated by the application. The CreateDate column is used to store the date and time when the row was originally created. This column could be populated by the application being supported, by a default value, or by a trigger. This architecture suggests using a trigger. The ModifyUser column is used to identify the user that last modified the row. The column may contain a SQL Server login name, or it may contain a foreign key to a system user table if the application being supported has internal security. This column would be populated by the application. The ModifyDate column is used to store the date and time when the row was last modified. This column could be populated by the application being supported or by a trigger. This architecture suggests using a trigger. The DetectWork column is used to detect concurrent access to a particular row. Any multi-user database application has to have some method of dealing with concurrent access to data. Concurrent access means that more than one user is accessing the same data at the same time. A problem occurs when user X reads a row for editing, user Y reads the same row for editing, user Y saves changes, then user X saves changes. The changes made by user Y are lost unless something prevents user X from blindly overwriting the row. This problem may not be handled by the database alone. The solution usually involves the application as well. One way of dealing with concurrent access is to use a counter column that is incremented by the application with each modification to the row. In the example above, user X would be alerted by the application that the row had been modified between the time of reading and the time of attempting to save changes. This situation is noticed by the database and/or by the application because the counter had changed. The application should allow user X to start over with the current row contents, or overwrite the changes made by user Y. No changes would be unknowingly lost. As mentioned, the database and application need to work together to take advantage of this column. The application reads a row for editing. It increments the counter column by one when saving changes. The database (a stored procedure) checks the stored counter before updating. If the stored counter is not less than the incremented counter, then a concurrency violation has taken place. In other words, another user changed the row between reading and attempting to save changes. The database denies the requested update and informs the application that the update was not performed. The application offers the user options for proceeding. If the user chooses to overwrite the row, the application simply increments the counter by one and tries to save again. As an alternative to incrementing the counter by one, the application could increment the counter by a very large number (such as a million) to indicate that a concurrency violation has taken place and to force the save to succeed. In this case, the counter would be used to track concurrency violations as well as updates. The RecordMask column is used to record a status indicator for the row. In some situations, rows in selected tables may have several different states of existence. This column could be used to mark rows as active, inactive, pending, disabled, deleted, or any other state that business needs may dictate. If rows would be marked as either active or deleted only it may be better to consider row auditing functionality (watch for my future article about a robust audit trail system). The proposed database architecture suggests using triggers to populate the CreateDate column and the ModifyDate column. A default value would work for the CreateDate column, but it would allow row insertions using supplied dates and that potentially reduces the validity of the dates. A trigger would cause supplied dates to be replaced with system-generated dates. Triggers are also very flexible because they can be written to fire under certain conditions. For example, it may be desirable to have the triggers fire only when a single row is being handled or when the application doing the handling is not the primary application. This arrangement would maximize performance for the primary application by allowing it to supply dates for bulk row operations and avoid the additional work of updating the dates within the trigger. The proposed database architecture suggests several standard routines for each table. There are six standard stored procedures, one standard user-defined function, and two standard triggers. Such routines can be generated from the database schema rather than being 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 is potentially subject to malicious use 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: Name Object Type Object Use ================ ===================== ========== trgGRCITableName trigger Create trgGRCUTableName trigger Update uspGRCITableName stored procedure Create uspGRCUTableName stored procedure Update uspGRCDTableName stored procedure Delete uspGRCSTableName stored procedure Read (Retrieve) uspGRKDTableName stored procedure Delete uspGRKSTableName stored procedure Read (Retrieve) udfGRKSTableName user-defined function Read (Retrieve) - requires SQL Server 2000 or newer 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 a requirement of 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). It would be extremely tedious to manually write the T-SQL code for each of the standard objects for each table in a database of any size. In order to save others from such boring repetition, I will now present a set of several tools to generate the standard objects. The tools can quickly generate the necessary T-SQL code to create the objects. The routines to generate the standard objects, and the generated objects themselves, take advantage of the consistency of the proposed surrogate key architecture. The tool set consists of 12 stored procedures to create and manage the standard objects. Each standard object is created by a separate stored procedure, so that's nine of the 12 tools. There's a stored procedure to create all of the standard objects (or just a subset of them) with one call. There's a stored procedure to delete all of the standard objects with one call. Finally, there's a stored procedure to examine which standard objects already exist in the database. The T-SQL code in Listing 1 creates 12 system stored procedures in the master database. I chose the master database for convenience, but the stored procedures could be created in user databases instead (remove the "sp_" prefixes from the names before creating the stored procedures in a user database). The listing also creates two additional system stored procedures in the master database, sp_DBAWorkMaster and sp_DBAWorkFormat. These two routines are used internally by the other 12 routines. The sp_DBAWorkMaster routine is a central location for defining the names of standard columns and the naming convention of standard objects. The sp_DBAWorkFormat routine is used to format data type declarations. The 12 main stored procedures and their actions are listed below. Name Action ==== ==================================== sp_CreateTriggerCI creates trgGRCITableName sp_CreateTriggerCU creates trgGRCUTableName sp_CreateProcedureCI creates uspGRCITableName sp_CreateProcedureCU creates uspGRCUTableName sp_CreateProcedureCD creates uspGRCDTableName sp_CreateProcedureCS creates uspGRCSTableName sp_CreateProcedureKD creates uspGRKDTableName sp_CreateProcedureKS creates uspGRKSTableName sp_CreateFunctionKS creates udfGRKSTableName sp_CreateAllObjects creates all standard objects sp_DeleteAllObjects deletes all standard objects sp_ReviewAllObjects checks for existing standard objects NOTE: The naming of things within this T-SQL code is not very helpful for understanding how it works. Parameters, variables, tables, and columns are named rather capriciously. I humbly beg your forgiveness for my programming idiosyncrasies! These stored procedures definitely reflect my weird style. The stored procedure parameters are listed here for easy reference: CREATE PROCEDURE dbo.sp_CreateTriggerCI @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @DBWhose varchar(2000) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateTriggerCU @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @DBWhose varchar(2000) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateProcedureCI @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateProcedureCU @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateProcedureCD @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateProcedureCS @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateProcedureKD @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateProcedureKS @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateFunctionKS @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_CreateAllObjects @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @DBWhose varchar(2000) = NULL, @PCAdmin varchar(20) = NULL CREATE PROCEDURE dbo.sp_DeleteAllObjects @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @DBUltra bit = 0, @PCUltra bit = 0 CREATE PROCEDURE dbo.sp_ReviewAllObjects @DBIntra varchar(8000) = NULL, @DBExtra varchar(8000) = NULL, @PCIntra varchar(100) = NULL, @PCExtra varchar(100) = NULL, @PCUltra bit = 0 The first four parameters for all 12 of the routines are the same, and all of them are optional. They work together to form a combination of table selection criteria using names. The first and second parameters (@DBIntra/@DBExtra) are lists of table names separated by pipes (vertical bars). The @DBIntra parameter specifies table names to be included. The @DBExtra parameter specifies table names to be excluded. The third and fourth parameters (@PCIntra/@PCExtra) offer table selection based on pattern matching of names. The @PCIntra parameter includes tables by using a LIKE operator on the names. The @PCExtra parameter excludes tables by using a NOT LIKE operator. The effects of these four parameters are combined with AND operators. Often only one of the parameters (or none) would be used for a given call, but it may be useful to provide @DBExtra and/or @PCExtra in combination with @PCIntra in order to work with the desired subset of tables. If the parameters are omitted, or if null values are provided, they are effectively ignored for selection purposes. The method used to select tables in these routines is not necessarily the most efficient way of handling delimited strings. The CHARINDEX function was used for simplicity because performance is not a significant issue when referencing tables with very modest row counts in an administrative routine. If this kind of selection task were being done in a production routine that referenced tables with larger row counts it would probably be better to parse the delimited string and use the result set for a join. It's awkward to embed single quotes in a string literal with T-SQL. For convenience when specifying string parameters, caret symbols (^) are converted to single quotes in the @DBWhose parameter (see below for an example). NOTE: In most cases in these routines, a parameter or variable of a particular name is used for the same basic purpose everywhere it appears. However, I use certain names as generic parameters or variables. For example, the parameters @DBIntra/@DBExtra and @PCIntra/@PCExtra are always used to select things by name, but the parameters @DBUltra, @PCUltra, and @PCAdmin are not necessarily used the same way in each routine. The fifth parameter (@DBWhose) for the first two routines (to create triggers) is optional and it's used to specify criteria under which the generated triggers will take action. If no value is provided for the parameter then the generated triggers will always take action. If a value is provided it's used within an IF statement to conditionally execute the trigger code. For example, if the value "@@ROWCOUNT = 1" is provided then the trigger will take action only when a single row is involved. As another example, if the value "@@ROWCOUNT > 1 OR APP_NAME() = ^MyApplication^" is provided then the trigger will take action when more than one row is involved or when an application named "MyApplication" is being used. The sixth parameter (@PCUltra) for the first two routines (to create triggers) is optional and it's used internally for efficiency when the routines are called by the sp_CreateAllObjects stored procedure. The fifth parameter (@PCUltra) for the next seven routines (to create stored procedures and a function) is optional and it's used internally for efficiency when the routines are called by the sp_CreateAllObjects stored procedure. The fifth parameter (@DBWhose) for the sp_CreateAllObjects routine is optional and it's simply passed through to the parameter of the same name for the two routines that create triggers. The sixth parameter (@PCAdmin) for the sp_CreateAllObjects routine is optional and it defines which standard objects should be generated. It's simply a string of nine characters with each position representing a standard object in the order listed above. If a position contains "N", "0", or space then the corresponding standard object is omitted. If a position contains any other character then the corresponding standard object is generated. The fifth parameter (@DBUltra) for the sp_DeleteAllObjects routine is optional and it determines whether T-SQL script is generated or the actions are carried out immediately. A value of zero (0) causes T-SQL script to be generated and a value of one (1) causes the actions to be carried out immediately. The sixth parameter (@PCUltra) for the sp_DeleteAllObjects routine is optional and it targets some additional objects that are outside the scope of this article. Look for more information in an upcoming article about a robust data auditing system. The fifth parameter (@PCUltra) for the sp_ReviewAllObjects routine is optional and it determines whether table name prefixes (if used and if defined) are included in the output. A value of zero (0) means they will be omitted and a value of one (1) means they will be included. These stored procedures contain a local variable, @TPre, that can be used to specify a table name prefix. If a table name prefix is identified with this variable then the prefix can be omitted from table names provided as parameters. The examples below should be executed in the context of the example database presented in my previous article. However, that database has no standard columns in any table. The routines to generate triggers sense the lack of standard columns and do not generate any code. Use the CREATE TABLE statement below to create a table with all six standard columns. Then observe how the generated routines are automatically adjusted to work with the standard columns in the Standard table. Notice that the CreateDate and ModifyDate columns in the Standard table are defined as accepting NULL in anticipation of being populated by a generated trigger, but the generated routines do not assume that the triggers are present and they provide default values for both columns. CREATE TABLE dbo.Standard (StandardID int IDENTITY(1,1), CreateUser varchar(40) NOT NULL, CreateDate smalldatetime NULL, ModifyUser varchar(40) NOT NULL, ModifyDate smalldatetime NULL, DetectWork int NOT NULL, RecordMask tinyint NOT NULL, ColumnX int NOT NULL, ColumnY int NOT NULL, ColumnZ int NOT NULL) This example generates T-SQL code to create two triggers and six stored procedures for each table. The T-SQL can be copied from the results, pasted into a new Query Analyzer connection window, and executed to create the objects in the database. EXECUTE sp_CreateAllObjects NULL,NULL,NULL,NULL,'@@ROWCOUNT = 1','YYYYYYYYN' This example generates T-SQL code to delete all generated objects. EXECUTE sp_DeleteAllObjects NULL,NULL,NULL,NULL,0 This example returns a result set showing which generated objects exist in the database. EXECUTE sp_ReviewAllObjects NULL,NULL,NULL,NULL,0