donderdag 14 april 2011

SQL Server column best practices

Today the third post of the SQL Server best practices. In my former posts (part I, part II)  i already mentioned the general best practices and table best practices. In this post i'll explain my best practices regarding the columns in a table. Here are my best practices:
  • Namingconvention according to Pascal Casing.
  • Prefix the column with a single tablename together with the columnname(eg. CustomerID).
  • Another option is shortening the tablename with a short abbreviation(eg. CustID).
  • Don't use TEXT or NTEXT but Varchar(MAX).
  • Use unicode datatypes only when this is necessary. They use twice as much space as non unicode fields. In case of a website unicode is a necessary aspect and offcourse when a application is multilungual use unicode.
  • Use a proper collation. If there is no collation, the collation of the database is used. If no collation on database level is specified, the server collation is used. So, if collation are not properly specified you can run into all kind of collation conflicts in joining and querying.
  • Don't use the decimal datatype for storing currency information. Use the money datatype if you need a 4 position precision
  • Only use unique identifier when you really need this. It's using 16 bytes space.
  • If a column always has a value use the 'NOT NULL constraint'
  • Use the IMAGE datatype only when really needed.
  • Use varchar instead of char.
  • Always use the smallest datatype possible
  • If a column uses less than 20 characters use Nchar or char.
  • If a table has many large columns, think about splitting the table in multiple tables with 1:1 relation.
  • Use bit fields for values like Yes/No, True/False and True/False. SQL Server can optimize this by collecting multiple bit fields in a byte.
  • Use User Defined datatypes when multiple columns appears in multiple tables. This way a consistent datatype is used.
  •  Use the following convention:
    • For key columns based on a integer use [tbl]<name>ID and this is very usable for the primary key.
    • For key columns based on a varchar use [tbl]<name>Code.
  • Fields that are used as a foreign key, are named [tbl]<name>FK.
    • e.g. PackageAuditFK points to PackageAudit table - PackageauditID.
Greetz,

Hennie

Geen opmerkingen:

Een reactie posten