SQL Server Coding Standards 17

Prepared by Bihag Thaker

The stored procedure must ensure its atomicity, i.e. it either executes all statements or none. Client code (caller of a stored procedure) expects it to be this way.

It does not know how many statements are actually executed and may not implement any transaction support. In case of failure, the stored procedure must properly roll back all changes.

Before rolling back a transaction, it should check the value of @@TRANCOUNT

1 2	IF @@TRANCOUNT > 1 ROLLBACK TRANSACTION

SQL Server 2005 and later versions should use BEGIN TRY…END TRY, BEGIN CATCH…END CATCH syntax for exception handling in stored procedures.

The stored procedure should not hide/suppress errors. In cases when business functionality requires a client code to call several stored procedures/queries in a row and considers the action as an atomic action, client code must be implementing transaction support, which normally relies on the fact that an exception is raised if stored procedure fails.

If stored procedure hides an error, client transaction support will not work.

To follow this standard, throw an exception with RAISERROR() function in BEGIN CATCH…END CATCH block, pass the error details to the application and let the application handle the error. Pass the Error Number with a @ErrorNumber output parameter.

Following should be the general logic for implementing transaction along with proper exception handling:

Prepared by Bihag Thaker

Prefix the User Defined Function Name with ‘trg_’.

Try to include only letters in identifiers by using Pascal Casing and avoid the use of special characters in identifiers. Underscore (_) character may be used for element separation in an identifier.

Here element means Application Prefix, type of Operation, Table Name or Entity Name etc. However, word separation in single element should be achieved with PascalCasing only.

Use proper, meaningful and self-explanatory identifiers for user-defined functions. Following naming convention should be used for trigger:

‘trg_’ + + ‘_’ + + ‘_’ +

Here, is the name of the table on which the trigger is defined. is the type of trigger. It can be either ‘AFTER’ or ‘INSTEADOF’. is the underscore (_) separated list of operations for which trigger is defined. It can be INSERT, DELETE or UPDATE.

Some of the examples are:

1 2	trg_tblOrders_After_DELETE trg_tblOrders_After_INSERT_UPDATE

If there is some alternative solution to using triggers then avoid creating frequent triggers on the table as they degrade the performance of the DML statements.

Check if the same functionality can be achieved using the constraints as constraints are faster than triggers. Only use triggers for complex business logic whose implementation is not possible by constraints.

If the trigger is specified for INSERT, UPDATE or DELETE, do not use OUTPUT clause in their respective DML statements that fire the trigger.

Sample Trigger Code Snippet:

Prepared by Bihag Thaker

Prefix the User Defined Function Name with ‘udf_’.

Try to include only letters in identifiers by using Pascal Casing and avoid the use of special characters in identifiers. Underscore (_) character may be used for element separation in an identifier.

Here element means Application Prefix, type of Operation, Table Name or Entity Name etc. However, word separation in single element should be achieved with PascalCasing only.

Use proper, meaningful and self-explanatory identifiers for user defined functions. Following naming convention should be used for stored functions:

‘udf_’ + + ‘_’ + +

Here, part can be optional. can be a verb like ‘Get’, ‘Check’, ‘Validate’ or ‘Return’ and so on. Some of the examples are:

1 2	udf_Person_ValidateEmailAddress() udf_HR_CalculateNetSalary()

Use schemas to separate different sets of user defined functions across multiple applications when possible. For example, if schema Person is used instead of prefix ‘Person_’ and schema HR is used instead of prefix ‘HR_’, then above user defined functions should be as follows:

1 2	[Person].[udf_ValidateEmailAddress]() [HR].[udf_CalculateNetSalary]()

Do not create Scalar-Valued Function extensively. Avoid use of Scalar-Valued Functions in queries as much as possible because they degrade the performance of the queries.

Do not create Scalar-Valued Function just for the purpose of code reusability. If same functionality can be achieved with some inline calculation, then avoid functions. Code Reusability should not come at application’s performance cost.

Do not create Multiline Table-Valued Function as they tend to degrade the performance of the queries. If the same functionality can be achieved with Inline Table-valued Function, then avoid using Multiline Table-valued Function.

