Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
  Tips to improve SQL Server performance &  database design

Tips to improve SQL Server performance & database design

14 Mar 2024
Advanced
263K Views
7 min read

Tips to Improve SQL Server Performance & Database Design: An Overview

Best performance is the main concern in this SQL Server Tutorial to develop a successful application. Like a coin, the database is an application's tail side (back-end). A good database design, as emphasized in this SQL Server Course, provides the best performance during data manipulation, resulting in the optimal performance of an application.

Key Points

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

1. Choose Appropriate Data Type

Choose the appropriate SQL Data Type to store your data since it also helps 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 require storing of large text data (more than 8000 characters). Up to 8000 characters of data you can store in Varchar.

2. Avoid nchar and nvarchar

Practice avoiding nchar and nvarchar data types since both the data types take just double memory as char and varchar. Use nchar and nvarchar when you are required to store Unicode (16-bit characters) data like 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 a variable-length (varchar) field that takes less space for NULL.

4. Avoid * in the SELECT statement

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

Example of Avoid * in the SELECT statement

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

5. Use EXISTS instead of IN

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

Example of Use EXISTS instead of 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 a Clause since it acts as a filter over selected rows. Having a clause is required if you further wish to filter the result of an aggregation. Don't use the HAVING clause for any other purpose.

7. Create Clustered and Non-Clustered Indexes

Practice creating clustered and non-clustered indexes since indexes help to access data quickly. But be careful, more indexes on a table will slow the INSERT, UPDATE, and DELETE operations. Hence try to keep a small no of indexes on a table.

8. Keep clustered index small

Practice keeping the clustered index as much as possible since the fields used in the clustered index may also be used in the 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 increases the size significantly. 

9. Avoid Cursors

Practice avoiding the cursor since the cursor is very slow in performance. Always try to use the SQL Server cursor alternative

10. Use the Table variable in place of the Temp table

Practice using a Table variable in place of the Temp table since the Temp table resides in the tempdb database. Hence, using Temp tables requires interaction with the tempdb database, which takes a little time to take 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 object name

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

Example of Use Schema name before SQL object name

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

13. Keep Transactions small

Practice keeping transactions as small as possible since transactions lock the processing table's data during its life. Sometimes long transactions may result in deadlocks. Please refer to the article SQL Server Transactions Management

14. SET NOCOUNT ON

Practice setting NOCOUNT ON since SQL Server returns some rows affected by SELECT, INSERT, UPDATE, and DELETE statements. 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 to the article Exception Handling by TRY…CATCH

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

Practice creating a stored procedure for queries that are required to access data frequently. We also created a stored procedure for resolving more complex tasks.

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

Practice avoiding the prefix "sp_" with the user-defined stored procedure name since the system-defined stored procedure name starts with the 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 results if system system-defined stored procedure has the same name as your defined procedure.

Read More:
Summary

Optimising SQL Server performance necessitates meticulous design and code. Choosing appropriate data types, avoiding nulls & needless clauses, strategically using indexes, minimizing cursors and temp tables, and developing efficient stored procedures are all key approaches. By following these guidelines, you may ensure that your database performs optimally for your applications.

FAQs

Q1. What trick can be used to improve database performance?

One of the most essential methods for speeding up database searches is indexing. Ensure that columns that are often searched, sorted, or used in join operations are indexed. Use the appropriate data type: Using the correct data type for columns can boost query efficiency dramatically.

Q2. How can I improve my SQL Server skills?

Practice with real-world scenarios that challenge you to solve problems and optimise performance is one of the finest ways to develop your SQL abilities and understanding. SQL workouts and quizzes can be found on websites such as HackerRank, LeetCode, Codewars, & SQL Fiddle.

Q3. What are the 5 key factors for database performance?

Workload, throughput, resources, optimization, and contention are the five elements that determine database performance. The workload that the DBMS is asked to handle defines the demand.

Q4. How do I reduce SQL query execution time?

Indexes are an important part of database performance. They enable the database management system to swiftly discover and retrieve data, lowering the time necessary to run SQL queries. Indexes on one or more columns in a table can be established, and they provide a rapid lookup technique for retrieving data.

Q5. What is SQL optimization?

SQL optimization is the practice of enhancing the performance and efficiency of SQL queries used to retrieve and manipulate data from a relational database management system (RDBMS).

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this