Browse Articles

Tips to improve SQL Server performance & database design

31 Aug 2022
Advanced
245K Views

Best performance is the main concern to develop a successful application. Like a coin, the database is the tail side (back-end) of an application. A good database design provides the best performance during data manipulation which results in the best performance of an application.

During database designing and data manipulation we should consider the following key points:

  1. Choose Appropriate Data Type

    Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

  2. Avoid nchar and nvarchar

    Practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters, etc.

  3. Avoid NULL in the fixed-length field

    Practice avoiding the insertion of NULL values in the fixed-length (char) field. Since NULL takes the same space as the desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.

  4. Avoid * in SELECT statement

    Practice avoiding * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in the select statement, give the name of columns which you required.

     -- Avoid
    SELECT * FROM tblName
    --Best practice 
    SELECT col1,col2,col3 FROM tblName 

  5. Use EXISTS instead of IN

    Practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.

     -- Avoid 
    SELECT Name,Price FROM tblProduct 
    where ProductID IN (Select distinct ProductID from tblOrder)
    --Best practice 
    SELECT Name,Price FROM tblProduct 
    where ProductID EXISTS (Select distinct ProductID from tblOrder)
    
  6. Avoid Having Clause

    Practice avoiding Having Clause since it acts as a filter over selected rows. Having clause is required if you further wish to filter the result of an aggregation. Don't use HAVING clause for any other purpose.

  7. Create Clustered and Non-Clustered Indexes

    Practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a table will slow the INSERT, UPDATE, DELETE operations. Hence try to keep small no of indexes on a table.

  8. Keep clustered index small

    Practice to keep clustered index as much as possible since the fields used in the clustered index may also be used in nonclustered index and data in the database is also stored in the order of the clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly. Please refer the article Effective Clustered Indexes

  9. Avoid Cursors

    Practice avoiding cursor since the cursor is very slow in performance. Always try to use SQL Server cursor alternative. Please refer the article Cursor Alternative.

  10. Use Table variable in place of Temp table

    Practice using a Table variable in place of Temp table since the Temp table resides in the tempdb database. Hence the use of Temp tables required interaction with the tempdb database that is a little bit time taking the task.

  11. Use UNION ALL in place of UNION

    Practice using UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

  12. Use Schema name before SQL objects name

    Practice using schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result, performance is best.

     --Here dbo is schema name
    SELECT col1,col2 from dbo.tblName
    -- Avoid
    SELECT col1,col2 from tblName 

  13. Keep Transaction small

    Practice keeping transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may result in deadlocks. Please refer to the article SQL Server Transactions Management

  14. SET NOCOUNT ON

    Practice to set NOCOUNT ON since SQL Server returns a number of rows affected by SELECT, INSERT, UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:

     CREATE PROCEDURE dbo.MyTestProc
    AS
    SET NOCOUNT ON
    BEGIN
    .
    .
    END 

  15. Use TRY-Catch

    Practice using TRY-CATCH for handling errors in T-SQL statements. Sometimes an error in a running transaction may cause deadlock if you have no handle error by using TRY-CATCH. Please refer the article Exception Handling by TRY…CATCH

  16. Use Stored Procedure for frequently used data and more complex queries

    Practice creating a stored procedure for query that is required to access data frequently. We also created a stored procedure for resolving more complex task.

  17. Avoid prefix "sp_" with the user-defined stored procedure name

    Practice avoiding prefix "sp_" with user-defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Hence SQL server first searches the user-defined procedure in the master database and after that in the current session database. This is time-consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure.

Read More Articles Related to SQL Server
Summary

In this article, I expose some key point to improve your SQL Server database performance. I hope after reading this article you will be able to use these tips within your SQL Server database designing and manipulation. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.

Learn to Crack Your Technical Interview

Accept cookies & close this