1. Try to use the WHERE clause in your SELECT statements whenever possible.
  2. Avoid using Cursors as much as possible.
    Try to rewrite Cursor logic using set-based approaches.
    One reason to avoid Cursors is that each FETCH operation is equivalent to a separate SELECT, which can be very costly.
  3. Make sure your SELECT statement really needs DISTINCT. Avoid using it unnecessarily.
  4. In your SELECT statements, only specify the columns you actually need.
    Avoid using SELECT * as much as possible, since it can prevent index usage and reduce performance.
  5. The SET ROWCOUNT command performs a similar task to the TOP clause, but TOP is generally more efficient.
  6. Prefer using EXISTS and IN instead of NOT EXISTS and NOT IN when possible, as they often provide better performance.
  7. Use Constraints when necessary, such as PRIMARY KEY, FOREIGN KEY, and DEFAULT.
  8. Avoid using multiple mechanisms to enforce the same rule.
    For example, if referential integrity is enforced using PRIMARY KEY and FOREIGN KEY, do not duplicate this logic in Triggers, as it adds unnecessary overhead.
  9. When you can choose between JOIN and SubQuery, prefer JOIN because it is usually faster.
  10. If both IN and EXISTS can be used, prefer EXISTS as it is generally more efficient.
  11. Avoid using functions like SUBSTRING in the WHERE clause.
    They can cause table scans instead of index seeks.
    (This also applies to functions like RIGHT, etc.)
  12. Avoid using transformation functions in the WHERE clause whenever possible.
  13. Avoid using DISTINCT and ORDER BY unless they are truly necessary.
  14. If your application frequently performs wildcard searches on CHAR or VARCHAR (e.g., LIKE '%value%'), consider using Full-Text Search.
  15. You can use GROUP BY with or without aggregation functions, but avoid using it unnecessarily without aggregation.
  16. Prefer Derived Tables over Temporary Tables when possible.
  17. Avoid non-SARGable expressions in the WHERE clause (e.g., applying functions on columns).
    Rewrite conditions so indexes can be used effectively.
  18. Create indexes on columns frequently used in WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT.
  19. As a rule of thumb, every table should have at least one Clustered Index.
    Typically, columns with sequential values (like IDENTITY) are good candidates.
  20. Be careful not to create duplicate indexes unintentionally.
  21. Avoid creating indexes in the following cases:
  • When the optimizer does not use them
  • On very small tables
  • On very wide columns
  • On TEXT, NTEXT, or IMAGE columns
  • When the table is rarely used
  1. Sometimes splitting a composite index into multiple single-column indexes can improve performance (test to confirm).
  2. If tables are frequently joined, create indexes on the join columns.
  3. Prefer creating UNIQUE indexes when possible, as they improve search performance.
  4. Avoid using FLOAT or REAL columns as PRIMARY KEY.
  5. Do not create multiple Clustered Indexes on the same table.
  6. Avoid creating Clustered Indexes on columns that are frequently updated.
  7. Choose columns with high query usage for Clustered Indexes.
  8. A PRIMARY KEY does not always have to be a Clustered Index.
  9. Avoid using Clustered Indexes on GUID columns when possible.
  10. Always use SET NOCOUNT ON at the beginning of Stored Procedures.
  11. Replace NTEXT, TEXT, and IMAGE with NVARCHAR(MAX) and VARBINARY(MAX).
  12. Consider using Table Variables instead of Temporary Tables when appropriate.
  13. Do not use DATETIME as a PRIMARY KEY.
  14. Prefer CHECK CONSTRAINT over Triggers for enforcing rules.
  15. Keep Trigger logic minimal to reduce overhead.
  16. Avoid using ROLLBACK in Triggers whenever possible.
  17. Avoid using SELECT INTO for creating temporary tables unless necessary.

Stored Procedure Performance Tips

  1. Do not prefix Stored Procedure names with sp_.
    This prefix is reserved for system procedures, and SQL Server will first search in the master database, causing a performance hit.
  2. Instead of placing multiple queries inside a single Stored Procedure, consider splitting them into separate procedures and calling them as needed.

Reason:
SQL Server caches execution plans per query. If a procedure contains multiple queries, it may be recompiled more often, reducing performance.

Each Stored Procedure is compiled once, and its Execution Plan is reused, improving performance.

Additional Notes:

  • Use SET NOCOUNT ON
    Prevents sending the number of affected rows to the client, reducing network traffic.
  • Keep Stored Procedures small and focused
    This reduces locking and improves performance.
  • Avoid dynamic SQL when possible
    Static queries benefit more from execution plan caching.
  • Use WITH RECOMPILE when necessary
    Useful when query parameters vary significantly and cached plans are inefficient.
  • Allow users to cancel long operations
    Avoid forcing system restarts due to unresponsive queries.
  • Handle optional parameters carefully
    Use conditional logic (IF...ELSE) to generate efficient execution paths.
/