Tips to improve SQL Server database design and performance

Tips to improve SQL Server database design and performance

Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into 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

Does 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 fixed-length field

Does 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.

4.        Avoid * in SELECT statement

Does 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.

1.  -- Avoid

2.  SELECT * FROM tblName

3.  --Best practice

4.  SELECT col1,col2,col3 FROM tblName

2. Use EXISTS instead of IN

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

1.   -- Avoid

2.  SELECT Name,Price FROM tblProduct

3.  where ProductID IN (Select distinct ProductID from tblOrder)

4.  --Best practice

5.  SELECT Name,Price FROM tblProduct

6.  where ProductID EXISTS (Select distinct ProductID from tblOrder)

3. Avoid Having Clause

Does 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.

4. Create Clustered and Non-Clustered Indexes

Does 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.

5. Keep clustered index small

Does 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.

6. Avoid Cursors

Does practice to avoid cursor since cursor are very slow in performance. Always try to use SQL Server cursor alternative. Please refer below Cursor Alternative.

As we know, the cursors are required when we need to update records in a database table in singleton fashion means row by row. A Cursor also impacts the performance of the SQL Server since it uses the SQL Server instance’s memory, reduce concurrency, decrease network bandwidth and lock resources.

You should avoid the use of cursor. In this article, I am explaining how you can use cursor alternatives like as WHILE loop, Temporary tables and Table variables. We should use cursor in that case when there is no option except cursor.

Example of Cursor Alternative

Suppose we have table "ProductSales" that stores the information about each product sales. Now we want to calculate the Total Sales Quantity and Amount of each and every product.

We can solve this problem by following three methods.

1.   CREATE TABLE ProductsSales

2.  (

3.   ID int IDENTITY(1,1) NOT NULL,

4.   ProductID int NOT NULL,

5.   ProductName varchar(50) NOT NULL,

6.   Qty int NOT NULL,

7.   Amount decimal(10, 2) NOT NULL )

8.  GO

9.  SELECT * FROM ProductsSales

10.--We have the table with below data

Problem solution methods

1.        Using Cursor

1.   SET NOCOUNT ON

2.  DECLARE @ProductID INT

3.  DECLARE @ProductName VARCHAR(100)

4.  DECLARE @TotalQty INT

5.  DECLARE @Total INT

6.  DECLARE @TProductSales TABLE

7.  (

8.   SNo INT IDENTITY(1,1),

9.   ProductID INT,

10. ProductName VARCHAR(100),

11. TotalQty INT,

12. GrandTotal INT

13. ) --Declare Cursor DECLARE Cur_Product CURSOR

14. FOR SELECT DISTINCT ProductID FROM ProductsSales

15. --Open Cursor

16.OPEN Cur_Product

17.--Fetch Cursor

18.FETCH NEXT FROM Cur_Product INTO @ProductID

19.WHILE @@FETCH_STATUS = 0

20. BEGIN

21.SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID

22.SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID

23.INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)

24.FETCH NEXT FROM Cur_Product INTO @ProductID END

25.--Close and Deallocate Cursor

26. CLOSE Cur_Product

27. DEALLOCATE Cur_Product

28. --See Calculated data

29.SELECT * FROM @TProductSales

2.        Using Table Variable

1.   SET NOCOUNT ON

2.  DECLARE @ProductID INT

3.  DECLARE @ProductName VARCHAR(100)

4.  DECLARE @TotalQty INT

5.  DECLARE @Total INT

6.  DECLARE @i INT =1

7.  DECLARE @count INT

8.   --Declare Table variables for storing data

9.  DECLARE @TProduct TABLE ( SNo INT IDENTITY(1,1),

10. ProductID INT

11. )

12. DECLARE @TProductSales TABLE

13. (

14. SNo INT IDENTITY(1,1),

15. ProductID INT,

16. ProductName VARCHAR(100),

17. TotalQty INT,

18. GrandTotal INT

19. )

20. --Insert data to Table variable @Product

21. INSERT INTO @TProduct(ProductID)

22. SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC

23. -- Count number of rows

24. SELECT @count = COUNT(SNo) FROM @TProduct WHILE (@i <= @count)

25. BEGIN

26. SELECT @ProductID = ProductID FROM @TProduct WHERE SNo = @i

27. SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID

28. SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID

29. INSERT INTO @TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)

30. SELECT @i = @i + 1

31. END

32. --See Calculated data

33. SELECT * FROM @TProductSales

3.        Using Temporary Table

1.   SET NOCOUNT ON

2.  DECLARE @ProductID INT

3.  DECLARE @ProductName VARCHAR(100)

4.  DECLARE @TotalQty INT

5.  DECLARE @Total INT

6.  DECLARE @i INT =1

7.  DECLARE @count INT

8.  --Create Temporary Tables for storing data

9.  CREATE TABLE #TProduct ( SNo INT IDENTITY(1,1),

10. ProductID INT

11. )

12. CREATE TABLE #TProductSales

13. (

14. SNo INT IDENTITY(1,1),

15. ProductID INT, ProductName VARCHAR(100), TotalQty INT, GrandTotal INT )

16. --Insert data to temporary table #Product

17.INSERT INTO #TProduct(ProductID) SELECT DISTINCT ProductID FROM ProductsSales ORDER BY ProductID ASC

18.SELECT @count = COUNT(SNo) FROM #TProduct

19.WHILE (@i <= @count)

20.BEGIN

21.SELECT @ProductID = ProductID FROM #TProduct WHERE SNo = @i

22.SELECT @ProductName = ProductName FROM ProductsSales WHERE ProductID = @ProductID

23.SELECT @TotalQty = SUM(Qty),@Total = SUM(Amount) FROM ProductsSales WHERE ProductID = @ProductID

