Tips to improve SQL Server database design and performane

Tips to improve SQL Server database design and performance

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.

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.

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

Avoid * in SELECT statement : Practice to avoid * 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 select statement, give the name of columns which you required.

-- Avoid

SELECT * FROM tblName

--Best practice

SELECT col1,col2,col3 FROM tblName

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)

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

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 tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.

Keep clustered index small : Practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly.

Avoid Cursors : You should avoid using SQL cursor since it has adverse effect on SQL server’s performance. It fetches the records row by row which results in repeated network round trips.

Use Table variable inplace of Temp table : Practice to use Table varible in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking task.

Use UNION ALL inplace of UNION : Practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

Use Schema name before SQL objects name: Practice to use 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

Keep Transaction small : Practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks.

SET NOCOUNT ON: Practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement.This prevents stored procedure to send messages indicating number of rows affected thus saves network traffic.

CREATE PROCEDURE dbo.MyTestProc

AS

SET NOCOUNT ON

BEGIN

.

.

END

Use TRY-Catch : Practice to use 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.

Use Stored Procedure for frequently used data and more complex queries : Practice to create stored procedure for query that is required to access data frequently.

Avoid prefix "sp_" with user defined stored procedure name : Practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Because SQL server first search the system defined stored procedure. This is time consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure.