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 few of them won’t require re-compilation
• Add ‘WITH RECOMPILE‘ to CREATE PROCEDURE statement if query varies each time it is run (Prevents reusing the execution plan. SQL Server does not cache a plan for this procedure)
• All objects that are called within the same stored procedure should be referred to in the format of object_owner.object_name (A small performance boost, since SQL Server performs name resolution on the objects)
• Use the new ANSI JOIN syntax instead of the old style joins (The new join syntax has a slight performance advantage over the old way of using the WHERE clause for a join)
• Minimize the number of table lookups especially if there are sub-query SELECTs or multicolumn UPDATEs

Hmm, I’ve finally setup a page ‘about Em’ – yes, that’s about me! It’s a 1 page portfolio that covers my blogs, startups and a little about me. You can view it at: www.axdimensions.com

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

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 simply write ‘WITH ENCRYPTION’ after the parameters:

CREATE PROCEDURE [SP_with_Parameters]

@Customer VARCHAR(25),
@CustomerID INT

WITH ENCRYPTION
AS

/* Rest of the SP here.. */

Now when the user will attempt to view the stored procedure, an error will occur:

Error 20585: [SQL-DMO] Encrypted object is not transferable, and script can not be generated.

Last week I came across a post on the ‘Adsense Blog’ – and it cleared up my confusion regarding Adsense earning. Google Adsense program is wiser than a layman thinks. You start earning more when you start getting healthy clicks! Quoting from the Adsense Blog:

If your website performs well for advertisers, there may be increased competition among them to fill your ad spaces. This means we’d have a wider variety of possible ads to display, so the ads you see on your site may be more relevant to your site content and your users’ interests. This may lead to more clicks from your users, more placement-targeted campaigns geared towards your site, and increased advertiser bids. Overall, you’re likely to earn more revenue with your site if advertisers are generating conversions and receiving quality leads from your site.

On the other hand, if your website performs poorly for advertisers, they may be less inclined to display on your site. This means that the ads our system displays on your site may not be as relevant to your site content and your users’ interests, leading to fewer clicks and decreased advertiser bids. As a result, you’re likely to earn less revenue with your site if advertisers are performing poorly.

For more details, click here.

Unable to download the browser? Get the full setup from MediaFire. Click on the filename below to proceed!

chrome_installer.exe (7.35 MB)

Enjoy!

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

“iText is a library that allows you to generate PDF files on the fly” – using this library, one can easily write PDFs in .Net. To get started, download the library from http://www.lowagie.com/iText/ (or SourceForge.net).

After downloading the .dll file, add it as a reference in your application.

Sample code (C#.Net):

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;

namespace ConsoleApplication
{
class Program
{
static void Main(string[] args)
{
//1. Create a document obj.
Document myDoc = new Document(PageSize.A4.Rotate());

//2. Create a writer & stream that will write to the file
PdfWriter.GetInstance(myDoc, new FileStream("Em.pdf", FileMode.Create));

//3. Open document
myDoc.Open();

//4. Write content to the document
myDoc.Add(new Paragraph("Hello World!"));

//5. Close document
myDoc.Close();
}
}
}

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.

Every OS has a ‘hosts’ file that defines mapping of hostnames to IP address. The computer file is used to store information on where to find a node on a computer network, and is under the control of local computer’s administrator.

‘Hosts’ file can be helpful for blocking/redirecting hostnames and accelerating DNS resolution. It is used by variety of OS including Windows, Linux, MacOS, Symbian etc.

In Microsoft Windows NT/2000/XP/20003, its default location is:
%SystemRoot%\system32\drivers\etc\

If you open a hosts file using any text editor, it will look like:

# Copyright (c) 1993-1999 Microsoft Corp.
#
# This is a sample HOSTS file used by Microsoft TCP/IP for Windows.
#
# This file contains the mappings of IP addresses to host names. Each
# entry should be kept on an individual line. The IP address should
# be placed in the first column followed by the corresponding host name.
# The IP address and the host name should be separated by at least one
# space.
#
# Additionally, comments (such as these) may be inserted on individual
# lines or following the machine name denoted by a ‘#’ symbol.
#
# For example:
#
#      102.54.94.97     rhino.acme.com          # source server
#       38.25.63.10     x.acme.com              # x client host

127.0.0.1    localhost

To add more entries, simply add IP address followed by the hostname (as shown in the file) and your hostnames will be redirected to that IP address!