24.INSERT INTO #TProductSales(ProductID,ProductName,TotalQty,GrandTotal) VALUES(@ProductID,@ProductName,@TotalQty,@Total)

25.SELECT @i = @i + 1

26. END

27. --See Calculated data

28. SELECT * FROM #TProductSales

29.--Now Drop Temporary Tables

30. DROP TABLE #TProduct

31. DROP TABLE #TProductSales

7. Use Table variable inplace of Temp table

Does 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.

8. Use UNION ALL inplace of UNION

Does 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.

9. Use Schema name before SQL objects name

Does 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.

1.   --Here dbo is schema name

2.  SELECT col1,col2 from dbo.tblName

3.  -- Avoid

4.  SELECT col1,col2 from tblName

10. Keep Transaction small

Does 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. Please refer below article SQL Server Transactions Management

A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.

We use transaction in that case, when we try to modify more than one tables/views that are related to one another. Transactions affect SQL Server performance greatly. Since When a transaction is initiated then it locks all the tables data that are used in the transaction. Hence during transaction life cycle no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

Types of Transactions

1.        Implicit Transaction

Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements runs under the implicit transaction. If there is an error occurs within these statements individually, SQL Server will roll back the complete statement.

2.        Explicit Transaction

Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be execute as a unit. Since SELECT statements doesn’t modify data. Hence generally we don’t include Select statement in a transaction.

Transactions Example

1.   CREATE TABLE Department

2.  (

3.   DeptID int PRIMARY KEY,

4.   DeptName varchar(50) NULL,

5.   Location varchar(100) NULL,

6.   )

7.  GO

8.   CREATE TABLE Employee

9.  (

10. EmpID int PRIMARY KEY,

11. Name varchar(50) NULL,

12. Salary int NULL,

13. Address varchar(100) NULL,

14. DeptID int foreign Key references Department(DeptID)

15.)

1.   --Now Insert data

2.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')

3.  GO

4.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)

5.  SELECT * FROM Department

6.  SELECT * FROM Employee

1.   BEGIN TRANSACTION trans

2.  BEGIN TRY

3.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')

4.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)

5.   IF @@TRANCOUNT > 0

6.   BEGIN COMMIT TRANSACTION trans

7.   END

8.  END TRY

9.  BEGIN CATCH

10. print 'Error Occured'

11. IF @@TRANCOUNT > 0

12. BEGIN ROLLBACK TRANSACTION trans

13. END

14.END CATCH

1.   --Now Select data to see transaction affects

2.  SELECT * FROM Employee

3.  SELECT * FROM Department

1.   --Transaction with Save Point BEGIN TRANSACTION trans

2.  BEGIN TRY

3.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')

4.  IF @@TRANCOUNT > 0

5.   BEGIN SAVE TRANSACTION trans;

6.   END

7.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi')

8.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)

9.  IF @@TRANCOUNT > 0

10. BEGIN COMMIT TRANSACTION trans

11. END

12.END TRY

13.BEGIN CATCH

14. print 'Error Occured'

15.IF @@TRANCOUNT > 0

16. BEGIN ROLLBACK TRANSACTION trans

17. END

18.END CATCH

1.   --Now Select data to see transaction affects

2.  SELECT * FROM Employee

3.  SELECT * FROM Department

11. SET NOCOUNT ON

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

1.   CREATE PROCEDURE dbo.MyTestProc

2.  AS

3.  SET NOCOUNT ON

4.  BEGIN

5.  .

6.  .

7.  END

12. Use TRY-Catch

Does 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. Please refer below article Exception Handling by TRY…CATCH

Like C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks. In Sql Server, against a Try block we can have only one CATCH block.

TRY..CATCH Syntax

1.   BEGIN TRY

2.  --T-SQL statements

3.  --or T-SQL statement blocks

4.  END TRY

5.  BEGIN CATCH

6.  --T-SQL statements

7.  --or T-SQL statement blocks

8.  END CATCH

Error Functions used within CATCH block

1.        ERROR_NUMBER()

This returns the error number and its value is same as for @@ERROR function.

2.        ERROR_LINE()

This returns the line number of T-SQL statement that caused error.

3.        ERROR_SEVERITY()

This returns the severity level of the error.

4.        ERROR_STATE()

This returns the state number of the error.

5.        ERROR_PROCEDURE()

This returns the name of the stored procedure or trigger where the error occurred.

6.        ERROR_MESSAGE()

This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Exception handling example

1.   BEGIN TRY

2.  DECLARE @num INT, @msg varchar(200)

3.  ---- Divide by zero to generate Error

4.  SET @num = 5/0

5.  PRINT 'This will not execute'

6.  END TRY

7.  BEGIN CATCH

8.  PRINT 'Error occured that is'

9.  set @msg=(SELECT ERROR_MESSAGE())

10.print @msg;

11.END CATCH

12.GO

1.   BEGIN TRY

2.  DECLARE @num INT

3.  ---- Divide by zero to generate Error

4.  SET @num = 5/0

5.  PRINT 'This will not execute'

6.  END TRY

7.  BEGIN CATCH

8.  SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;

9.  END CATCH;

10.GO

1.                 Note

1.     A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.

2.     The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.

3.     Each TRY block is associated with only one CATCH block and vice versa

4.     TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.

5.     TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.

6.     XACT_STATE function within the TRY..CATCH block can be used to check whether a open transaction is committed or not. It will return -1 if transaction is not committed else returns 1.

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

Does practice to create stored procedure for quaery that is required to access data frequently. We also created stored procedure for resolving more complex task.

14. Avoid prefix "sp_" with user defined stored procedure name

Does practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Hence SQL server first search 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.