A week ago, I attended a meeting of programmers where the subject of discussion was “Good, Better and Best Programming Techniques”. Although I didn’t get a chance to talk about SQL Server during the meeting, I had prepared some notes on what I consider to be the best programming techniques in SQL. These techniques are not exhaustive and can be applied to other programming languages as well.

1. Avoid using the SP_ prefix for stored procedures

Stored procedures with the SP_ prefix are first searched in the master database before being searched in any other database. It is best to avoid this prefix to prevent unnecessary searches.

2. Keep your SQL Server up to date

Always install the latest server packs and security packs to ensure that your SQL Server is running optimally and securely.

3. Optimize your hardware

Make sure your SQL Server runs on optimal hardware. If your operating system supports 64-bit SQL Server, install the 64-bit version for better performance.

4. Use RAID 10 Array

Implementing RAID 10 Array can improve the performance and fault tolerance of your SQL Server.

5. Reduce network traffic by using stored procedures

By using stored procedures, you can reduce network traffic as only the required result set is returned from the database.

6. Return only the necessary result set

If your application requires paging, it is best to implement it in SQL Server instead of at the application level. This reduces unnecessary data transfer and improves performance.

7. Analyze query performance

After running a query, check the Actual Execution Plan to analyze the cost of the query. You can also use the Database Engine Tuning Advisor to further optimize your queries.

8. Use stored procedures instead of user-defined functions (UDFs)

Use user-defined functions sparingly and opt for stored procedures instead. Stored procedures provide more features and can achieve all the tasks that UDFs can do.

9. Test with realistic data

When testing your system, use realistic data instead of sample data. Realistic data provides a better scenario for testing and helps identify potential problems before they occur in production.

10. Avoid using SELECT *

Instead of using SELECT * to retrieve all columns from a table, specify the required column names. This reduces network traffic and minimizes locks on the table.

11. Avoid using cursors

Cursors can result in performance degradation. Instead, consider using subqueries, derived tables, or Common Table Expressions (CTEs) to achieve the same operation.

12. Reduce the use of nullable columns

Nullable columns consume an extra byte for each column used and add overhead to queries. Minimize the use of nullable columns for better performance and logic development.

13. Reduce deadlocks

Use query hints and proper logic of order in columns to reduce deadlocks. Deadlocks can impact performance and stability of the system.

14. Normalize your database

Normalized databases increase scalability and stability of the system. However, avoid going beyond the 3rd normal form as it can adversely affect performance.

15. Use WHERE clauses for assertive logic

Use WHERE clauses to compare assertive logic in your queries. Additionally, prefer using IN over NOT IN, even though IN requires more values to specify in the clause.

16. Store BLOBs in the filesystem

Store BLOBs (Binary Large Objects) in the filesystem and only store the path to them in the database. If the path is common, store it in an application variable and append the filename from the BLOB column.

17. Perform referential integrity checks and data validations

Always use constraints such as foreign keys and check constraints to ensure referential integrity and validate data. This helps maintain data integrity and consistency.

18. Optimize ORDER BY clause

If the ORDER BY clause is on an indexed column, the SQL Server optimizer will use an index scan. This can improve the performance of your queries.

19. Ensure consistent result sets from stored procedures

Stored procedures should always return the same number of result sets and the same columns regardless of the input parameters. This ensures consistency in the application.

20. Format SQL code for readability

Make your SQL code readable by formatting it properly. Use line breaks, indentation, and comments to improve code readability and maintainability.

21. Avoid using TEXT or NTEXT data types

In SQL Server 2005 and later versions, use VARCHAR(MAX) or NVARCHAR(MAX) instead of TEXT or NTEXT data types. These new data types offer better performance and functionality.

22. Optimize table joins

When joining multiple tables, order them in a way that performs the most restrictive search first. This helps filter out the maximum number of rows in the early phases of the join, improving query performance.

23. SET NOCOUNT ON

Always include the SET NOCOUNT ON statement at the beginning of your SQL batches, stored procedures, and triggers to avoid unnecessary network traffic. This also reduces the chances of errors on linked servers.

24. Avoid using temporary tables

Instead of using temporary tables, consider using Common Table Expressions (CTEs) or derived tables. These alternatives can provide better performance and simplify your queries.

25. Always take backups

Never forget to take backups of all your data. Regular backups are essential for data protection and disaster recovery.

26. Avoid working on production servers

Never perform any development or testing directly on production servers. Always use separate environments for development, testing, and production to minimize the risk of data loss or system downtime.

27. Seek help when needed

Don’t hesitate to ask for help when you need it. We all have room to learn and grow, and seeking assistance from others can lead to better solutions and improved skills.

These are some of the best programming techniques in SQL Server that can help improve performance, maintainability, and scalability of your applications. By following these techniques, you can optimize your SQL code and ensure the efficient operation of your SQL Server.