Query to list all the procedures and triggers not having SET NOCOUNT ON defined

It is a tiny query in size but can play a crucial role to avoid overhead, caused due to missing SET NOCOUNT ON definition in the stored procedures and triggers. Read more about SET NOCOUNT here. Here are few useful articles talking about why it's important to add SET NOCOUNT ON. SET NOCOUNT ON Improves … Continue reading Query to list all the procedures and triggers not having SET NOCOUNT ON defined

Best practices for Stored Procedures in SQL Server

This article includes set of 40 recommendations for stored procedures. Following checklist can be used by Developers during development of stored procedure. Leads can refer this checklist for review of the stored procedure submitted for code review. This checklist can also come handy to Database Developers and Administrators while working on Performance Tuning/Optimization of stored … Continue reading Best practices for Stored Procedures in SQL Server

EXISTS() and NOT EXISTS()

Many a times we have requirements to check existence of a particular value in a particular table. There could be several ways to accomplish it viz. using left join and sub-query. All of us know that sub-queries are not optimized way while dealing with large volume of data. So what is the alternative to the Left Join which could also cause performance … Continue reading EXISTS() and NOT EXISTS()