SQL SERVER – 15 Best Practices for Better Database Performance

In this blog post we will see 15 best practices for better Database Performance.

SQL SERVER - 15 Best Practices for Better Database Performance bestpratices-800x450

I have written 14 best practices here, read them all and let me know what is as per your opinion should be the 15th best practice.

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 to 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 date time 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 than using sub queries or nested queries.

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

12. User comments for readability as well as guidelines for the 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.

15. ______________________________________________________________

Let me know what should be the 15th best practice.

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – 2005 – Transferring Ownership of a Schema to a User
Next Post
SQL SERVER – 2005 – Restore Database Using Corrupt Datafiles (.mdf and .ldf)

Related Posts

108 Comments. Leave new

  • Hi Pinal,

    1. use correct way of table

    For Example

    use ..

    select e.,e.<col2) from .. as e
    OR
    select e.,e.<col2) from … as e

    instead of

    select e.,e…… from as e ;

    2. Avoid ISNULL(),COALESCE() AND with CONVERT ()

    3. In our Procedure avoid cursor,while,CTE (recursive) AND temporary tables

    4. Avoid more table valued UserDefinedFunction inside store procedure

    Reply
  • HI All,
    i do have a column with huge text more then 8000 char and store as image text data types what are the best option to store data in data base. can convert text data into xml.

    Reply
  • nakulvachhrajani
    July 23, 2016 2:02 pm

    I believe the 15th principle would be to understand that any RDBMS is a data management system, not a UI layer. Things like data access, data manipulation and processing should be the responsibility of an RDBMS. But one should not ask the database system to do things like formatting and representing the data.

    Reply
  • Marcus Aurélius Minervino Júnior
    July 26, 2016 6:21 am

    Create a environment of performance (training, training, tests )
    Generate and analyze the SQL Execution Plans for almost all SQLs
    Use denormalization when needed
    Use the best tools and technologies that your database has (MDC for IBM DB2, cluster indexes for SQL Server, Java VM inside database for Oracle..)

    Reply
  • Oleksandr Vdovyn
    August 5, 2016 12:47 am

    Good point.

    Reply
  • Mario I Fascino
    August 16, 2016 7:57 pm

    This one is controversial, but some of the best database architectures i have seen exhibit this one trait, either by design or by chance: 15. Minimize NULLs in your tables, use NOT NULL very sparingly and strategically.
    This one trait tends to enforce point 2, as well as data integrity.

    Reply
  • Mario I Fascino
    August 16, 2016 7:58 pm

    Sorry, meant… use NULL very sparingly and strategically…Maximize the use of NOT NULL

    Reply
  • Ricardo Rodrigues
    September 11, 2016 5:59 am

    Remember to use ‘UPDATE STATISTICS”. This save my applications. The performance is excelente. Create a job and do once on week.

    Reply
  • use nolock in tables that dont update to much, or u will get dirty reads,
    dont use nolock in tables that dont update to much, selects dont block each other.

    Reply
  • Rohidas Kumbharkar
    October 3, 2016 5:44 pm

    Choose columns with the integer data type (or its variants) for indexing. varchar column indexing will cause performance problems.

    Reply
  • # 15 – Read Codd’s Twelve Rules for Relational Databases at least once a year.

    Reply
  • #15 NO HARD CODE

    Reply
  • #15 Always Use single column (IE : Primary Key Column) while you need to just count the number of rows,it takes lesser time than count(*).

    Thanks pinal for this Idea….I think,I read my given #15 from some of your blog post.

    Reply
  • Mohsin Azam Afridi
    November 3, 2016 12:12 pm

    #15 Can be:
    As we know,for ‘Bit’ datatype in SQL,there is only two values – [1 or 0],so to handle this in Select Query,some people often use
    Select Id,Name,Address From dbo.Employee where Isnull(deleted,0)1 —Show only record that is not deleted,Instead we can use it as
    Select Id,Name,Address From dbo.Employee where deleted=0,It only checks for deleted -0 ,no concern with 1 ,so this is handy in Performance Wise. :)
    Thanks

    Reply
  • 15th best practice might be

    Avoid using Triggers to ignore deadlock or recursive trigger situation that loads server free space and crashes.
    Better to use trigger related logic in a function or SP and to be called from application codes at times for data stability and safety.

    16th best practice might be

    Avoid using cursor for simple / single table update , instead use merge query for faster query execution time and more compact SQL query.

    Reply
  • Use CTE table instead of subquery and for join use integer column

    Reply
    • All depends on query plan and cost. We can’t generalize the statement.

      Reply
      • SQL Server Performance Tune
        May 4, 2017 7:21 pm

        “10. Using JOIN is better for performance than using sub queries or nested queries.”

        Doesn’t this hold for this statement as well? Sometimes subqueries may perform better.

  • How to retrieve 50 Lacs record at a time? is it possible in sql?

    Reply
    • What kind of question is that? Yes, you can. Does it fail with any error? What exactly you would do with those records?

      Reply
      • This question is asked in interview. what should be the answer for this question? Please help me.

      • Yes, its possible but it all depends on destination. If you try this in SSMS grid, you would get failure due to SSMS not SQL Server.

  • chirag satasiya
    April 11, 2017 11:01 am

    Hi Pinal,

    For 15: Join tables with proper column.

    Fully qualified name would be preferable.

    This one i recently learned from your session.

    Reply
  • Use SELECT COUNT(1) instead of COUNT(*)

    Reply
  • Use bit type for column like active , iscashbook , isdeleted status.

    Reply

Leave a Reply

Menu