General good practice

You MUST store T-SQL scripts and database code in source control as a 'single source of truth.'

You SHOULD: -

• Check your SQL application is performant. You may find your support team or Database Administrator (DBA) may be able to help.

• Review your code to address any issues, such as performance and security considerations.

• Share code using code snippets and a common repository.

• Conform to ANSI standards wherever possible.

• Prefer CAST to CONVERT

• Prefer SET to SELECT when assigning variables.

• Prefer COALESCE to ISNULL

• Format and analyse your code using SQL Prompt before checking into source control.

• Configure SQL Prompt using our config files. See SQL Prompt configuration for help.

You SHOULD NOT:-

• Use deprecated features.

• Use dynamic SQL. If you cannot avoid this then call it using sp_executesql rather than the EXECUTE statement.

• Expect your DBA to performance tune your queries but do ask them for advice. Particularly what tools to use.

• Use undocumented stored procedures.

• Create objects that flout the single responsibility pattern. Such as: -

• Entity-attribute-value tables.

• Generic stored procedures with no specific (or multiple) use cases.

• Using a single table to hold all lookup values, metadata, and domain or reference data.

Avoid using T-SQL to execute business logic

As a rule, you SHOULD NOT place business logic in the database.

Follow these rules when using an object relational mapper (ORM)

You MUST consider this standard when using an Object-relational mapping (ORM) tool such as Microsoft's Entity Framework. And to achieve the best performance from your database: -

• You SHOULD use stored procedures for data access. Support tools (such as those used for SQL Migrations) cannot identify issues with code that is not stored in the database.

• You SHOULD follow good practice if working with ORMS. See ORM tips, below.

• You SHOULD NOT use Entity Framework's code first approach to create and maintain database schemas.