WAITFOR in Sql Server 2008

WAITFOR in Sql Server 2008

Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row.


A. Using WAITFOR TIME

The following example executes the stored procedure sp_update_job at 10:20 P.M. (22:20).

USE msdb;
EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
    WAITFOR TIME '22:20';
    EXECUTE sp_update_job @job_name = 'TestJob',
        @new_name = 'UpdatedJob';
END;
GO
B. Using WAITFOR DELAY

The following example executes the stored procedure after a two-hour delay.

BEGIN
    WAITFOR DELAY '02:00';
    EXECUTE sp_helpdb;
END;
GO

http://msdn.microsoft.com/en-us/library/ms187331(v=SQL.100).aspx

Calculate Time Difference Between DateTime Objects

To get time difference you need to use TimeSpan object, with code like this:

TimeSpan ts = DateTime1 - DateTime2;

For example, if you want to calculate time difference between server time and UTC time:

[ C# ]

protected void Page_Load(object sender, EventArgs e)
{
    // Declare and get DateTime values
    DateTime StartDate = System.DateTime.Now;
    DateTime EndDate = System.DateTime.UtcNow;
 
    // Find time difference between two dates
    TimeSpan TimeDifference = StartDate - EndDate;
 
    // Write difference in hours and minutes
    Response.Write("Time difference between server time and Coordinated Universal Time (UTC) is " + 
        TimeDifference.Hours.ToString() + " hours ");
    if (TimeDifference.Minutes != 0)
        Response.Write(" and " + TimeDifference.Minutes.ToString() + " minutes.");
 
}

http://www.beansoftware.com/ASP.NET-FAQ/DateTime-TimeSpan.aspx

Web Farm in Asp.net

Web Farm in Asp.net

After developing our asp.net web application we host it on IIS Server.  Now one standalone server is sufficient to process ASP.NET Request and response for a small web sites but when the site comes for big organization where there an millions of daily user hits then we need to host the sites on multiple Server. This is called web farms. Where single site hosted on multiple IIS Server and they are  running behind the Load Balancer.

Fig : General Web Farm Architecture

This is the most common scenarios for any web based production environment. Where Client will hit an Virtual IP ( vIP) . Which is the IP address of Load Balancer. When Load balancer received the request based on the server load it will redirect the request to particular Server.

http://www.dotnetfunda.com/articles/article713-difference-between-web-farm-and-web-garden.aspx

Trigger in Sql Server

What is a Trigger

A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of data. It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action no the table that they are assigned to.

Types Of Triggers

There are three action query types that you use in SQL which are INSERT, UPDATE and DELETE. So, there are three types of triggers and hybrids that come from mixing and matching the events and timings that fire them.

Basically, triggers are classified into two main types:-

(i) After Triggers (For Triggers)
(ii) Instead Of Triggers

(i) After Triggers

These triggers run after an insert, update or delete on a table. They are not supported for views.
AFTER TRIGGERS can be classified further into three types as:

(a) AFTER INSERT Trigger.
(b) AFTER UPDATE Trigger.
(c) AFTER DELETE Trigger.


http://www.codeproject.com/KB/database/TriggersSqlServer.aspx

Web Garden in asp.net

Web Garden in asp.net

Overview of Web Garden

By default, each application pool runs with a single worker process (W3Wp.exe). We can assign multiple worker processes with a single application pool. An application pool with multiple worker processes is called a Web Garden. Many worker processes with the same application pool can sometimes provide better throughput performance and application response time. And each worker process should have its own thread and memory space.

Web Garden (Application pool with multiple worker processes)

As Shown in the picture, in IIS Server, there may be multiple application pools and each application pool has at least a single worker process. A Web Garden should contain multiple worker processes.

There are certain restrictions in using a Web Garden with your web application. If we use Session Mode as "in proc", our application will not work correctly because the Session will be handled by a different worker process. To avoid this, we should use Session Mode as "out proc" and we can use "Session State Server" or "SQL-Server Session State".


reference:http://www.codeproject.com/KB/aspnet/ExploringIIS.aspx

When to Use Generic Collections in C#

The following generic types correspond to existing collection types:

• List is the generic class corresponding to ArrayList.
• Dictionary is the generic class corresponding to Hashtable.
• Collection is the generic class corresponding to CollectionBase. Collection can be used as a base class, but unlike CollectionBase it is not abstract, making it much easier to use.
• ReadOnlyCollection is the generic class corresponding to ReadOnlyCollectionBase.ReadOnlyCollection is not abstract, and has a constructor that makes it easy to expose an existing List as a read-only collection.
• The Queue, Stack, and SortedList generic classes correspond to the respective nongeneric classes with the same names.
• There are several generic collection types that do not have nongeneric counterparts:
• • LinkedList is a general-purpose linked list that provides O(1) insertion and removal operations.
  • SortedDictionary is a sorted dictionary with O(log n) insertion and retrieval operations, making it a useful alternative to SortedList.
  • KeyedCollection is a hybrid between a list and a dictionary, which provides a way to store objects that contain their own keys.

Difference Between Primary key and Unique key

Difference Between Primary key and Unique key

1.Even though both the primary key and unique key are one or more columns that can uniquely identify a     row in a table, they have some important differences.
2.Most importantly, a table can have only a single primary key while it can have more than one unique key. 
Primary key can be considered as a special case of the unique key. 
3.Another difference is that primary keys have an implicit NOT NULL constraint while the unique key does    not have that constraint. 
4.Therefore, unique key columns may or may not contain NULL values but primary key columns cannot contain NULL values.