Here are few tips to tune up stored procedures in SQL Server. I gathered these after doing some search on Google:

Include the ‘SET NOCOUNT ON‘ statement to stop the message indicating the number of rows affected (Reduces network traffic)
Break down the stored procedure in parts and call them from 1, esp. if you know that [...]

Continue reading...

In SQL Server 2000, you can insert multiple records by using a single INSERT statement. It helps to boost performance a little and the task gets done in a single query.
Sample code:

INSERT INTO MyTable  (Col1, Col2)
SELECT  'First', 1
UNION ALL
SELECT  'Second', 2
UNION ALL
SELECT  'Third', 3
UNION ALL
SELECT  'Fourth', 4
UNION ALL
SELECT  'Fifth', 5
GO

Continue reading...

Often developers require hiding the stored procedures from end users. In such case, developers can perform a one way encryption using the WITH ENCRYPTION clause. Once the definition of the stored procedure is encrypted, it cannot be decrypted or viewed by anyone.
Sample Code:

CREATE PROCEDURE [SP_Name]
WITH ENCRYPTION
AS

SELECT
[CustomerID], [CompanyName]
FROM
[Northwind].[dbo].[Customers]

If your stored procedure has some parameters, you can [...]

Continue reading...

DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
Source: Transact-SQL Reference

Continue reading...

A simple query that computes time difference between two dates and displays the result in “hh:mm” format.

– 2008-08-10 00:00:04 < Start_Time
– 2008-08-10 08:32:17 < End_Time
– 8:32 << Output (HHMM_Duration)

SELECT Start_Time, End_Time,
CONVERT(VARCHAR, DATEDIFF(hour, Start_Time, End_Time)) + ':' + CONVERT(VARCHAR, DATEDIFF(minute, Start_Time, End_Time)%60) as HHMM_Duration
FROM Events

*The above query was executed on Microsoft SQL Server 2000.

Continue reading...