In this era of software development technology, the main concern in development should be the best performance. As we already aware that database is the backbone of any software. So whilst designing the database we need to keep a few things in mind to excel the performance. So here we go with those key points:-

1) Appropriate Data Type:- SQL Data Type should be appropriate as it also helps to tweak the query performance. You might be thinking how to choose the data type and on which basis, so let me explain with an example: Suppose you have to store strings, you need to use 'varchar' in place of 'text' data type, 'varchar' performs better than 'text'. 'Text' can be used in case if you have to store large text data i.e. more than 8000 characters. If its up to 8000 characters you can use 'varchar'.

2) Avoid nchar and nvarchar:- You should avoid 'nchar' and 'nvarchar' data type because both data types takes the double memory as 'char' and 'nvarchar'. You should use these data types only in case when you are required to store Unicode (16-bit characters) data like as Hindi, Japanese characters etc.

3) Avoid NULL in fixed-length field:- If NULL is required to insert in a field, use variable-length field in place of fixed-length field as NULL takes the same space as desired input value for that field. (Just for reference or to give you the idea, 'Char' is fixed-length and 'Varchar' is variable-length).

4) Avoid * in SELECT statement:- Why to avoid this, because SQL Server converts the * to columns name before query execution. Another thing, instead of querying all columns by using * in select statement, just give the name of columns you required.

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

5) Use EXISTS instead of IN:- Best practice to check existence is to use EXISTS 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:- Having Clause acts as filter over selected rows. If you are required to filter the result of an aggregations, only then it should be used. Don't use this for any other purpose.

7) Clustered and Non-Clustered Indexes:- Indexes helps in to access data fastly. But creating large number of indexes may slow down the INSERT, UPDATE, DELETE operations. So try to keep small number of Indexes.

8) Avoid Cursors:- Cursors are very slow in performance. So always try to use SQL Server cursor alternative.

9) Use Table variable in place of Temp table:- Better to use Table variable because Temp table resides in the TempDb database. So, use of Temp tables required interaction with TempDb database which is little bit time taking task.

10) Use UNION ALL in place of UNION:- UNION ALL faster than UNION because it doesn't sort the result set for distinguished values.

11) Use Schema name before SQL objects name:- Schema Name helps the SQL Server for finding that object in a specific schema to have the best result performance.

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

12) Keep Transaction small:- Try to keep the transaction as possible as small, transactions locks the processing tables data during its life (for data integrity). Sometimes, long transactions may lead to deadlocks.

13) SET NOCOUNT ON:- SQL Server returns number of rows effected by SELECT, INSERT, UPDATE and DELETE statement. We can prevent this by setting NOCOUNT ON like as:\

CREATE PROCEDURE dbo.TestProcedure
AS
SET NOCOUNT ON
BEGIN
--Your query goes here..
END 

14) Use TRY-Catch:- As I already mentioned that long transactions may lead to deadlocks, so if you are required to have large transactions, in that case, if one of them causes error, results into deadlock. To prevent deadlock, you can use exception handling. Use Try-Catch to handle these error.

15) Avoid prefix "sp_" with User Defined Stored Procedure name:- As most of us are aware that System Defined Stored Procedure name starts with prefix "sp_" and SQL server first search the User Defined Procedure in the master database and after that in the current session database. If you are creating procedure having prefix "sp_", that will lead to make search process time consuming. If system defined stored procedure have the same name as user defined procedure, it may give unexpected results.


These are some key point I exposed here, hope it will help you excel the performance of database design.

Happy Coding.. :)