zondag 6 maart 2011

SQL Server general best practices

Today a post about general best practices that can be used when you're developing with SQL Server.  These best practices i've been gathering in the last 5 years and are grabbed from different places or are discovered during my projects. These best practices have been efficient, effective that have been based on repeatable procedures that have been proven themselves over 5 years. I plan to write best practices about the following subjects:
  • General best practices
  • Projects
  • How to work when developing with SQL Server
  • Server objects
    • Linked Server
    • Service
    • Email
    • Job
    • Ad Hoc Distributed Queries option
  • Database objects
    • General
    • Database
    • Table
    • Columns
    • View
    • Stored procedure
    • User Defined Function
    • Trigger
    • Index
    • User defined data type
    • Primary key
    • Foreign key
    • Default
    • Variable
    • Cursor
    • Temporary table
    • Filegroup
    • Login
    • User
    • Role
    • Transactions
    • Documentation
  • T-SQL
    • Query
    • T-SQL coding
This post is about the first subject : General Best practices. So here are my general SQL Server best practices :
  • Choose a language for naming the objects in SQL Server and stick to it.
  • Store your project files, .cmd's, third party software not on C but on D:  (mostly the CD/DVD) or E:. In this way Windows and other software can be installed very easily without worrying about your projectfiles.
  • Don't use reserved words. Check books online and look for "Reserved words".
  • Don't use spaces in the names of SQL Server objects. They can interfere with other applications
  • Don't use ondocumented features of SQL Server because:
    • There is no support from Microsoft.
    • It's not sure that in future Servicepacks or new versions the feaure still exists.
  • Use 'fully qualified' names for objects like "schemaname.objectname". In this way it's easier for SQL Server to identify the objects (resolve).
  • Never adjust system tables, system views or stored procedures in SQL Server because:
    • There is no support from Microsoft.
    • It's not sure that in future Servicepacks or new versions the feaure still exists.
  • Use version control software  like Team Foundation Server or Subversion. This is important to administer code and this becomes more important when multiple developers are working on the code.
  • Always use the 'normal' characters like "ABCabc". Never use characters like "@#$%^&^*()-, etc. An exception is made for
    • The underscore (_). Try to avoid this because it's not very pleasant to read this.
  • Build DTAP proof code! DTAP is a acronym for Develop-, Test-, Acceptance and Productionenvironment. So a wrong example of building a non DTAP compliant code is using a name of server as a Linked server object or using a servername in your code.
  • Never use 'sa' in your applications for logging in to SQL Server.
  • Do not create objects in the master database.
So hope that it  will help you when developingin SQL Server.

Greetz,
Hennie

Geen opmerkingen:

Een reactie posten