Transcript MCITP Administrator: Microsoft SQL Server 2005 Database

MCITP Administrator: Microsoft
SQL Server 2005 Database Server
Infrastructure Design Study
Guide (70-443)
Chapter 9: Creating Database
Conventions & Standards
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
© Wiley Inc. 2006. All Rights Reserved.
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.
© Wiley Inc. 2006. All Rights Reserved.
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.)
© Wiley Inc. 2006. All Rights Reserved.
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.
© Wiley Inc. 2006. All Rights Reserved.
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.
© Wiley Inc. 2006. All Rights Reserved.
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.
© Wiley Inc. 2006. All Rights Reserved.
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.
© Wiley Inc. 2006. All Rights Reserved.
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.
© Wiley Inc. 2006. All Rights Reserved.
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).
© Wiley Inc. 2006. All Rights Reserved.
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.
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
Vendor Naming Conventions
• Wherever possible vendor should
use local naming convention
• Do not allow vendor naming
conventions to supplant your system
© Wiley Inc. 2006. All Rights Reserved.
Documenting & Disseminating
Naming Conventions
• Document adopted standards
• Disseminate
– Paper documents
– Microsoft SharePoint Portal
– Post on intranet
• Assure contractors are provided and
follow naming conventions
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
Coding Standards
• Document adopted standards
• Disseminate
– Paper documents
– Microsoft SharePoint Portal
– Post on intranet
• Assure contractors are provided and
follow coding standards
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.
Communicating Database
Access Standards
• Document adopted standards
• Disseminate
– Paper documents
– Microsoft SharePoint Portal
– Post on intranet
• Assure contractors are provided and
follow database access standards
© Wiley Inc. 2006. All Rights Reserved.
Deployment Process
Standards
• Define roles for
– Development database
– Testing database
– Production database
© Wiley Inc. 2006. All Rights Reserved.
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
© Wiley Inc. 2006. All Rights Reserved.