While creating stored procedures, specify the name of the schema explicitly within which the stored procedure is to be created even if it is dbo.

Following is the sample template of User Defined Function. It has been provided here only to have a basic idea of coding structure and standards that a stored procedure should follow:

Prepared by Bihag Thaker

Prefix the Stored Procedure Name with ‘usp_’.

Try to include only letters in identifiers by using Pascal Casing and avoid the use of special characters in identifiers. Underscore (_) character may be used for element separation in an identifier.

Here element means Application Prefix, type of Operation, Table Name or Entity Name etc. However, word separation in single element should be achieved with PascalCasing only.

Use proper, meaningful and self-explanatory identifiers for stored procedures. Stored procedures are generally used for different actions like INSERT, UPDATE, DELETE and SELECT operations.

Following naming convention should be used for stored procedures:
‘usp_’ + + ‘_’ + + /

Here, part can be optional. can be a verb like ‘Add’, ‘Insert’, ‘Update’, ‘Change’, ‘Set’, ‘Delete’, ‘Remove’, ‘Select’ and ‘Get’ and so on. Some of the examples are:

1 2 3 4

usp_Sales_GetOrderDetails usp_Sales_InsertOrderDetails usp_Sales_UpdateOrderDetails usp_Sales_DeleteOrderDetails

Use schemas to separate different sets of stored procedures across multiple applications when possible. For example, if schema Sales is used instead of ‘Sales_’ as Application Prefix, then above Stored Procedures should be as follows:

1 2 3 4

[Sales].[usp_ GetOrderDetails] [Sales].[usp_ InsertOrderDetails] [Sales].[usp_ UpdateOrderDetails] [Sales].[usp_ DeleteOrderDetails]

Create stored procedures and try to implement functionalities through stored procedures wherever possible rather than using ad-hoc queries from the application.

While creating stored procedures, specify the name of the schema explicitly within which the stored procedure is to be created even if it is dbo.

Return the success or failure status from the stored procedure with the use of RETURN statement. Value 1 should be used for success and 0 for failure.

If a stored procedure is required to return some value, do not use RETURN statement. The RETURN statement is not intended for this purpose. Consider using OUTPUT parameters as required in stored procedures and pass output values from OUTPUT parameters to the application.

Use @ErrorCode OUTPUT parameter to communicate with the application any error occurred in the stored procedure.

When possible, consider single stored procedure call which returns multiple result sets to the application rather than calling stored procedure multiple times for each different result set. This reduces round trips to the database server and improves the application response time.

Do not write very lengthy stored procedures. Try to keep them as short as possible.

If business logic which needs to be implemented within a stored procedure is very lengthy, then try to break the business logic into different modules and implement each different module in its separate stored procedure and call those sub-stored procedures in the main stored procedure.

Use SET NOCOUNT ON at the beginning of the stored procedures whenever possible. This can improve the performance of the queries.

Consider declaring variables at the top of stored procedures.

Do not use temporary tables and table variables frequently. When possible, see if the same functionality can be achieved by using Table Expressions like derived tables and Common Table Expressions.

Avoid creating frequent temporary tables in IF conditions within the stored procedure.

Whenever intermediate result set needs to be stored within a stored procedure, consider implementing temporary table approach and table variable approach. Compare the results between two approaches and choose the one which is efficient.

Always use transactions in the stored procedures when multiple DML statements are performed within a unit of work.

Keep transactions as short as possible to reduce locking issues.

Do not use WITH RECOMPILE option with the stored procedures.

Following is the sample template of a stored procedure. It has been provided here only to have a basic idea of coding structure and standards that a stored procedure should follow:

Prepared by Bihag Thaker

Prefix the View Name with ‘vw_’

Try to include only letters in identifiers by using Pascal Casing and avoid the use of special characters in identifiers. Underscore (_) character may be used for element separation in an identifier.

Here element means Application Prefix, Group Name, Table Name or Entity Name etc. However, word separation in single element should be achieved with PascalCasing only.

Use proper, meaningful and self-explanatory identifiers for Views. Views are used for SELECT operations.

