SQL Server best practices

logo header sql08 SQL Server best practicesNever under estimate the load on SQL Server because it might affect overall performance.
Pinal Dave shared with us his view on 14 best practices. Here is his list if anyone wants to add something write a comment below:

1. Store relevant and necessary information in the database instead of application structure or array.

2. Use normalized tables in the database. Small multiple tables are usually better than one large table.

3. If you use any enumerated field create look up for it in the database itself to maintain database integrity.

4. Keep primary key of lesser chars or integer. It is easier to process small width keys.

5. Store image paths or URLs in database instead of images. It has less overhead.

6. Use proper database types for the fields. If StartDate is database filed use datetime as datatypes instead of VARCHAR(20).

7. Specify column names instead of using * in SELECT statement.

8. Use LIKE clause properly. If you are looking for exact match use “=” instead.

9. Write SQL keyword in capital letters for readability purpose.

10. Using JOIN is better for performance then using sub queries or nested queries.

11. Use stored procedures. They are faster and help in maintainability as well security of the database.

12. Use comments for readability as well guidelines for next developer who comes to modify the same code. Proper documentation of application will also aid help too.

13. Proper indexing will improve the speed of operations in the database.

14. Make sure to test it any of the database programming as well administrative changes.


One thought on “SQL Server best practices

  1. Just some notes…

    1. To use LIKE with better performance, use only one percent sign, LIKE “%hej” or LIKE “hej%”
    2. Use hibernate instead of stored procedures, except in extreme cases. What’s more expensive, the extra developer time to look around for code and bugs in both database and code (and also mixing logic with datastore), or to buy a dedicated DB server, or cluster it?
    3. Comments are poor substitutions for descriptive variable names.
    4. If you have the possibility, store file and image uploads somewhere else than where your code and other resources are being stored. At least on another HD. It will make profiling, clustering, backup/restoring more easy to maintain.

    As for me, I prefer uploads in a DB (on a separate cluster if needed).

    I don’t know of any performance issues with that solution – anyone care to fill in?

Leave a Reply

Your email address will not be published. Required fields are marked *