Recommended Practises

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

	A clustered index on every table => Automatically defragment tables
	The owner of all objects should be 'dbo'
	Access objects with roles
	Run the SQL-Service with a domain account => Replication
	Use Windows Collation if possible => Runs 10-20% faster, but you cannot replicate with a SQL-Server 7
	Logfilesize = 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 5
	Put data files on a RAID 10

	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

	Back Up the Master Database
	Specify a Maximum File Size
	Specify Large Autogrow Increments
	Change the Default Filegroup

	Specify Appropriate Data Types and Data Type Sizes
	Always Specify Column Characteristics in CREATE TABLE
	Generate Scripts to Recreate Database and Database Objects

	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

	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

	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

	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

	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

	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

	Use table variables instead of temporary tables, whenever possible