How can you performance tune a Stored Procedure?

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

How do you throw custom errors in SQL

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.

What are DQL commands?

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 ]

What are TCL commands?

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