SQL Server Standards

Database Object Names

1. Names of all database objects, except columns, are prefixed with a lower case identifier as shown in the table below.
2. Each word in an object's name is capitalized and separated by an underscore. However, an underscore is not required between the prefix and the first word.
   • Matriculation_Date is more readable than matriculationdate.
   • Both tblAward and tbl_Award are acceptable.
3. Prepositions, conjunctions, and articles are used sparingly in column names.
   • Student_Count is less verbose than Count_Of_Students.
4. Column names are descriptive, and abbreviations avoided wherever possible.
   • School_City is more descriptive than City.
5. Column names are consistent throughout the database.
   • If a column is named Course_ID in one table, it is not named Section_ID in another.
6. Stored procedures begin with the identifier "usp" (user-defined stored procedure) to differentiate them from Microsoft's system stored procedures. This is also more efficient because when SQL Server encounters a procedure beginning with sp_, it first tries to locate it in the master database first.
7. Tables, views, and stored procedures are always fully qualified to ensure SQL Server follows a direct path to find it rather than searching through the master catalog.

Development Standards

1. All tables are in, at least, third normal form. Exceptions include tables used exclusively for reporting or as data collection stores.
2. For every table defined, at least one view is required. As many other views may be defined as are necessary. Application software refers to views instead of the tables themselves. This insulates the application from changes that are made to the underlying tables.
3. All tables, with the possible exception of very small ones with limited growth potential, include a primary key to guarantee uniqueness and enhance performance.
4. Keys composing the primary index must be consecutive and exist at the beginning of the row.
5. Referential integrity is maintained through the use of foreign keys. (A foreign key is one or more columns of a table whose values must be equal to a primary key or unique constraint in another table.) However, the use of foreign keys to enforce RDI is balanced with the corresponding performance overhead.
6. Datatypes must be appropriate to contents of column. UNICODE characters, nchar and nvarchar, are not allowed unless required for a specific reason.
7. Basic data validation is performed at the data level. That is, columns are defined with the correct size, datatype, and other characteristics. This is much safer than relying on programmatic constraints.
8. Table columns used in frequent search operations are indexed to improve efficiency. The DBA determines and defines the type of index required (i.e., unique, non-unqiue, cluster, non-clustered).
9. Tables are joined using the "JOIN" syntax. The older, less precise method using the "*-" or "-*" notation are no longer acceptable, as support for it will eventually be dropped.
10. Every attempt must be made to use stored procedures rather than dynamic SQL statements in Web and application development. Stored procedures are already compile, whereas dynamic SQL must be compiled every time it is run.
11. Tables with the potential for unlimited growth include a plan for purging and/or archiving before moving to production status.

Permissions

1. The owner of all database objects must be 'dbo'. To insure proper ownership preface object names with "dbo" when they are created, as in these examples:
   • Tables: CREATE TABLE [dbo].[tblTableName]
   • Views : CREATE VIEW [dbo].[vViewName]
   • Stored Procedures: CREATE PROCEDURE [dbo].[uspProcName]
2. Permissions are not granted directly on tables but on views and stored procedures instead. If a table is deleted in order to make structural changes, permissions are lost. However, permissions granted on views and procedures remain intact.
3. Permissions are not granted to individual login accounts but on roles. Therefore, individual users always belong to a role that defines the function they perform within SQL Server. This preserves permissions when users' roles within the organization change.

Security

1. Trusted security (i.e., Windows NT security) is used whenever possible in SQL Server 7.0 and beyond. Exceptions are the remaining generic accounts and accounts used by programs and the Web.
2. Individual logins are not granted permissions to objects; instead, individuals are assigned to roles which have the necessary permissions granted.
3. Personal login accounts are never placed in program code. If a program required SQL Server access, request a separate account.

Administration

1. The database administrator (DBA) defines and manages all objects in the production environment.
2. Developers request moves to production at least a week before they are due.
3. Database objects are moved to production status after being reviewed for adherence to the above standards.
4. Modifications are made and tested on the test server before they are migrated to production.
5. The test database server is not used for production applications.