Implementation rules - Transact-SQL - Creating and Managing Databases - Creating Data Types and Tables - Specifying Data Types - Implementing Data Integrity - Planning Indexes - Creating and Maintaining Indexes - Implementing Views - Using Triggers - Other
Implementation rules |
A clustered index on every table
=> Automatically defragment tablesThe owner of all objects should be 'dbo' Access objects with roles Run the SQL-Service with a domain account
=> ReplicationUse Windows Collation if possible
=> Runs 10-20% faster, but you cannot replicate with a SQL-Server 7Logfilesize = 30-50% of DB size
Don't use unrestricted file grow!Physically mirror the Logfile on a RAID 1.
Don't put the logfile on a RAID 10 or RAID 5Put data files on a RAID 10
Transact-SQL |
Keep Business Logic on the Server as Stored Procedures Use ANSI SQL Syntax Choose an Appropriate Naming Convention Save Statements As Scripts and Comment Them Thoroughly Format Transact-SQL Statements to Be Legible by Others
Creating and Managing Databases |
Back Up the Master Database Specify a Maximum File Size Specify Large Autogrow Increments Change the Default Filegroup
Creating Data Types and Tables |
Specify Appropriate Data Types and Data Type Sizes Always Specify Column Characteristics in CREATE TABLE Generate Scripts to Recreate Database and Database Objects
Specifying Data Types |
If Columns Length Varies, Use a Variable Data Type Use tinyint Appropriately For Numeric Data Types, Commonly Use Decimal If Storage Is Greater Than 8'000 Bytes, Use Text or Image Use money for Currency Do Not Use float or real as Primary Keys
Implementing Data Integrity |
Use Constraints Because They Are ANSI-compliant Use Cascading Referential Integrity Instead of Triggers Use Declarative Data Inegrity Whenever Possible Implement Procedural Data Integrity by Using Triggers and Stored Procedures Disable Constraint Checking When Loading New Data
=> ALTER TABLE {CHECK | NOCHECK} {ALL}Disable Constraint Checking on Existing Data
=> NOCHECK Option
Planning Indexes |
Create Indexes on Columns That Join Tables Use Indexes to Enforce Uniqueness Drop Unused Indexes Avoid Long Clustering Keys Consider Using a Clustered Index to Support Sorting and Range Searches Create Indexes That Support Search Arguments
Creating and Maintaining Indexes |
Use the FILLFACTOR Option to Optimize Perfomance Use the DROP_EXISTING Option for Maintaining Indexes Execute DBCC SHOWCONTIG to Measure Fragmentation Allow SQL Server to Create and Update Statistics Automatically To Defrag Indexes Use Either DBCC INDEXDEFRAG or Rebuild the Index Consider Creating Statistics on Nonindexed Columns to Enable More Efficient Execution Plans
Implementing Views |
Us a Standard Naming Convention dbo Should Own All Views Verify Object Dependencies Before You Drop Objects Never Delete Entries in the syscomments Table Carefully Evaluate Creating Views Based on View
Using Triggers |
Use Triggers Only When Necessary Keep Trigger Definition Statements as Simple as Possible Include Recursion Termination Check Statements in Recursive Trigger Definitions Minimize Use of ROLLBACK Statements in Triggers
Other |
Use table variables instead of temporary tables, whenever possible