chapter 9: creating database conventions & standards mcitp administrator: microsoft sql server...

Chapter 9: Creating Database Conventions & Standards

MCITP Administrator: Microsoft SQL Server 2005 Database Server

Infrastructure Design Study Guide (70-443)

Benefits of Naming Conventions

• Easily identify an object’s purpose , type, and function

• Helps integrate new devlopers into development team

• Learning curve shortened

© Wiley Inc. 2006. All Rights Reserved.

Naming Convention Guidelines

• Establish for all major type of database objects

• Document & Disseminate conventions

• Enforce conventions


Typical Naming Conventions: Tables

• Tables typically represent entities such as Customer or Order.

• Use the name of the entity that the table

• Use singular names whenever possible.


Typical Naming Conventions: Columns

• Use meaningful name for each column in the database. (For example, use LastName for a column holding the last name of an employee in the Employee table.)


Typical Naming Conventions: Views

• Views typically join several tables or other views together to generate or summarize information.

• Use names that indicate the purpose of the information they return.

• It is common to use a standard prefix such as “vw_” for view names to distinguish them from tables.


Typical Naming Conventions: Stored Procedures

• Stored procedures express actions.

• Use a meaningful name combining verbs and objects that describe their action.

• Avoid confusion with system-stored procedures anddo not use the “sp_” prefix; consider using “usp” instead.


Typical Naming Conventions: User Defined Functions

• Use a meaningful name that describes the calculations that they perform.

• A common convention is to prefix the name with “ufn” to distinguish them from columns or views in SQL statements.


Typical Naming Conventions: Triggers

• Combine the name of the table and the trigger event type.

• Indicate whether the trigger is an AFTER or INSTEAD OF trigger by including “After” or “InsteadOf” in the name, e.g., dAfterOrder.


Typical Naming Conventions: Indexes

• Combine the name of the table and the names of the columns and frequently include a prefix such as a “IX_” prefix.

• Augment the prefix to indicate whether the index is clustered or nonclustered, a unique index, and so on.


Typical Naming Conventions: Constraints

• name the constraint after the rule it enforces or the column it operates on.

• Add a prefix indicating the type of constraint (check, primary key, foreign key, unique constraint, and so on).


Typical Naming Conventions: Schema

• Use schemas to group database objects by functionality and to partition objects into protected domains.

• Add a prefix that identifies a name as a schema so that there is no confusion that it might be a table.


Naming Conventions: Bad Practices

• Using sp_ in a user-defined stored procedure name

• Inconsistent use of upper and lowercase• Using spaces or nonalphanumeric

characters• Naming tables with tbl prefix• Including datatype abbreviation in column

name• Using shortened or abbreviated object

names• Using reserved words as object names


Vendor Naming Conventions

• Wherever possible vendor should use local naming convention

• Do not allow vendor naming conventions to supplant your system


Documenting & Disseminating Naming Conventions

• Document adopted standards

• Disseminate – Paper documents– Microsoft SharePoint Portal– Post on intranet

• Assure contractors are provided and follow naming conventions


Database Standards

• Standards used to minimize chaos• Standards must be:

– Clear– Sensible– Enforced

• Can reduce learning curves• Can reduce failure by requiring

activity through known working methods


T-SQL Coding Standards

• T-SQL should be considered true source code

• Use standard source code control and standards for coding

• Use templates for each type of object


T-SQL Coding Stylistic Standards

• Prefix every reference to a database object with the name of the schema it belongs to

• Indent every block of code appropriately

• Use UPPERCASE for all SQL and SQL server keywords


T-SQL Coding Functional Standards

• Ensure code in triggers can handle multiple inserts, updates or deletes

• Never use UDFs to perform searches on other tables

• Avoid using cursors inside stored procedures

• Require stored procedures avoid creating/using temporary tables

• Use TRY…CATCH constructs to perform error handling


Coding Standards



• Assure contractors are provided and follow coding standards


Database Access Standards

• Direct Access not normally a good practice

• Indirect access can be established by– Specifying applications use stored

procedures– Restrict all data acces to views


Indirect Access: Stored Procedure - Advantages

• Applications aren’t tightly coupled to database schema

• Can shield operations that may expose sensitive data

• Can optimize and tune queries without affecting/modifying applications

• Reduces network traffic through encapsulating logic on server rather client applications


Indirect Access Through Views - Advantages

• Can hide complex SQL loic from applications

• Reduces coupling between application and database

• Can be selective about information provided

• Applications can be seelctive about what data they retrieve


Communicating Database Access Standards



• Assure contractors are provided and follow database access standards


Deployment Process Standards

• Define roles for – Development database– Testing database– Production database


Protecting Production Data During Deployment

• Allow only production database administrators to access production database

• Make changes to database only by using T-SQL scripts

• Backup all affected databses before deployment

• Define roles and responsibilities of staff members during deployment

• Record all changes in a Run Biook


chapter 9: creating database conventions & standards mcitp administrator: microsoft sql server...

Documents

conventions wiley

typical naming conventions

benefits of naming conventions

naming convention guidelines

schema use schemas

standard prefix

inconsistent use of

tables tables