Query Design hints

Here’s a list of the performance-related best practices you should follow when designing the database queries:

·         Use the command SET NOCOUNT ON.

·         Explicitly define the owner of an object.

·         Avoid nonsargable search conditions.

·         Avoid arithmetic operators and functions on WHERE clause columns.

·         Avoid optimizer hints.

·         Stay away from nesting views.

·         Ensure there are no implicit data type conversions.

·         Minimize logging overhead.

·         Adopt best practices for reusing execution plans.

·         Adopt best practices for database transactions.

·         Eliminate or reduce the overhead of database cursors.

·         BETWEEN is better than IN

·         always use the same data type for both expressions in where clause to avoid implicit conversions

·         Use EXISTS over COUNT(*) to Verify Data Existence

·         Use UNION ALL Instead of UNION

·         Reduce Logging Overhead by using table variables in place of temp tables

·         Do not use SELECT *; use proper column names to decrease network traffic and fewer locks on table.

·         Avoid Cursors as it results in performance degradation. Sub Query, derived tables, CTE can perform same operation.

·         NULL columns consume an extra byte on each column used as well as add overhead in queries. Also NULL is not good for logic development for programmers.

·         Normalized database always increases scalability and stability of the system. Do not go over 3rd normal form as it will adversely affect performance.

·         Use WHERE clauses to compare assertive logic. Use IN rather than NOT IN even though IN will require more value to specify in clause.

·         Always perform referential integrity checks and data validations using constraints such as the foreign key and check constraints.

·         Stored Procedure should return same numbers of resultset and same columns in any input parameters. Result Set of Stored Procedure should be deterministic.

·         Index should be created on highly selective columns, which are used in JOINS, WHERE and ORDER BY clause.

·         Use Column name in ORDER BY clause instead of numbers.

·         Do not use TEXT or NTEXT if possible. In SQL Server 2005 use VARCHAR (MAX) or NVARCHAR (MAX).

·         Do not use temp tables; use CTE or Derived tables instead.

·         Try to use constraints instead of triggers, whenever possible. Constraints are much more efficient than triggers and can boost performance.

·         Use table variables instead of temporary tables. Table variables require less locking and logging resources than temporary tables, so table variables should be used whenever possible.

·         When you use GROUP BY with the HAVING clause, the GROUP BY clause divides the rows into sets of grouped rows and aggregates their values, and then the HAVING clause eliminates undesired aggregated groups. In many cases, you can write your select statement so, that it will contain only WHERE and GROUP BY clauses without HAVING clause.

·         Try to avoid using the DISTINCT clause, whenever possible.