Following naming convention should be used for view: ‘vw_’ + + ‘_’ + + / Here, part can be optional. can generally a verb like ‘Get’ or ‘Select’ and so on. can be optional additional description. Some of the examples are:

1 2	vw_Sales_GetOrderDetailsForToday vw_Sales_GetOrderDetailsTotalByCustomerID

Use schemas to separate different sets Views across multiple applications when possible. For example, if schema Sales is used instead of ‘Sales_’ as Application Prefix, then above Views should be as follows:

1 2	[Sales].[vw_ GetOrderDetailsForToday] [Sales].[vw_ GetOrderDetailsTotalByCustomerID]

When queries are frequently executed which require complex joins and calculations, then you should implement them in view. Consider creating views when same complex queries are executed from the different part of applications.

Whenever possible, create views that are schema bound.

Following is the sample template of a stored procedure. It has been provided here only to have a basic idea of coding structure and standards that a stored procedure should follow:

Prepared by Bihag Thaker

Data integrity is an important aspect of any database. Data integrity is implemented by using constraints.

Use constraints wisely as per the requirement and nature of the application. Constraints enforce data integrity rules but using lot of constraints may realise overhead when performing DML operations on data.

So, use them as per the requirement of the application. Generally, you should use constraints to enforce the data integrity. Following are some of the guidelines which should be followed:

Creating a clustered primary key constraint is generally the best practice. You should have one primary key constraint preferably clustered on every table in your database.

To maintain data integrity, use unique constraints when a column is not expected to hold duplicate values in any case. For example, a unique constraint should be defined on Social Security Number.

Apply referential integrity with foreign key constraints whenever possible to establish relationships between primary table and foreign table to maintain data integrity.

Always, try to apply NOT NULL constraints on columns wherever possible and keep minimum columns nullable as possible. This disallows columns to have null values and simplifies the queries and other DML operations.

When it is required for a column to allow NULL values, try to define a default value by creating a default constraint on that column.

When a column is expected to hold a specific domain of values of either a set of predefined values or a range of values, then apply check constraints to maintain the data integrity on such columns.

Prepared by Bihag Thaker

Choosing correct data type while designing tables is an important part of application designing.

Incorrect choice of data types in tables can cause database and application to perform inefficiently.
Following are some of the guidelines to choose a proper data type for a column:

Always try to choose the correct data type depending on the type and characteristics of the values that are to be stored in a particular column.

Use Unicode data types like NCHAR, NVARCHAR and NTEXT only if other complex language characters other than simple English like characters need to be stored which can’t be stored in CHAR, VARCHAR or TEXT data types.

For Unicode, data type it requires 2 bytes to store a single character which can lead the inefficient storage of data and query execution.

Always use variable length data types like VARCHAR and NVARCHAR rather than using fixed length data types like CHAR and NCHAR as variable length data types make use of storage space efficiently.
Use fixed length data types CHAR and NCHAR only when the number of characters in data is known and determined.

Wherever possible, use VARCHAR (MAX) and NVARCHAR (MAX) data types instead of using TEXT and NTEXT data types.

Always use correct choice in numeric and integer data types. Always choose the small data type which requires less storage size over the other big data types as far as it can accommodate all the possible values in a domain and serves the purpose of the application.

For example, if a CountryID needs to be stored associated with each country name and as far as we know that the total number countries are not greater than 255, then the correct choice of data type will be TINYINT and not SMALLINT or INT.

Also, columns which are expected to store the values that fall in the range of SMALLINT; define them only as SMALLINT and not INT.

Similarly, use only BIGINT data type only when it is expected from an application to have a value which falls outside of the range if INT data type.

Use DATETIME data types wisely. If you don’t expect a date value to be much small or much large which falls in the range of SMALLDATETIME data type and accuracy of more than a minute is not required then use SMALLDATETIME and not DATETIME as it requires less storage space than DATETIME data type.

Use DATETIME data type to store date values along with the time component and when time accuracy is required in milliseconds. If time component is not relevant, you should use DATE data type which stores only the date part.

Avoid choosing data types which require explicit typecasting at the time of performing DML operations.
For example, storing date value or integer value in a string data type (VARCHAR or NVARCHAR) and then converting them back to DATETIME or INT while querying data is considered a bad practice and should be avoided.

