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
Privacy Overview
Referbruv

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

Strictly Necessary Cookies

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

3rd Party Cookies

This website uses Google Analytics to collect anonymous information such as the number of visitors to the site, and the most popular pages.

Keeping this cookie enabled helps us to improve our website.