Why to use SET NOCOUNT ON Statement
in SQL?
By default in every Sql
statement / stored procedure returns a message that indicates number of
affected rows after sql query got executed. This information is useful while
you are building your sql query or procedure (in debugging) otherwise there is
no use of it.
By using SET NOCOUNT ON; we can
reduce much more extra network traffic. So when your procedure is ready use as
below given example. It will make your query rapid and great.
CREATE PROC dbo.ProcedureName
AS
SET NOCOUNT ON;
SELECT column FROM dbo.Table
SET NOCOUNT OFF;
GO
Why to use Schema name with SQL
objects?
Everyone knows about SQL Schema,
but have we used it in our daily life query or stored procedure? We will have a
look about advantages of using schema with our SQL object.
Schema name should be used with the stored
procedure and with all sql objects referenced inside the stored procedure. This
will help sql server to directly finding the complied plan instead of searching
the objects in other schemas. It also improves the stored procedure’s
performance. Therefore, always refer the objects with qualified name in the stored
procedure like
--Good-habit
SELECT
column_name FROM dbo.TableName
--Bad-Habit
SELECT
column_name FROM TableName
Naming Convention of stored
procedures
We are very good programmer and
following excellent standards when it comes with naming convention in our code
or sql query. But sometimes we missed out something which is very important but
looks very tiny. Here are one of the example we really never realize.
While wring the stored
procedure we use any of the naming prefix i.e (sp_). We should never use the
stored procedure name as prefix sp_ it consider it is a system defined stored
procedure and search into the system objects (master database).
So while you are creating your
stored procedure use something like usp_ or on your choice name but not sp_.
Use Exist instead of SELECT
COUNT (*)
When we want to check existence
of record whether it is in our We are very much familiar with SELECT count statement but here is the better solution to
find our the existence of record into tables.
IF EXISTS (SELECT TOP 1 * FROM Table WHERE column1
= 'abc' AND column2 = 'xyz')
Print 'Yes'
ELSE
Print 'No'
the above statement get executed
return ‘Yes’ / ‘No’ as per condition whether it is true or false.
Comments