Prepared by Bihag Thaker

Prefix the Table Name with ‘tbl_’.

Use proper, meaningful and self-explanatory identifiers for tables. The table identifier should be plural. Examples are tbl_Orders, tbl_Customers, tbl_Employees.

Try to include only letters in identifiers by using Pascal Casing and avoid the use of special characters in identifiers. Underscore (_) character may be used for element separation in an identifier.

Here element means Application Prefix, Group Name, Table Name or Entity Name etc. However, word separation in single element should be achieved with PascalCasing only. For example instead of using tbl_Page_Option_Types, use tbl_Page_OptionTypes.
In this, ‘Page_’ can be an Application Prefix or Group Name.

Use schemas to separate different sets of tables across multiple applications.

An Application Prefix along with underscore characters (_) may be used for a group of related tables like tbl_Page_Layouts, tbl_Page_Options, tbl_Page_Links for better manageability.

Prepared by Bihag Thaker

MERGE statement is a new feature introduced in SQL Server 2008 and is very helpful in performing multiple operations like INSERT, UPDATE and DELETE on the same table within a single statement.

Wherever possible, use MERGE statement when a particular operation needs to be carried out based on the condition of existence or non-existence of particular records.

One application of MERGE statement is when you want to insert new records with some Primary Key values if they don’t exist and update the records with the new column values if records already exist with the same Primary Key values.
For example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

MERGE [dbo].[tbl_Customers] AS T USING ( VALUES ( 'CUST005' ,'SomeFirstName5' ,'SomeLastName5' ,'19820101' ,'SomeEmailAddress5@domain.com' ,'F' ) ,( 'CUST006' ,'SomeFirstName6' ,'SomeLastName6' ,'19820101' ,'SomeEmailAddress6@domain.com' ,'F' ) ) AS S (CustomerID,FirstName,LastName,BirthDate,EmailAddress,Gender) ON T.CustomerID = S.CustomerID WHEN NOT MATCHED BY TARGET THEN INSERT ( CustomerID ,FirstName ,LastName ,BirthDate ,EmailAddress ,Gender ) VALUES ( S.CustomerID ,S.FirstName ,S.LastName ,S.BirthDate ,S.EmailAddress ,S.Gender ) WHEN MATCHED THEN UPDATE SET CustomerID = S.CustomerID ,FirstName = S.FirstName ,LastName = S.LastName ,BirthDate = S.BirthDate ,EmailAddress = S.EmailAddress ,Gender = S.Gender;

Had this task be done without using MERGE statement, then you would have to write multiple INSERT and UPDATE statements for each record with the check of existence of a particular record by a particular Primary Key using IF EXISTS(). MERGE statement simplifies this task and
make the code more manageable.

Another application of MERGE is when you want to synchronize two tables.

For example, if there are tbl_Orders and tbl_OrderHistory tables and at the end of the week you synchronize these tables in a way so that new orders from tbl_Orders table should be inserted in tbl_OrderHistory table, deleted orders from tbl_Orders table should be deleted from tbl_OrderHistory table and updated orders from tbl_Orders table should be updated in tbl_OrderHistory table then rather than using multiple INSERT, UPDATE, DELETE statements with complex conditions, perform such task with MERGE statement.

Prepared by Bihag Thaker

Use the same indentation rules for DELETE statement as specified in the article of the SELECT statement.

While writing DELETE queries, make sure that the correct set of rows will be deleted by including WHERE clause. DELETE operation once committed cannot be undone.

Writing and executing a SELECT query with the similar WHERE criteria as in DELETE statement on the table should be exercised before implementing actual DELETE queries. In this way, it can be confirmed that the correct set of rows will be deleted.

When it is required to return deleted records t other calling application, consider using OUTPUT clause in the DELETE statement rather than using a temporary table or table variable to store deleted records followed by a SELECT statement.

1 2 3 4 5 6 7 8

DELETE FROM [dbo].[tblCustomers] OUTPUT deleted.FirstName ,deleted.LastName ,deleted.BirthDate ,deleted.EmailAddress ,deleted.Gender WHERE CustomerID='CUST006'