SQL Server guidelines

Databases are not a primary concern of Nexus Link, but some of our services are based on databases and we often end up helping customers to create databases for micro services, etc. In this article we are collecting our current best practices.

Table template

This is our template for creating a database table. The template is followed by sub chapters with details and the rationale behind our choices.

CREATE TABLE dbo.[{TableName}]
  (
    Id uniqueidentifier NOT NULL ROWGUIDCOL
        CONSTRAINT DF_{TableName}_Id DEFAULT (newid())
        CONSTRAINT PK_{TableName} PRIMARY KEY NONCLUSTERED,
    RecordVersion rowversion,
    RecordCreatedAt datetime2 NOT NULL
        CONSTRAINT DF_{TableName}_RecordCreatedAt DEFAULT (sysutcdatetime()),
    RecordUpdatedAt datetime2 NOT NULL
        CONSTRAINT DF_{TableName}_RecordUpdatedAt DEFAULT (sysutcdatetime())
  ) 
GO
CREATE CLUSTERED INDEX IX_{TableName}_RecordCreatedAt ON dbo.[{TableName}] (RecordCreatedAt)
GO

Primary key (Id)

• We always have a single column as the primary key.
• We always name the primary key column as Id.
  Rationale

  See our naming conventions

• We always use a uniqueidentifier as the primary key.
  Rationale

  This has been discussed heavily among experts and you can find a summary and further links in the most upvoted answer to the question Guid vs INT - Which is better as a primary key?.
  We are aware that there are pro's and con's. We have used uniqueidentifier for the primary key for many years and it has served us well.

• By marking it as rowguidcol SQL server will add a useful variable, $ROWGUID
  Rationale

  See this article about [why you should use ROWGUIDCOL] (https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d4e763fd-13ee-40a2-9ace-443df609fbdf/rowguidcol?forum=sqldatabaseengine)

• By adding a constraint with a default value, newid(), the primary key will be automatically created on insertion. Use newid, not newsequentialid.
  Rationale

  newid PROS:

  • It is impossible for an intruder to guess other id:s after having seen one generated id. This means that it can for instance safely be used in URL:s that lead to secret information.
  • It is possible to let the caller create the id; id doesn't have to be generated by the database
    newidCONS:
  • Performance is degraded if the clustered index is based on the primary key. Our counter measure is to use the RecordCreateAt column for the clustered index, thereby getting a minimal performance hit, see below.
• Set an explicit name for the constraint.
  Rationale

  See our naming conventions

Clustered index (RecordCreatedAt)

All tables should have a clustered index (Microsoft) and in Azure SQL databases it is even mandatory. By default the primary key would be the basis for the clustered index. Our choices for the primary key above means that we will have another base for the clustered index.

• Set the primary key as nonclustered
• Add a datetime2 column named RecordCreatedAt. This name is used by convention by us.
• Add a clustered index for the RecordCreatedAt field
• Add a constraint with a default value, sysutcdatetime(), to automatically fill the value at insertion and with maximum sub second accuracy (which is not supported by getutcdate()), which increases the probability that the value is unique. Set an explicit name for the constraint

Optimistic concurrency (RecordVersion)

In Nexus Link, we use optimistic concurrency. We can support from the database side by simply doing as follows:

• Add a rowversioncolumn named RecordVersion.

Whenever we read data from the table we will also read the record version and when we update the record we update it with a condition; WHERE RowVersion=@RowVersion. If this doesn't result in an update, then someone else has updated the data before us and we have a conflict condition.

The RecordVersion column also can come in handy for non-trigger based detection of which rows that have changed in a table. For instance; Azure Logic Apps can detect table changes if the table has a primary key and has a column of type rowversion.

Naming conventions

• Table names are in singular, i.e. Product not Products.
  Rationale

  There are of course different opinions on Stack Overflow on this subject, but we have chosen singular.

• The primary name column is named Id.
  Rationale

  This is primarily by convention as there are many opinions on this subject, see Stack Overflow.

• As we always access our databases from C# code we tend to use column names that also fits the naming conventions for property names in C#. For instance we use Pascal case, e.g. FirstName and avoid the underscore character.
• Foreign key columns are named as follows: {reference name}Id where reference name is a suitable way to name the reference, e.g. MotherId. If there is no natural reference name, then we use {referenced table}Id, eg PersonId.
• Time stamp columns are named {description with verb in imperfect}At. E.g. RecordCreatedAt. Rather than naming a column ValidUntil we would therefore name it DeprecatedAt.

Constraints, etc

• Constraints should always be named
  Rationale

  If you don't name them you will get generated names that may differ between instances of the database, making it hard to drop them. Here is a blog post about it.

• Default constraints are named DF_{table name}_{column name}, e.g. DF_Person_Id
• Foregin key constraints are name FK_{source table name}_{source column name}_{target table name} or FK_{source table name}_{target table name}.
• Primary keys constraints are named PK_{table name}, e.g. PK_Person
• Indexes are named IX_{table name}_{column names}, e.g. IX_Person_LastName_FirstName. Use UX_ instead of IX_ if the index is unique.

Miscellaneous

• Use the column type datetime2 for time stamps (and use sysutcdatetime() if you want to get the current time).
  Rationale

  "datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage." according to the top answer in article from stackoverflow. On top of that it is also recommended by Microsoft

• Use UTC for times saved to Datetime2 columns.