From IT Skills
Jump to: navigation, search

database[edit | edit source]

database name should not have dot symbols

table definition[edit | edit source]

Primary key[edit | edit source]

  • 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)
ALTER TABLE <TableName> ADD CONSTRAINT pk_<TableName> PRIMARY KEY (Id)

Composite keys[edit | edit source]

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)

IDENTITY[edit | edit source]

  • we may not use
    IDENTITY(1,1)
    
    for tables with static data

Unique[edit | edit source]

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

Not unique index[edit | edit source]

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

Foreign key[edit | edit source]

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

Default[edit | edit source]

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

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

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.