• Use column names instead of * in SELECT query
• Reduce the use of temp tables created in the SP, delete the tables once their use is over.
• Create indexes on frequently queried columns,
• use JOIN statements instead of sub-queries or correlated sub-queries.

We can use raiseError to throw custom exceptions in an P-SQL block.

Example:

RAISERROR (N'Error Raised: %s %d.', -- Message text.  
           10, -- Severity,  
           1, -- State,  
           N'number', -- First argument from the 
           5); -- Second argument.  

Returns Error Message: Error Raised: number 5.

DQL or Data Query Language commands are used for querying data from database objects. This is only a single command which can result in records from dbo such as tables, views etc and can be subject to projection.

SELECT [ DISTINCT column_1, column_2 .. | * ]
FROM <table_name>
WHERE <query_condition>
GROUP BY <columns_list>
HAVING <condition>
ORDER BY <columns_list> [ ASC | DESC ]

TCL or Transaction Control Language commands are used to enforce control on transactions within a database object which might result in a change in the state of data within the dbo.

These are:

• COMMIT
• ROLLBACK [ TO save_point_name ]
• SAVEPOINT save_point_name