From IT Skills
Jump to: navigation, search


database name should not have dot symbols

table definition[edit]

Primary key[edit]

  • name Id is better than <TableName>Id because it is distinct from reference field
    • however if primary key should be not of INT type but VARCHAR or uniqueidentifier then other name than <TableName>Id is good
  • constraint name like pk_<TableName> for example, by script
ALTER TABLE <TableName> ADD Id int IDENTITY(1,1)

Composite keys[edit]

1. Composite keys are supported for legacy databases only and using them imposes some limitations on the default functionality. You shouldn't use them in new XPO applications, because a) it is always possible to avoid this by changing your database schema; b) it often indicate bad database design. In most cases while developing a new XPO-based application, e.g., using an Object Oriented model, you won't need compound keys (this is true for other popular ORMs like Entity Framework).

  • 2-fields composite key may be used for many-to-many tables or for history table (mainly inserted, some rows may be deleted)


  • we may not use
    for tables with static data


  • constraint name like un_<TableName>_<FieldName>

Not unique index[edit]

  • constraint name like idx_<TableName>_<FieldName>

Foreign key[edit]

  • constraint name like fk_<TableName>_<ReferencedTableName>_<OptionalSuffix>
  • on cascade delete not recommended because database administrator may modify data by t-sql scripts


  • constraint name like df_<TableName>_<FieldName>

Use nvarchar(max), varchar(max), and varbinary(max)[edit]

Ntext, text, and image data types will be removed in a future version of Microsoft SQL Server.

Avoid using these data types in new development work.

Pages in category "MS Sql coding guide"

The following 3 pages are in this category, out of 3 total.